RE: Sql problem in my java program.

Thanks Dave and Nigel.
Got it to work with the prepared statement and the conditioning of the
values. 

Michael Smith
iSeries.mySeries.


-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of NGay@xxxxxxxxxxxxx
Sent: Thursday, January 11, 2007 11:39 AM
To: Java Programming on and around the iSeries / AS400
Subject: Re: Sql problem in my java program.


Mike,

You could always just put in a

if (data9 == null)
      data9 = "";

That will work, unless you actually want the null value to be retained
as a null in the iSeries database.  To solve that you'd have to to
change your insert statement to

PreparedStatement ps = condb.prepareStatement ("INSERT into MercAud00
VALUES (?,?,?,?,?,?.....)"); (using the correct number of ?s for how
many fields you have)

and then set the values by, e.g.
ps.setInt (1, instrement_type);
ps.setInt (2, siteId);
...and so on, using the correct data types, and finally do
ps.executeUpdate ();

rather than building the whole statement up as a string.

HTH,

Nigel Gay,
Computer Patent Annuities Limited.


java400-l-bounces@xxxxxxxxxxxx wrote on 11/01/2007 12:37:50 PM:

I'm trying to write a little program that reads data from an Access 
database and loads an Iseries Database.

Here is the jist of the code.
         stmtdb = condb.createStatement();
         while (rs.next()) {//B2
            int instrument_type = rs.getInt(1);
            int siteId = rs.getInt(2);
            int siteId2 = rs.getInt(3);
              String crckey     = rs.getString(4);
            String logdate    = rs.getString(5);
            int linkField     = rs.getInt(6);
            int logTrigger    = rs.getInt(7);
            int alarms        = rs.getInt(8);
                        String data1         =
rs.getString(9);
            String data2         = rs.getString(10);
            String data3       = rs.getString(11);
            String data4       = rs.getString(12);
            String data5         = rs.getString(13);
            String data6         = rs.getString(14);
            String data7         = rs.getString(15);
            String data8         = rs.getString(16);
            String data9       = rs.getString(17);
            String data10      = rs.getString(18);
            String editFlag   = rs.getString(19);
            String alarmsFlag = rs.getString(20);

            String query2 = "INSERT into MercAud00 "
+
            "VALUES (" + instrument_type
                  + "," + siteId
                  + "," + siteId2
                   + ", " + "'" + crckey +
"'"
                  + ", " + "'" + logdate +
"'"
                  + ", " + linkField
                  + ", " + logTrigger
                  + ", " + alarms
                  + ", " + data1
                  + ", " + data2
                   + ", " + data3
                  + ", " + data4
                  + ", " + data5
                  + ", " + data6
                  + ", " + data7
                  + ", " + data8
                  + ", " + data9
                  + ", " + data10
                  + ", " + "'" + editFlag
+ "'"
                  + ", " + "'" +
alarmsFlag + "'"
                  + ")";
            System.out.println(instrument_type + ",
" + siteId + ", " + siteId2 + ", " + crckey + ", " + logdate +
               ", " + linkField + ", " +
logTrigger + ", " + alarms
               + ", " + data1 + ", " + data2 +
", " + data3 + ", " + data4 +
               ", " + data5 + ", " + data6 + ",
" + data7 + ", " + data8 + ", "
               + data9 + ", " + data10 + ", " +
editFlag + ", " + alarmsFlag);
            stmtdb.execute(query2);


Here is the last line printed in the System.out
3, 12, 4, 000D, 2006-08-26 10:22:49, 5, 3, 0, 0, 0, 456.30, 84.33, 
638163, 193449, 0, null, .00, 8.22, 0,

This record is where my program is blowing up.  I suppose it is 
happening on the null value.

How do I handle this?.  I tried putting a coalesce(data8) in the 
insert statement, but it didn't like that very much.


Michael Smith
iSeries.mySeries.

--
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/java400-l.

************************************************************************
********
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested to
return a copy of this message to the sender indicating that you have
received it in error, and to destroy the received copy; and (2) any
disclosure or distribution of this message, as well as any action taken
or omitted to be taken in reliance on its content, is prohibited and may
be unlawful.
************************************************************************
********