| View previous topic :: View next topic |
| Author |
Message |
ChillWill
General User
Joined: 24 Jan 2006
Posts: 19
Location: Wisconsin, USA
|
 Posted: Mon Dec 27, 2010 1:18 pm Post subject: [SOLVED] Multiline SQL command fails: MySQL, OOo Base |
 |
|
[SOLVED is a little too strong. WORKED AROUND is more like it.]
I have a Basic language macro in OOo Base to execute some commands in MySQL. There are 3 commands, and if I execute them one at a time, the macro works. But if I combine them into a 3-line command, I get a SQL syntax error.
This is with OOo 3.2.1, Debian GNU/Linux, MySQL 5.0.51a, JDBC connection.
Here is the code that works: | Code: |
oDBdocument = oFormH.Parent.Parent.Parent 'Please don't laugh at my code, I'm an amateur.
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDatabaseSource = oContext.getByName(oDBdocument.URL)
oConnection = oDatabaseSource.getConnection("","")
oSQLstatement = oConnection.createStatement()
oSQLstatement.ResultSetConcurrency = 1008 '1008 => UPDATABLE, i.e. the next statement won't cause a read-only error
oSQLstatement.ResultSetType = 1004 '1004 => SCROLL_INSENSITIVE, i.e. you can move forward or backward in the ResultSet
' ######## Important part starts here
strSQL = "DELETE FROM HHMAX; " 'HHMAX is a table that just holds 1 integer
oSQLstatement.executeUpdate(strSQL)
strSQL = "INSERT INTO HHMAX VALUES ((SELECT MAX(HOUSEHOLDINDEX) FROM TBL_HOUSEHOLD)); "
oSQLstatement.executeUpdate(strSQL)
strSQL ="UPDATE TBL_HOUSEHOLD SET PUBLISH=True WHERE HOUSEHOLDINDEX=(SELECT MAX_HH_INDEX FROM HHMAX); "
oSQLstatement.executeUpdate(strSQL) |
(What it does is find the last record in TBL_HOUSEHOLD and sets PUBLISH = True for that record.)
Here's the equivalent, simpler code that does not work:
| Code: | oDBdocument = oFormH.Parent.Parent.Parent
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDatabaseSource = oContext.getByName(oDBdocument.URL)
oConnection = oDatabaseSource.getConnection("","")
oSQLstatement = oConnection.createStatement()
oSQLstatement.ResultSetConcurrency = 1008
oSQLstatement.ResultSetType = 1004
' ######## Important part starts here
strSQL = "DELETE FROM HHMAX; " & chr(10) 'chr(10) is the newline char
strSQL = strSQL & "INSERT INTO HHMAX VALUES ((SELECT MAX(HOUSEHOLDINDEX) FROM TBL_HOUSEHOLD)); " & chr(10)
strSQL = strSQL & "UPDATE TBL_HOUSEHOLD SET PUBLISH=True WHERE HOUSEHOLDINDEX=(SELECT MAX_HH_INDEX FROM HHMAX); "
oSQLstatement.executeUpdate(strSQL) |
Some things I tried:
Eliminate the chr(10) so the 3 commands are on 1 line, separated by semicolons
Use chr(13) & chr(10) in place of just chr(10)
You can combine the commands on a single line or multiple lines in Base's "Execute SQL Command" dialog box, and it works fine if you run the command from MySQL command line (i.e., not using Base). And this code worked when I was using Base's HSQL built-in database. But with MySQL, combining 2 or more commands in the Basic language macro seems to fail every time.
It's a lot more convenient to write the code if I can send a multi-line command to MySQL. I also think it might execute faster.
Do you know what's wrong and how to do it right ? Thanks much!
Last edited by ChillWill on Thu Dec 30, 2010 8:21 am; edited 1 time in total |
|
| Back to top |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Mon Dec 27, 2010 3:31 pm Post subject: |
|
|
Hello
I use always chr(13).
Maybe you have to use
execute
in stead of executeUpdate
see here.
Romke |
|
| Back to top |
|
|
ChillWill
General User
Joined: 24 Jan 2006
Posts: 19
Location: Wisconsin, USA
|
| Posted: Mon Dec 27, 2010 5:34 pm Post subject: |
|
|
Thanks, Romke. It didn't work, but the link you cited was helpful. The explanations for all 3 of the "execute" methods listed (executeQuery, executeUpdate, and execute) say that they can execute a SQL statement, not multiple statements.
I may have misremembered when I said you can execute multiple statements with executeUpdate using HSQLDB.
Do you think the following would work? Write the SQL statements to an external file on disk, then use a Basic language command to tell MySQL to execute the file as a script.
Best regards,
Will |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Dec 28, 2010 2:18 am Post subject: |
|
|
Hello
I think Executequery and executeUpdate can only be done with one line. But execute can be done with more lines. The reason is maybe the first two give back an result. Execute give no result.
example you can use a lot of lines.
| Code: | ostatement.execute("-- stap 1" & chr(13) &_
"-- Maak een lijst van de woorden die al op genomen zijn in de data base" & chr(13) &_
"-- Dat ook al van veel eerder zijn" & chr(13) &_
"drop table ""oude_woorden"" if exists ;" & chr(13) &_
"select * into ""oude_woorden"" from ""woordenlijst"" where ""id""=" & sPrimarykey & chr(13) &_
"-- stap 2" & chr(13) &_
"-- Er is al een lijst van woorden die uit het bewerkte record komt." & chr(13) &_
"-- Woorden die niet meer in het bewerkte record staan maar nog in de database" & chr(13) &_
"-- moeten ver wijderd worden maak daar een lijst van" & chr(13) &_
"drop table ""wegdoen"" if exists ;" & chr(13) &_
"-- Selecteer nu wat wel in de woorden lijst is maar niet in de nieuwe woorden" & chr(13) &_
"select ""oud"".""id"",""oud"".""woorden"" into ""wegdoen"" " & chr(13) &_
" from ""oude_woorden"" as ""oud"" " & chr(13) &_
" where ""oud"".""woorden"" not in (select * from ""nieuw"");" & chr(13) &_
"-- stap 3" & chr(13) &_
"-- verwijder nu de woorden die in wegdoen staan" & chr(13) &_
"delete from ""woordenlijst""" & chr(13) &_
" where ""id"" in ( select ""id"" from ""wegdoen"" )" & chr(13) &_
" and " & chr(13) &_
" ""woorden"" in ( select ""woorden"" from ""wegdoen"" );" & chr(13) &_
"" & chr(13) &_
"" & chr(13) &_
"-- stap 4" & chr(13) &_
"-- zoek de woorden op die in gesorteerd moeten worden" & chr(13) &_
"insert into ""woordenlijst""" & chr(13) &_
"-- dit werkt als een query" & chr(13) &_
"select " & sPrimarykey & " as ""id"", ""woorden""" & chr(13) &_
"from ""nieuw"" " & chr(13) &_
"where ""woorden"" not in (select ""woorden"" FROM ""oude_woorden"")" & chr(13) &_
"-- stap5 " & chr(13) &_
"-- wis alle tijdelijke files" & chr(13) &_
"drop table ""wegdoen"" if exists ;" & chr(13) &_
"drop table ""oude_woorden"" if exists ;" & chr(13) &_
"drop table ""nieuw"" if exists ;")
|
A long time ago I have worked with a text file. I feed the database engine with the text file using the old basic instructions. But I think you have better to understand why you have now an error.
Can you describe what you want do. I have the idea that what you do, can be more easy done. When you want have more information what you can do with OOo-base read posts of Villeroy. He give a lot of detail information. Only reading his posts can you learn a lot
Romke |
|
| Back to top |
|
|
ChillWill
General User
Joined: 24 Jan 2006
Posts: 19
Location: Wisconsin, USA
|
| Posted: Tue Dec 28, 2010 8:52 pm Post subject: Found a workaround |
|
|
I don't think it's possible to execute multiple SQL commands with a single Basic-language "execute" command. But it is possible to use a shell() command to get MySQL to execute multiple SQL commands stored in a file on disk.
For example,
| Code: | | shell("bash -c ""/path/to/command/file/test.exe""") |
The file test.exe is
| Code: | | mysql --user=myusername --password=myMySQLpassword MyDbName < /path/to/scriptfile |
And scriptfile is a series of SQL commands. I only ran a test with 3 commands in it, but it ought to work for any set of commands that MySQL can accept from the command prompt. scriptfile can be generated by the Basic-language macro. |
|
| Back to top |
|
|
ChillWill
General User
Joined: 24 Jan 2006
Posts: 19
Location: Wisconsin, USA
|
| Posted: Wed Dec 29, 2010 8:50 pm Post subject: |
|
|
Again, thanks Romke.
Here's the code that does not work, using statement.execute:
| Code: | strSQL = "DELETE FROM TBL_SCALAR; " & chr(13)
strSQL = strSQL & "INSERT INTO TBL_SCALAR VALUES ((SELECT MAX(HOUSEHOLDINDEX) FROM TBL_HOUSEHOLD)); " & chr(13)
strSQL = strSQL & " UPDATE TBL_HOUSEHOLD SET CITY = " & chr(39) & strCity & chr(39) & ", PUBLISH = "
strSQL = strSQL & boolPublish & ", EXCLUDEWHAT = " & chr(39) & strExcludeWhat & chr(39)
strSQL = strSQL & ", POSTMARK = " & chr(39) & strDate & chr(39) & " WHERE HOUSEHOLDINDEX "
strSQL = strSQL & "= (SELECT SCALAR FROM TBL_SCALAR); "
oSQLstatement.execute(strSQL) |
The SQL command, strSQL, has this value when the code fails:
| Code: | DELETE FROM TBL_SCALAR;
INSERT INTO TBL_SCALAR VALUES ((SELECT MAX(HOUSEHOLDINDEX) FROM TBL_HOUSEHOLD));
UPDATE TBL_HOUSEHOLD SET CITY = 'Wasilla', PUBLISH = False, EXCLUDEWHAT = 'Moose-gun caliber', POSTMARK = '2010-12-29' WHERE HOUSEHOLDINDEX = (SELECT SCALAR FROM TBL_SCALAR); |
What I'm trying to do is get the primary key value for the last record in TBL_HOUSEHOLD and then set several values in that record. I use a SELECT statement to get the largest primary key value in TBL_HOUSEHOLD. The biggest value of the key will be in the last record. I put that key value into TBL_SCALAR. TBL_SCALAR has just 1 column, SCALAR, and I make sure it has only 1 row by using the DELETE FROM command before I INSERT INTO it.
When I try to run that code fragment, I get this error: | Quote: |
BASIC runtime error.
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near';
INSERT INTO TBL_SCALAR VALUES ((SELECT
MAX(HOUSEHOLDINDEX) FROM TBL_HOUSEHOL' at line 1. |
Every time I tried to run more than 1 SQL command in a single statement.execute (or .executeUpdate or .executeQuery) statement, I got this type of error. I'm pretty sure the code that was quoted in the error alert always refers to the second SQL command, which strongly suggests the problem comes from trying to do more than 1 command at a time.
Romke, are you interfacing OOo Base to MySQL, or is it some other database engine? That's the only reason I can imagine why your code example works and mine doesn't work.
Happy new year,
Will |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Thu Dec 30, 2010 2:03 am Post subject: |
|
|
Hello
I do use HSQLDB and not MySQL.
When you do this kind of things most starters do not understand how difficult it is. They start working in BASIC for making working SQL code. BASIC and SQL do not know that the other language exist. The best solution is most of the time make first all the code in real SQL so you knew you have good SQL code. Then translate the code in to BASIC.
Before testing the code in SQL I print the SQL code line by line: Real SQL code and the SQL generated by BASIC. When the two line are the same then I use the execute statement.
When you have mixed uppercase and lowercase in the variables then the variable names must be surround by a character what makes clear that it is not translate to uppercase. This character for HSQLDB is a double quote. When I need a double quote in a basic line I need to double quotes.
So I think your problem is on an other place as you maybe think.
So what is maybe the working order
a) Make all SQL lines and test them in the SQL tool
b) Print the line line by line: orginal SQl and the BASIC generated SQL and see if they are the same
c) When they are the same test it with the EXECUTE statement
I have no idea if your SQL code is good code but it seems me not the question on this moment.
Romke |
|
| Back to top |
|
|
ChillWill
General User
Joined: 24 Jan 2006
Posts: 19
Location: Wisconsin, USA
|
| Posted: Thu Dec 30, 2010 8:17 am Post subject: |
|
|
My Basic-language code generates an SQL statement (or a series of statements). I made the Basic program write the SQL into a text file immediately before it attempts to execute the SQL.
I can take that text file and paste it into the MySQL command-line interface, and it works correctly. But it does not work when the Basic-language code tries to run it with the .execute (or .executeQuery or .executeUpdate) method.
Essentially I have used your steps a, b, and c, and I have to conclude that you can run multiple SQL statements in a single .execute command if your engine is HSQLDB but not if your engine is MySQL.
The quotes issue that you mentioned can indeed be confusing. Often I use chr(34) or chr(39) because I find it less confusing than doubling up the quote marks. Also I took Roberto Benitez's advice to use all capitals, from his book "Database Programming: OpenOffice.org Base and Basic". All-caps is ugly, but you avoid some of the quotes problems.
Thanks for your help with this. It's solved to my satisfaction. I hope this discussion may help someone else who runs into the same question.
Will |
|
| Back to top |
|
|
|
|
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
|
