SQLException when using ADO driver and prepared statements

users
#1

Hello!

Does the ADO driver support prepared statements at all, or am I doing
something wrong? I'm experimenting with LibreOffice Basic and MS SQL Server
2008 R2, trying to read rows from a database with the ADO driver and a
prepared statement. I have old ODBC code that works against MSSQL, but for
some reason I can't get the ADO version to work.

I have set up a simple test table (tbl1) like this:
col1 (integer); col2 (varchar(12))
1; aaaa
2; bbbb
3; cccc

I'm trying to get a row from the table with a prepared statement like this:
Sub test
Dim DatabaseContext As Object
Dim flatProp(0) As New com.sun.star.beans.PropertyValue
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object

DatabaseContext = createUnoService("com.sun.star.comp.sdbc.ado.ODriver")
Connection = DatabaseContext.connect("sdbc:ado:PROVIDER=sqloledb;DATA
SOURCE=myserver;INITIAL CATALOG=mydb;USER ID=user;PASSWORD=pwd", flatProp())
Statement = Connection.prepareStatement("SELECT col1 FROM tbl1 WHERE col2 =
?")
Statement.setString(1, "bbbb")
ResultSet = Statement.executeQuery()
MsgBox ResultSet.getString(1)
End Sub

When I run the script, I get a com.sun.star.sdbc.SQLException (Incorrect
syntax near ':'..) at the executeQuery() line.

If I replace the question mark in the query with 'bbbb' the code works fine.

#2

Replying to myself to add that this is with LibreOffice 4.1.0.4 on Windows
XP.

#3

Hi,

DatabaseContext = createUnoService("com.sun.star.comp.sdbc.ado.ODriver")
Connection = DatabaseContext.connect("sdbc:ado:PROVIDER=sqloledb;DATA
SOURCE=myserver;INITIAL CATALOG=mydb;USER ID=user;PASSWORD=pwd", flatProp())
Statement = Connection.prepareStatement("SELECT col1 FROM tbl1 WHERE col2 =
?")
Statement.setString(1, "bbbb")
ResultSet = Statement.executeQuery()
MsgBox ResultSet.getString(1)
End Sub

When I run the script, I get a com.sun.star.sdbc.SQLException (Incorrect
syntax near ':'..) at the executeQuery() line.

If I replace the question mark in the query with 'bbbb' the code works fine.

So the question is not whether you can use prepared statements, but
whether you can use parameterized queries in prepared statements ?

I guess I would start with whether the simple parameterized SQL
statement actually runs correctly from within the LO Query SQL editor ?

Alex

#4

hei,
did some test in the past and found only troubles using ADO, for a MS SQL server ODBC is the best option

#5

Hello!

Alexander Thurgood:

So the question is not whether you can use prepared statements, but
whether you can use parameterized queries in prepared statements ?

Well, yeah, actually :slight_smile:

Alexander Thurgood:

I guess I would start with whether the simple parameterized SQL
statement actually runs correctly from within the LO Query SQL editor ?

It doesn't seem to work. I tried SELECT "col1" FROM "tbl1" WHERE "col2" = :x
and just got a bunch of errors (listed below). I did some googling already
but didn't find anything yet. I have some actual work that has to be done so
I'm going to have to return to this a little later. Thanks for the tip!

1.
The data content could not be loaded.

2.
SQL Status: 42000
Error code: -2147217900

Incorrect syntax near ':'.

3.
SQL Status: 42000
Error code: -2147217900

Statement(s) could not be prepared.

#6

Hi,

It doesn't seem to work. I tried SELECT "col1" FROM "tbl1" WHERE "col2" = :x
and just got a bunch of errors (listed below). I did some googling already
but didn't find anything yet. I have some actual work that has to be done so
I'm going to have to return to this a little later. Thanks for the tip!

Did you try switching the SQL button (ESCAPE_PROCESSING) on/off and
running the query again ?

For example, this works for me from the "Create a Query in SQL mode"
window with :
- an embedded HSQLDB
- a mysqldb connected over the mysql JDBC connector driver
- a mysqldb connected over a native mysql C connector driver

However, in order for it to work, I have to have the SQL button not
pressed, i.e. ESCAPE_PROCESSING is turned off. If I activate the direct
SQL execution using the db engine's own SQL parser, the parameterized
query fails (at least on mysql).

The answer to your problem may (hopefully) simply lie in turning
ESCAPE_PROCESSING "off" in your Basic routine. If that doesn't work,
then there is probably some kind of problem with the driver code that LO
uses.

http://api.libreoffice.org/docs/common/ref/com/sun/star/sdb/QueryDefinition.html

Alex

#7

Hi!

Did you try switching the SQL button (ESCAPE_PROCESSING) on/off and
running the query again ?

I tried that. The only difference when the "Run SQL command directly" button
is on is that I don't get the Parameter Input dialog. The errors stay the
same.

mhaverin

#8

Hi,

I tried that. The only difference when the "Run SQL command directly" button
is on is that I don't get the Parameter Input dialog. The errors stay the
same.

Sounds like bug submission time then :wink:

Alex

#9

Hello!

I went and added this:
https://www.libreoffice.org/bugzilla/show_bug.cgi?id=68417

Thank you for your help!

#10

Hi,

I went and added this:
https://www.libreoffice.org/bugzilla/show_bug.cgi?id=68417

Thank you for your help!

No problem, the only difficulty now will be finding a QAer who has the same setup or can attempt to do what you are trying to do. I don't think that there are many of those around unfortunately, so your report may go unconfirmed for a while.

Alex

#11

Me again. You also might want to read this thread :

http://forum.openoffice.org/en/forum/viewtopic.php?f=30&t=41226

which basically resumes the whole situation pretty well.

Alex

#12

Hi!

I read that thread, but don't really get what I was supposed to learn from
it?

#13

Hi :slight_smile:
I think the idea was to see if the circumstances look fairly similar so that
other people can see what has been tried already in a similar case. Is the
case similar?

It usually helps us if your responses give some context. For example if one
individual asks one question and you reply just saying "Yes" but before your
message got to the list someone else had written a different question then
how does anyone else know what you are responding too?. In this case i went
to Nabble and found the post immediately before your response did give a
link. However, i have no idea if that is the link that mhaverin was
responding to when he/she said "I read that thread, but don't really get
what I was supposed to learn from it?"

Sorry this 'answer' is not very helpful!
Apols and regards from
Tom :slight_smile:

Alexander Thurgood wrote

#14

Hi,

I read that thread, but don't really get what I was supposed to learn from
it?

Actually, nothing, that was my fingers answering another unrelated thread, while my eyes were failing to tell my brain that I was in some other astral plane.

Sorry about that.

Alex