Hello,

I have problem with TSQLScript (?)
Script looks like that

SET TERM ^ ;
CREATE PROCEDURE GET_USER_PRIVILEGES (
USER_ACCOUNT VARCHAR(10))
RETURNS (
RESULT CHAR(1))
AS
BEGIN
FOR
SELECT DISTINCT RDB$PRIVILEGE
FROM RDB$USER_PRIVILEGES
WHERE (RDB$USER = :USER_ACCOUNT)
INTO :RESULT
DO

      BEGIN
           SUSPEND;
      END

END
^
SET TERM ; ^

and the error message looks like that
{...}PrepareStatement
-Dynamic SQL Error
-SQL Error Code = -104
Token unknown - line 11, column 11
-?

line 11 -> INTO :RESULT
column 11-> :

FlameRobin does not have any problems with this piece of code

Any hints ?
Paul

I use Lazarus 1.0.10, Firebird 2.5 on Ubuntu 32bit -------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130718/9e688dee/attachment-0001.html>

Not really, except it's a Firebird error and it would be interested to know what actually gets passed to the Firebird engine (to verify if that runs in FlameRobin).
Perhaps sqldb logging [1] would work for that?

[1]
http://wiki.lazarus.freepascal.org/SqlDBHowto#Troubleshooting:_TSQLConnection_logging

I think you have to set TSQLScript.UseSetTerm:=true. TSQLScript is parsing the script and executes queries one by one and needs to know what the "real" terminator is.
http://www.freepascal.org/docs-html/fcl/sqldb/tsqlscript.usesetterm.html

Ludo

W dniu 19.07.2013 10:48, Ludo Brands pisze:

UseSetTerm is set to TRUE and Terminator to ";"
Without "SET TERM" directive in the script and terminator set to ^ result is the same :(

I tested other similar procedures, every time problem is somewhere around "INTO :VARIABLE"
in the procedure code.

Paul

W dniu 19.07.2013 09:38, Reinier Olislagers pisze:

Only TSQLScript.OnException is triggered with error message as above I don't know why but TIBConnection.OnLog event isn't triggered at all (even when TSQLScript.OnException is not assigned)

Paul
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130719/16365200/attachment.html>

W dniu 19.07.2013 10:48, Ludo Brands pisze:

TSQLScript.OnException
Exception.Message shows statement to run like this (without any terminator)

CREATE PROCEDURE GET_USER_PRIVILEGES (
USER_ACCOUNT VARCHAR(10))
RETURNS (
RESULT CHAR(1))
AS
BEGIN
FOR
SELECT DISTINCT RDB$PRIVILEGE
FROM RDB$USER_PRIVILEGES
WHERE (RDB$USER = :USER_ACCOUNT)
INTO :RESULT
DO

      BEGIN
           SUSPEND;
      END

END

Paul

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130719/d990fd6b/attachment-0001.html>