[Solved] java.lang.NullPointerException in SQL Update

[zzyzcx]

I am trying to use a prepared statement to update a database and I'm getting java.lang.NullPointerException. This same macro used to work before I upgraded to OOo 3.2. Is there some change in OOo 3.2 in the way that Base handles Database Connections, SQL queries, or prepared statements?

I'd post the code, but it is spread out over several different macros - one that generates the query string, one that converts it to a prepared statement, culminating in the call:

PreparedStatement.ExecuteUpdate()

The error is generated when the above call is made.

As an aside, I also have NeoOffice installed on the same computer (I'm using a Mac) and the same exact macro executes without a hitch in NeoOffice. It used to execute without a hitch in OpenOffice as well. Anything that may have changed in the new version that may be causing this?

[rudolfo]

Although I am pretty sure that the exception is thrown by one of the UNO objects in Base ... have you tried the macro with a different macro language? And maybe even more important: What language do you use for your macros? Beanshell, Java, Javascript, 00o Basic, Python ?

[FJCC]

I have little experience with Base, but I'll pass along a similar experience. I was trying to add data to a table using copy and paste from a spreadsheet. The underlying SQL command must be some version of INSERT, I suppose. I got the NullPointerException every time I tried to insert a certain block of data but I couldn't find anything different about the problematic data. In my searches on the forum I found a reference by Villeroy to a Base bug in 3.2. I tried loading the same data using 3.1 and it worked the first time. So, you might want to roll back to 3.1 and wait for 3.3 before updating your OOo.

[zzyzcx]

@rudolfo:

What language do you use for your macros? Beanshell, Java, Javascript, 00o Basic, Python ?

I use OOo Basic.

@FJCC: Thanks for the tip. I'll try rolling it back and see if that works.

[zzyzcx]

After rolling back to 3.1 all works as it should. Thanks for the help.

[eibo]

Even if this thread is 1 year old I just wanted to offer the solution I found, as googling lead me here but I did not want to revert to 3.1 as had been suggested here.

My application did use lots of combos like

Code: Select all

Connection = Datenbank.getConnection("","")
<...>
SQL = "INSERT ..."
Statement = Connection.PrepareStatement(SQL)
Statement.ExecuteUpdate()
<...>

Apparently, this is no longer valid code. So I switched to using the following instead

Code: Select all

Connection = Datenbank.getConnection("","")
Statement = Connection.CreateStatement()
<...>
SQL = "INSERT ..."
Statement.ExecuteUpdate(SQL)
<...>

Incidentally, this was much better anyways, as the queries are all static and not parameterized and now I am reusing the "Statement" object. It did work, though, even if it probably did hurt performance.

[rudolfo]

Good to know that there is a workaround although if you have to rewrite your code. But particulary if you are working with large amounts of data prepared statements are by concept more convenient and reliable and have higher speed. Of course they are only reliable if the underlying API layer is working reliably. Here is typical pseudo code how prepared statements are used particularly with inserts:

Code: Select all

nLast = UBound(dataArray,1)  ' number of lines/records in the n x 4 array
oStmt = oConnection.PrepareStatement("INSERT INTO ""my_table"" VALUES (?,?,?,?)")
' I left out the four specific columns to avoid the nightmare of escaping double ""quotes""
For i=0 To nLast
    oStmt.setInt(1, CLng(dataArray(i,0)))  ' first column is an integer (maybe even the primary key)
    oStmt.setString(2, dataArray(i,1))
    oStmt.setString(3, dataArray(i,2))
    oStmt.setString(4, dataArray(i,3))
    oStmt.executeUpdate()
Next i

PrepareStatement returns a database object which is already semi-processed by the database engine while the counterpart for static statements CreateStatement returns only an object that is simply initialized and that leaves the main work to executeUpdate. It is a significant performance gain that only the binding of the parameters with setString() or setInt() has to be done in the loop, but parsing of the statement is done outside.
That's only the performance aspect of the workaround and I guess that eibo is surely aware of this if he mentions that "it probably did hurt performance".

But there is one more aspect apart from performance: With prepared statements you don't have to inspect your data on special characters that need to be escaped before you concatenate the SQL statement string for executeUpdate:

Code: Select all

dataArray(1,2) = "Don't do it!"
sql = "INSERT INTO ""my_table"" VALUES (2,'" & dataArray(1,2) & "','nothing','ignore me')"
oStmt.executeUpdate(sql)

will produce an error. Surely you can write an escaping function that processes all variables before concatenating them into the string. But if you have worked with prepared statements and their clean interface for this, you simply just don't want to revert back to static statements.