Problems with SQL commands

hab · General User Joined: 30 Aug 2004 Posts: 6

Hi

when I type the command in pure mysql it works fine

SELECT IF (firstname like 'A%', 'OK', 'NOT'), firstname from mgw_contacts

but when I use OOP, it says SQL-Exception

"SELECT IF (firstname like 'A%', 'OK', 'NOT'), firstname from mgw_contacts "

BTW easy SELECT queries work fine with OOP.

Liliana_mcm · Posted: Thu Sep 09, 2004 4:00 am Post subject:

Hi,

What about try something like this :

hab · General User Joined: 30 Aug 2004 Posts: 6

Thats is not bad, but ...

the if statement in mysql works like this
if (true, "first", "second") -> the output is "first"
if (false, "first", "second") -> the output is "second"

so the command:

SELECT IF (firstname like 'A%', 'OK', 'NOT'), firstname from mgw_contacts;

Should give the output like this:

Row1 Row2
OK Alfred
NOT Berta
OK Alexandra
NOT Caesar

and so on .....

Liliana_mcm · Posted: Fri Sep 10, 2004 2:03 am Post subject:

Hi,

Maybe I did not understand you question.
I'm a developing a front-end to a PostgreSQL Database with OO Writer, and i now that in postgres you can do something like this:

hab · General User Joined: 30 Aug 2004 Posts: 6

Thanks for your effort

but that code does not work.

I get the error-message:

"Message: Syntax error or access violation, message from server: "You have an error in your SQL syntax"

But when running it on pure mysql it works fine.

blackr2d · Posted: Wed Sep 29, 2004 12:12 am Post subject: Run SQL directly?

Sorry if I miss the point here, I am a beginner really.
But I do quite a lot of queries on MySQL in OO lately and I have hit the same problem in the very beginning.
My solution was to enable "Run SQL command directly" and voila! No more problems. All SQL copied directly from your old scripts / reports will work straight away.

That means you have to write all your queries from hand without the use of the "design view", but for me it's not a problem, I'm actually faster typing SQL in.
_________________
Jack

hab · General User Joined: 30 Aug 2004 Posts: 6

> "Run SQL command directly"

This is a very goog idea, does this work within a macro too?

I get the error message at the point: oMerge.execute(oProps())

Peter · Posted: Fri Oct 01, 2004 12:43 am Post subject: Run SQL command directly

Try with oStatement.EscapeProcessing = False , so OO does not parse the SQL syntax.
Peter

hab · General User Joined: 30 Aug 2004 Posts: 6

Peter · Posted: Wed Oct 06, 2004 11:26 am Post subject:

EscapeProcessing is a property of the statement (connection.createStatement), how did you get oMerge?

Peter

hab · General User Joined: 30 Aug 2004 Posts: 6

Peter · Posted: Thu Oct 14, 2004 2:54 am Post subject: EscapeProcessing

See http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/DataAccessDescriptor.html.
EscapeProcessing only works with CommandType=2 (const COMMAND), oMerge.Command must be the SQL-String.

Greetings from Berlin

Peter