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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.