sql statement with where won't execute

willie2 · General User Joined: 29 Apr 2005 Posts: 9

I have quite a strange problem with a sql statement in a macro. With OOo 2.1 everything is fine. But since i updated to OOo 2.4 i can't execute sql statements with a "where" inside.

Example:

oResultSet = oStatement.executeQuery("SELECT LV FROM LV WHERE Project = ""test"";")
in OOo 2.1 no problem, but with OOo 2.4 an error

the same with:

oResultSet = oStatement.executeQuery("SELECT `" & fieldName & "` FROM `" & table & "` WHERE (`Project` = """ & InProject & """);")

There must be some problem with the expression after the ... WHERE Project . I tried all sorts or "arrangements" of quotations etc, but no success. If i use:

oResultSet = oStatement.executeQuery("SELECT LV FROM LV WHERE Project ;")
there is no error, but of course not the wanted result.

Any suggestions are appreciated ...

thom314 · Posted: Sun Oct 11, 2009 7:31 pm Post subject:

I don't have time to test this but I think you need double quotes around:the table and column names and single quotes around strings, as in:

"SELECT ""title"" FROM ""books"" WHERE ""author"" = 'betty'"
_________________
- Tom on Linux Mint

willie2 · General User Joined: 29 Apr 2005 Posts: 9

Thanks a lot, first part solved ...

oResultSet = oStatement.executeQuery("SELECT ""LV"" FROM ""LV"" WHERE ""Project"" = 'test';")

works! But how to set Quotations with variables

... WHERE (""Project"" = Quotations? & InProject & Quotations?);")

I tried many different variants but no success. Perhaps you can help me once again ...

RPG · Posted: Mon Oct 12, 2009 1:24 am Post subject:

Hello

Variabelen in a SQL statement must be surround with double quotes.

Basic and SQL don't see each other.

Conclusion use enough double quotes.

Romke

thom314 · Posted: Mon Oct 12, 2009 1:50 pm Post subject:

Perhaps I misunderstand your question but: