jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes JDBC - Error in prepare statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC - Error in prepare statement " Watch "JDBC - Error in prepare statement " New topic
Author

JDBC - Error in prepare statement

Dharam Sharma
Greenhorn

Joined: Sep 06, 2012
Posts: 6
Hi All,

First, please accept my apology if I am not providing enough information here..If so, please feel free to ask and I will collect those.
Also, it would be great if you can help me on how to get those information too...:-)

I am new in Java world..I am working on a task that requires to add/delete/select/modify records from informix database..
I have been working with similar code but having these DML statement as part of stored procedures and all worked okay.
This time, I need to write direct insert and select statement instead of having those in stored procedure. It seems like I am getting
an error while using insert statment..For testing, if I simply changed the insert statement with an update statement, it works just fine..
Do anyone know what might be causing an issue here?

Here is more details..


String lvINS_MA = String.format( " INSERT INTO tab_1(ma_serialno, ma_mm_serialno, ma_md_serialno, ma_md_mm_serialno, ma_extra1) VALUES(0,?,?,?,?) " );
//String.format("update tab_1 set (ma_mm_serialno) = ( 0 ) where ma_serialno = 1 " ); <-- If I replace the above line with this line, it works okay...

PreparedStatement psInsMA = null;

try {
psInsMA = conn.prepareStatement(lvIns_MA); <-- This lines throws an exception as "java.sql.SQLException: System or internal error java.lang.NullPointerException"

} catch (Exception e ) {
External.LogMsg(LOGLEVEL_ERROR, String.format("Prepare Statements : Error [%s] ", e.toString()));
}



Please note, the conn is correctly filled with correct values , like database, user etc..As stated earlier, it occurs only when I used the "INSERT" statement...When I run the same code with "UPDATE" statement line, it just works fine..

Thanks in advance...

Regards,

DDS

Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

I can only conclude that conn is null, except why would it work for update? It could be that the db user does not have insert privileges but has update privileges. Can you check that?

If that isn't it, please post more of the code, especially were you are creating the conn object and post the full stack trace of the error. use e.printStackTrace()
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Yes, the stack trace would definitely be useful.

Other than that, your INSERT sql query has a space in the front, while the UPDATE does not. It is not very probable, but the JDBC driver theoretically could have issues with this. I'd suggest trying to remove the space -- it cannot hurt.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Welcome the the ranch Dd.

There is often very useful information in the stack trace learning to read that is a very important skill, and providing it to us is also very useful.
Dharam Sharma
Greenhorn

Joined: Sep 06, 2012
Posts: 6
Thank you all for your quick response on this.

I tried all options but none of them worked i.e. removing space from front, removing other columns and leaving just one column with hardcoded value etc..
I will work on getting the e.printStackTrace() output for you all. I tried using it last night but looks like this function doesn't return anything and therefore,
I was unable to print it..I will have to spend sometime on how to use "e.printStackTrace()" to get the output in log file..If you guys have any quick code
reference on how to print it, please let me know. I am very sure I will get some help on Google search but just trying to get a quick answer here..

Also, please accept my apology in advance if you do not receive quick reply from me as I work on multiple tasks / projects at the same time and have
to switch between (Java / Perl / .NET / Web Services..) throughout the day and might not get time to work on Java task on any given day..
Thanks again for all your help..

Regards,

Dharam
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

I don't know anything about the code you're using for logging. Most logging packages have methods where you can pass the Exception itself as a parameter. Perhaps yours does too.

If it doesn't, then it really should. But in the meantime you can convert the stack trace to a String like this:



Or perhaps you could do your development in an environment which doesn't require you to use that logging system. A sandbox, it's called. You write small pieces of code and test them before they get inserted into a system which doesn't support debugging well.
Dharam Sharma
Greenhorn

Joined: Sep 06, 2012
Posts: 6
Thanks Paul..

Here is output ..Please note, in this run, I removed the space before INSERT...The conn (Connection) is properly filled...in debug mode, when I click on "conn" it shows "com.informix.jdbc.IfxSqliConnect@7cf01771"..
It looks to me that the jdbc version is not recognizing the keyword "INSERT" statement..

lvGetStackTrace : [com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:473), com.informix.jdbc.IfxSqli.handleSocketException(IfxSqli.java:8982), com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2325), com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1184), com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:320), com.informix.jdbc.IfxPreparedStatement.processSQL(IfxPreparedStatement.java:300), com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:170), com.informix.jdbc.IfxSqliConnect.createPreparedStmt(IfxSqliConnect.java:6065), com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2084), libMedia.SetMedia.readMediaDirectory(SetMedia.java:73), MediaAccess.SetAndGet.main(SetAndGet.java:72)]

Please let me know if you guys want me to collect any other information or try something else...Thanks to all of you again for your kind support and time ...

Regards,

Dharmendra
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

I'm sorry, I scanned the thread three times and I don't see where you posted the contents of the error message you were getting. Did I miss it, or have we really been discussing the problem with no information except "doesn't work" all along?
Dharam Sharma
Greenhorn

Joined: Sep 06, 2012
Posts: 6
Hi Paul,

Sorry, I meant to place the same as part of my initial para along with the same error at correct line in the code where it was occuring..An error is "java.sql.SQLException: System or internal error java.lang.NullPointerException"

Here is the code again for easy reference and also placed an error on the line where it occurs..Sorry again..

-Dharmendra
--------------------------------------------------------------------------------



String lvINS_MA = String.format( " INSERT INTO tab_1(ma_serialno, ma_mm_serialno, ma_md_serialno, ma_md_mm_serialno, ma_extra1) VALUES(0,?,?,?,?) " );
//String.format("update tab_1 set (ma_mm_serialno) = ( 0 ) where ma_serialno = 1 " ); <-- If I replace the above line with this line, it works okay...

PreparedStatement psInsMA = null;

try {
psInsMA = conn.prepareStatement(lvIns_MA); <-- This lines throws an exception as "java.sql.SQLException: System or internal error java.lang.NullPointerException"

} catch (Exception e ) {
External.LogMsg(LOGLEVEL_ERROR, String.format("Prepare Statements : Error [%s] ", e.toString()));
}

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I'd be very surprised if the JDBC driver neglected the INSERT statement. What database are you using?

My advice: take the statement that fails, replace question marks with some meaningful values and try to execute it in a SQL client to verify it runs. If it runs successfully, copy it exactly as it is from the SQL client and paste it into your program (use the clipboard, do not retype it ), and try to run it again. Let us know what happens.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Oh yes, you did post that. I was expecting an SQL exception, and apparently so were you. (That's a reasonable expectation.) And you're still trying to blame your SQL, even though there isn't an SQL exception which says anything about it.

Normally NullPointerException indicates a programming error. The fact that it's being thrown from inside the JDBC driver code suggests that there might be a programming error in the JDBC driver. Although that's rather unlikely. The other possibility is that the programming error is yours, somehow. The theory "It must be my fault" is usually correct when dealing with programming, and you are indeed applying that theory. I just don't think you have chosen the right thing to blame.

What I suspect is this: you are passing null to the JDBC driver instead of a String. In other words the "lvIns_MA" variable contains a null reference when you pass it to the prepareStatement() method. You can test that by replacing this line:



by this line:



In other words, deliberately pass null to the method, to see what happens. Now if the result of that is different from the result you're getting now, then my idea is wrong and you'll have to look somewhere else. If the result is the same, i.e. the same error message from the same place, then you should make sure you aren't passing null to the method in real life.
Dharam Sharma
Greenhorn

Joined: Sep 06, 2012
Posts: 6
Hi Paul,

Thanks again..

As you stated, I always first think it is my problem and I alway win both way, mainly in finding an error and also quickly.

In this case, I tried all the possible things from analysis point of view and very confident that an error is outside of the code.
For example, about following line, I am printing the "lvIns_MA" in log just before the following line and getting my insert statement. I also found that in debug mode as well...In debug mode, when I reach to this point,
I place my cursor on "conn", "lvIns_MA" and are able to see values on these..The thing that surprises me and also helps my confidence that an issue is out side of my code is : when I change the insert statement to
update or select or delete on the same table, it works just fine..Also, for additiona analysis , I forcely wrote incorrect INSERT statement (i.e. withou having VALUE clause) and this time, it gave me Syntax Error, which
was expected..so, the jdbc does understand the INSERT and it's Syntax but unable to prepare it if the sytax is correct..

psInsMA = conn.prepareStatement(lvIns_MA);

Regards,

Dharmendra
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Well, I'm not convinced that the syntax is correct. Following Martin's recent advice would help in determining that. But your present theory seems to be "The JDBC driver crashes when asked to prepare a syntactically-correct INSERT command". That doesn't seem likely to me, there must be other people in the world who use that driver to insert rows into tables. (The "I'm Not Special" principle of debugging.) However the way to test that would be by presenting the driver with a variety of strings which are INSERT commands of various degrees of correctness.

I'm also confused by the "String.format(...)" part of your posted code. It doesn't seem to do anything except to make me think it's part of the problem. So at this point it's just getting in the way. Since you should be looking for the simplest possible code fragment which illustrates the problem, I think that removing that for now would be helpful.
Dharam Sharma
Greenhorn

Joined: Sep 06, 2012
Posts: 6
Hi Paul and all,

First, thanks a lot for all your support and time to provide me help. I really appreciate it.

I just wanted to give you all an update that I was able to sort out and surprisingly, I did it by myself..I realized that you guys could have helped me earlier if I had provided little more information like I am using Eclipse etc etc.
Anyway, here is what I found out and have made my code worked. I am sure you guys will be able to tell me the valid reason for this cause so I will get better understanding but as of now, my code is working just fine..

So, to run my code in Eclipse, I had to link jar files to access informix database and those were from "/usr/informix/extend/krakatoa/" and jar files were "ifxlang.jar, jdbc.jar, jdbc_g.jar" etc etc.
For some reason, these jar file didn't work as expected mainly with my insert statement but after spending enough time and searching more on Google, I removed all these .jar files and have linked
other jar files from "/usr/informix/jdbc/lib/" folder and the jar files are "ifxjdbc.jar, ifxjdbcx.jar, ifxlang.jar" etc etc and that did the trick...

I am just not very sure that what's difference between these two set of jar and relying on you all experts to share what those are but the thing confuses me is, in my group, most of team members are using
jar files from /"usr/informix/extend/krakatoa" and using DB access via stored procedure and have been working just fine..it is first time I , a new member of team need to write direct INSERT statement
(instead of stored procedure) and noticed this issue..

Regards,

Dharmendra
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC - Error in prepare statement