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.
********************************************************************************