|
Hi all. I have such an issue with SQL Anywhere 10. I am working with it and a SyBase database version 5.0.0 I am developing an application which is supposed to take some .SQL scripts and execute them on the server. To do so I am using ibatis' ScriptRunner class, namely its method runScript. That method works sweet when executing a script which contains INSERT or UPDATE operations, but it displays com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -131: Syntax error whenever it comes across some "BEGIN" or even "DECLARE". I really do not know what is going. Anyone could help out? Thanks in advance.
This question is marked "community wiki".
|
|
A BEGIN END block has to be sent to SQL Anywhere as a single executable unit. If ScriptRunner is going to "look inside" your BEGIN and process it step by step, rather than send it all to the server in one fell swoop, you'll see a syntax error very quickly if you try anything interesting. Maybe there is some ScriptRunner syntax that will force it to pass a BEGIN block as a single unit. Thanks, Breck. However there is also the point that if I get rid (or comment) the BEGIN I still having problem because of DECLARE. runScript throws the same Exception when it comes it across :/ Anyway, I will try something like you said about forcing it into running the whole block as a single unit.
(23 Aug '13, 02:34)
David Lopez
Replies hidden
Is there not an ordinary method that executes an arbitrary SQL statement? Will that method not work if you just give it a batch in a string? For example, the following EXECUTE IMMEDIATE runs OK in PowerBuilder 11.5 with a SQL Anywhere Version 8 database; note that there is a BEGIN block followed by a statement that is outside the block... string ls_sql
ls_sql = "BEGIN " &
+ "DECLARE s VARCHAR ( 10 ); " &
+ "SET s = 'Hello'; " &
+ "MESSAGE s TO CONSOLE; " &
+ "END; " &
+ "MESSAGE 'Goodbye' TO CONSOLE;"
EXECUTE IMMEDIATE :ls_sql USING SQLCA;
IF SQLCA.SQLCODE <> 0 THEN
MessageBox ( 'Error', &
'EXECUTE IMMEDIATE failed in open:' &
+ '~r~nSQLCode = ' &
+ String ( SQLCA.SQLCode ) &
+ '~r~nSQLDBCode = ' &
+ String ( SQLCA.SQLDBCode ) &
+ '~r~n' &
+ SQLCA.SQLErrText )
RETURN
END IF
MessageBox ( 'EXECUTE IMMEDIATE', 'OK' );
Hello
Goodbye
(23 Aug '13, 09:31)
Breck Carter
|
|
DONE. In the end it was way easier than I initially thought. Volker: thanks for your reply, though the question was to do it as part of a Java Application. Breck: you put the key before me: CALLING. Java's CallableStatement allows you to call SQL procedures, so I worked out that I would only have to read all the stuff in each file to use it as the input for the methods prepareCall and execute from that very class. The reading of the commands in the file I did with Java Files' method readAllLines, whose output I put in a List of strings; I joined in all those Strings in one alone and used it as the parameter for the prepareCall to perform its wonders. Thanks so much to all who have participated in this thread! |
Hi again.
I have gone through the code of runScript on grepcode and seems like Breck was right: runScript does but taking each statement, which it recognizes as such once it finds the ";" character to mark the end of statement.
So to speak, no "BEGIN", "END", "BEGIN LOOP" or "END LOOP" are recognized by RunScript. That poses a real problem. So, unless someone here can come up with some idea to make runScript suitable to execute SQL scripts which contain all these stuff in addition to "DECLARE" and others, it seems otherwise.
Going through the code of SQLExec now, however I have also read that there is chance that the driver I am currently using to make the connection with the database server does not support batched transactions, so it only executes one operation at once.
So not a clear vision of the problem yet :-/
So, if runScript does not allow for batched statements, can't you sent the scripts directly to the database engine with JDBC? AFAIK, SQL Anywhere's JDBC driver should allow batched statements.
Hi again.
So far, I've learnt that dealing with SQL procedures is not like dealing with a SQL Script which merely contains INSERT, UPDATE or DELETE operations - I had expected otherwise at the beginning.
To do so it is necessary SQL/JRT and CallableState. Seems as it is about dealing with Java, rather than with SQL, that I will get to the end of the issue.
Are you calling a procedure, or creating one? You should be able to treat a CALL like an ordinary SELECT or INSERT.
No; the script uses CREATE PROCEDURE along with conditional blocks, loops and so on. So the CallableStatement and all the other stuff concerning SQL/JRT seems necessary as, to add up, I have gone through SQLExec and ScriptRunner codes and neither of them seem to be able to run such scripts.
Would it be an alternative to start dbisql (or the "smaller" dbisqlc) as a (possibly hidden) command to apply these scripts? These SQL Anywhere tools are made to run scripts and surely can handle statement blocks and the like, apparently...