The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

asked 22 Aug '13, 11:02

David%20Lopez's gravatar image

David Lopez
46335
accept rate: 0%

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 :-/

(23 Aug '13, 06:16) David Lopez
Replies hidden

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.

(24 Aug '13, 04:30) Volker Barth

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.

(26 Aug '13, 10:40) David Lopez
Replies hidden

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

(26 Aug '13, 11:38) Breck Carter

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.

(27 Aug '13, 02:45) David Lopez

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

(27 Aug '13, 02:52) Volker Barth
showing 2 of 6 show all flat view

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.

permanent link

answered 22 Aug '13, 17:32

Breck%20Carter's gravatar image

Breck Carter
26.6k433604877
accept rate: 21%

edited 22 Aug '13, 17:34

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!

permanent link

answered 27 Aug '13, 08:23

David%20Lopez's gravatar image

David Lopez
46335
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×103
×90
×65

question asked: 22 Aug '13, 11:02

question was seen: 2,387 times

last updated: 27 Aug '13, 11:53