OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Problems with SQL commands

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
hab
General User
General User


Joined: 30 Aug 2004
Posts: 6

PostPosted: Thu Sep 09, 2004 1:29 am    Post subject: Problems with SQL commands Reply with quote

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.


Last edited by hab on Wed Oct 06, 2004 3:58 am; edited 2 times in total
Back to top
View user's profile Send private message
Liliana_mcm
General User
General User


Joined: 19 May 2004
Posts: 32
Location: Marinha Grande - Portugal

PostPosted: Thu Sep 09, 2004 4:00 am    Post subject: Reply with quote

Hi,

What about try something like this :
Code:

 "SELECT firstname from mgw_contacts where  (firstname like ' "A%" ' OR  firstname like  ' "OK" ' OR  firstname like ' "NOT" ' ) "



if not results try to create some var with the values you want to search, and make de sql statement with this vars.

Code:

Dim var1, var2, var3 as string

var1 = "A%"
var2 = "OK"
var3 = "NOT"
...
 "SELECT firstname from mgw_contacts where  (firstname like ' "&var1&" ' OR  firstname like  ' "&var2&" ' OR  firstname like ' "&var3&" ' ) "


I hope thid help you.
Liliana Matos.
Back to top
View user's profile Send private message
hab
General User
General User


Joined: 30 Aug 2004
Posts: 6

PostPosted: Thu Sep 09, 2004 10:39 pm    Post subject: Reply with quote

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 .....


Last edited by hab on Tue Sep 28, 2004 6:38 am; edited 1 time in total
Back to top
View user's profile Send private message
Liliana_mcm
General User
General User


Joined: 19 May 2004
Posts: 32
Location: Marinha Grande - Portugal

PostPosted: Fri Sep 10, 2004 2:03 am    Post subject: Reply with quote

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:

Code:


Query:
SELECT a,
          CASE a
                WHEN 1 THEN 'un'
                 WHEN 2 THEN 'two'
                 ELSE 'other'
          END
    FROM teste;

Result of the query:
 a | case
---+-------
 1 | un
 2 | two
 3 | other



My Ideia:
code]

Query:
SELECT firstname
CASE firstname
WHEN 'A%' THEN 'OK'
ELSE 'NOT'
END
FROM mgw_contacts;

Result of the query:
firstname | case
-----------+-------
Alfred | OK
Berta | NOT
....
[/code]

Is that possible in MySQL ?? Try it.
I hope this will help you.

Liliana Matos.
Back to top
View user's profile Send private message
hab
General User
General User


Joined: 30 Aug 2004
Posts: 6

PostPosted: Tue Sep 28, 2004 6:43 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
blackr2d
General User
General User


Joined: 28 Sep 2004
Posts: 24
Location: Republic of Ireland

PostPosted: Wed Sep 29, 2004 12:12 am    Post subject: Run SQL directly? Reply with quote

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
Back to top
View user's profile Send private message
hab
General User
General User


Joined: 30 Aug 2004
Posts: 6

PostPosted: Thu Sep 30, 2004 11:25 pm    Post subject: Reply with quote

> "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())
Back to top
View user's profile Send private message
Peter
OOo Enthusiast
OOo Enthusiast


Joined: 28 May 2004
Posts: 105
Location: Berlin / Germany

PostPosted: Fri Oct 01, 2004 12:43 am    Post subject: Run SQL command directly Reply with quote

Try with oStatement.EscapeProcessing = False , so OO does not parse the SQL syntax.
Peter
Back to top
View user's profile Send private message
hab
General User
General User


Joined: 30 Aug 2004
Posts: 6

PostPosted: Wed Oct 06, 2004 3:56 am    Post subject: Re: Run SQL command directly Reply with quote

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



I inserted
oMerge.EscapeProcessing = TRUE / FALSE
before
oMerge.execute(oProps())


but the error message is still there,
does this really work?
Back to top
View user's profile Send private message
Peter
OOo Enthusiast
OOo Enthusiast


Joined: 28 May 2004
Posts: 105
Location: Berlin / Germany

PostPosted: Wed Oct 06, 2004 11:26 am    Post subject: Reply with quote

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

Peter
Back to top
View user's profile Send private message
hab
General User
General User


Joined: 30 Aug 2004
Posts: 6

PostPosted: Wed Oct 06, 2004 11:30 pm    Post subject: Reply with quote

1 1

Last edited by hab on Thu Nov 04, 2004 8:02 am; edited 1 time in total
Back to top
View user's profile Send private message
Peter
OOo Enthusiast
OOo Enthusiast


Joined: 28 May 2004
Posts: 105
Location: Berlin / Germany

PostPosted: Thu Oct 14, 2004 2:54 am    Post subject: EscapeProcessing Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group