Re: Inserting packed values into a DB2/400 table via JDBC

Ed,

What Marshall describes will work fine but if you do this in a loop or
more than once you might want to use a PreparedStatement. The steps are
about the same but you would supply question marks (?) for place holders
as "insert into it005p (item_number) values(?)" and set them by their
ordinal position like: myPreparedStatement.setBigDecimal(1, new
BigDecimal("5555.55")); In this case the one is the 1st ?.

David Morris

>>> marshall@xxxxxxxxxxx 10/28/2004 9:13:25 AM >>>
You are making things way too difficult.  The SQL processor on the 400

will take care of packing the data for you.  Just create an SQL string

containing:

INSERT INTO IT005P (ITEM_NUMBER) VALUES(47001)

and execute it.
===
Marshall Dunbar
DPS, Inc.
marshall@xxxxxxxxxxx 
direct:(828)635-5561
main office:(317)574-4300 (800)654-4689
====



EDoxtator <doc6502@xxxxxxxxx> 
Sent by: java400-l-bounces@xxxxxxxxxxxx 
10/28/2004 10:37 AM
Please respond to
Java Programming on and around the iSeries / AS400 
<java400-l@xxxxxxxxxxxx>


To
java400-l@xxxxxxxxxxxx 
cc

Subject
Inserting packed values into a DB2/400 table via JDBC






Hi All

I've got a table that has several packed columns.  How do I insert
packed
values into this table from a Windows PC via JDBC?  I'm using JTOpen
4.5.

Here is a snippet of the code I'm using to try and create the value:

Connection con = null;
com.ibm.as400.access.AS400PackedDecimal a = new 
     com.ibm.as400.access.AS400PackedDecimal( 7, 0 );

::: snipped connection code :::

   // Create and execute SQL Insert 
   Statement stmt = con.createStatement();
   byte[] b = a.toBytes( new BigDecimal( 47001 ) );
   String value = b.toString();

   String SQLStmt =
                 "INSERT INTO IT005P " +
        "(ITEM_NUMBER) " +
        "VALUES(\"" + value + "\")";

   ResultSet rs = con.execute( SQLStmt );

::: remainder of program snipped :::

Of course, what I see if I log the SQL statement is:

SQL Statement = INSERT INTO IT005P (ITEM_NUMBER) VALUES("[B@16672d6")

Error: [SQL0206] Column "[B@16672d6" not in specified tables.

If anyone has any ideas, I'd really appreciate it.

Thanks!

-Ed