This PSQL example doesn't work, why??

[frasse7]

Hi, this is an exact copy from the help files of Pervasive.SQL.V8:

CREATE PROCEDURE Checkmax(in :classid integer);
Begin
DECLARE :numenrolled integer;
DECLARE :maxenrolled integer;
SELECT COUNT(*) INTO :numenrolled FROM Enrolls WHERE class_ID = :classid;
SELECT Max_size INTO :maxenrolled FROM Class WHERE id = :classid;
IF numenrolled > :maxenrolled) THEN
PRINT 'Enrollment Failed. Number of students enrolled reached maximum allowed for this class' ;

ELSE
PRINT 'Enrollment Possible. Number of students enrolled has not reached maximum allowed for this class';

END IF;

END;
CALL Checkmax(101)

When I run this in the SQL Data Manager I get the following error code:

ODBC Error: SQLSTATE = S1000, Native error code = -5099
1: '<EOF>': Syntax error

I sure am a newbie and there must be something I'm doing terribly wrong if I can't even get the examples to work.

I'm connected to the database, I can run simple select statements.

Help, please..

 

[mirtheil]

THe problem is that you're trying to run two statements without telling Pervasive where one starts and the other ends. Try:
CREATE PROCEDURE Checkmax(in :classid integer);
Begin
DECLARE :numenrolled integer;
DECLARE :maxenrolled integer;
SELECT COUNT(*) INTO :numenrolled FROM Enrolls WHERE class_ID = :classid;
SELECT Max_size INTO :maxenrolled FROM Class WHERE id = :classid;
IF numenrolled > :maxenrolled) THEN
PRINT 'Enrollment Failed. Number of students enrolled reached maximum allowed for this class' ;

ELSE
PRINT 'Enrollment Possible. Number of students enrolled has not reached maximum allowed for this class';

END IF;

END;#
CALL Checkmax(101)

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[frasse7]

It didn't help to write '#' to separate the statements, but it gave me a clue. Thing was that I had changed the separator from '#' to ';'. When I changed it back it worked.
Thanks