Syntax error -131 when running ScriptRunner Java class

[Former Member]

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.

[Former Member]

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 😕

[VolkerBarth]

which is supposed to take some .SQL scripts and execute them on the server.

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.

[Former Member]

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.

[Breck_Carter]

Are you calling a procedure, or creating one? You should be able to treat a CALL like an ordinary SELECT or INSERT.

[Former Member]

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.

[VolkerBarth]

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

[Breck_Carter]

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.

[Former Member]

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.

[Breck_Carter]

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

[Former Member]

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!