prepareStatement in python Macro gives error

Frie · October 10, 2023, 11:53am

Libreoffice 7.6.2.1 with Firebird database on Ubuntu.
I am writing a macro in python that gives an error. The same code in Basic is working without problems.
When I use createStatement instead off prepareStatement there is no problem either.

The working code in Python:

      stSql = "SELECT ""bedrijfsnaam"" FROM ""bedrijfsgegevens"""
      statement = connection.createStatement()
      oResult = statement.executeQuery(stSql)

The ‘error’ code in Python:

      stSql = "SELECT ""bedrijfsnaam"" FROM ""bedrijfsgegevens"""
      statement = connection.prepareStatement(stSql)
      oResult = statement.executeQuery()

In the error code i get the message that table ‘bedrijfsgegevens’ is unknown.

Can someone help me to see the difference?

UPDATE: The error message:
ooo_script_framework.com.sun.star.sdbc.SQLException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Table unknown
*BEDRIJFSGEGEVENS
*At line 1, column 26
caused by
‘isc_dsql_prepare’
at /build/libreoffice/parts/libreoffice/build/connectivity/source/drivers/firebird/Util.cxx:69

mikekaganski · October 10, 2023, 12:19pm

Are you sure that double quotes are escaped using another double quote in Python? I’d expect

      stSql = 'SELECT "bedrijfsnaam" FROM "bedrijfsgegevens"'

or at least

      stSql = "SELECT \"bedrijfsnaam\" FROM \"bedrijfsgegevens\""

… but then, I’d also expect another error message…

Frie · October 10, 2023, 12:25pm

Indeed, that is the solution and now it works. But I still do not understand why the same sql-string with double " works in the createStatement situation. Anyway, Thanks!!

mikekaganski · October 10, 2023, 12:34pm

If I understand correctly, your

      stSql = "SELECT ""bedrijfsnaam"" FROM ""bedrijfsgegevens"""

is interpreted by Python as

      stSql = "SELECT " + "bedrijfsnaam" + " FROM " + "bedrijfsgegevens" + ""

which translates to

      stSql = "SELECT bedrijfsnaam FROM bedrijfsgegevens"

Possibly there are cases where the double quotes are not required by SQL syntax.

Frie · October 10, 2023, 12:39pm

Maybe, but why is the same string interpreted differently in createStatement and prepareStatement?
Update: I have done a few tests with the same string and createStatement simply interpretes the string different from prepareStatement, so I have to work with 2 different strings.

sokol92 · October 10, 2023, 1:01pm

This could happen.
The method prepareStatement is executed by the driver and is usually intended for parsing parameterized queries.
The method executeQuery is directly executed by the database.
The driver and the database may handle “incomplete” query syntax differently.

Frie · October 10, 2023, 1:04pm

Aha, there is the cause of the difference! Thanks a lot!