I've written an application for a small company where each person works from home and accesses and EDB database hosted in a virtual Windows server. To prevent two people from editing the same record at the same time, I implemented a locking mechanism in nine of the tables where I've added several fields to indicate where, when, and who locked the record. The fields are changed when an edit dialog is brought up, then changed again when their edits are either saved or canceled. The only problem I've had so far is if someone's computer loses internet connection leaving the record marked as "locked" (not by the database, but by the values of the "LockedXXX" fields I've added).

Just before bringing up the edit dialog where the user will make their changes, the application queries the "LockedXXX" fields to see if it's locked by another user. I have been using straight SQL to do this but would like to put it into a stored procedure. Since there are nine different tables that can be locked, I pass in the table name and it builds the SQL dynamically and runs it with EXECUTE IMMEDIATE. Or at least that's the goal.  I keep getting errors that not only make me unsure where the error is or what it's referring to.

I've listed the stored procedure below. In my database, I don't use integer IDs, but GUIDs (it helped to prevent duplicate records when the earlier version of this program used local databases and they all synchronized with the cloud-based one). The locked state of a record is indicated by whether the LockedCount field is odd (locked) or even (not locked).

ALTER PROCEDURE "LockInfo" (
  IN "TableName" VARCHAR(50),
  IN "RecordGUID" GUID,
  OUT "IsLocked" BOOLEAN,
  OUT "LockedByStaffGUID" GUID,
  OUT "LockedByStaffName" VARCHAR(30),
  OUT "LockedAtTimeStamp" TIMESTAMP)
BEGIN                                   
 EXECUTE IMMEDIATE
  'SELECT (LockedCount/2 <> TRUNC(LockedCount/2)) INTO ?, ' +
  '       LockedByGUID INTO ?, ' +
  '       s.Name INTO ?, ' +
  '       LockedTimeStamp INTO ? ' +
  'FROM ' + TableName + ' ' +
  'JOIN Staff s ON LockedByGUID = s.GUID ' +
  'WHERE GUID = ? '
  USING IsLocked, LockedByStaffGUID, LockedByStaffName, LockedAtTimeStamp, RecordGUID;

 /*
   For example (a valid query used to test):

   SELECT LockedCount/2 <> TRUNC(LockedCount/2), t.LockedByGUID, s.Name, t.LockedTimeStamp
   FROM InvoiceHeaders t
   JOIN Staff s ON t.LockedByGUID = s.GUID
   WHERE GUID = '{99860E86-F20D-4357-B7A6-1F622DEDA5F8}'
 */

 IF IsLocked IS NULL THEN
   SET IsLocked = False;
 END IF;
END


The example query in comments is what I build up in the application and works fine. It's what I expected to be built in the stored procedure. Instead I get the following error:

"ElevateDB error #700: An error was found in the statement at line 6 and column 4 (Expected end of expression but instead found INTO)"

I've looked at this over and over and reformatted it and pulled out the SQL to run it manually (actually, this proc was built from SQL that ran fine) but am at a loss. A previous version of the SQL added "t" as a table alias for TableName:

 EXECUTE IMMEDIATE
  'SELECT (t.LockedCount/2 <> TRUNC(t.LockedCount/2)) INTO ?, ' +
  '       t.LockedByGUID INTO ?, ' +
  '       s.Name INTO ?, ' +
  '       t.LockedTimeStamp INTO ? ' +
  'FROM ' + TableName + ' t ' +
  'JOIN Staff s ON t.LockedByGUID = s.GUID ' +
  'WHERE t.GUID = ? '
  USING IsLocked, LockedByStaffGUID, LockedByStaffName, LockedAtTimeStamp, RecordGUID;

but that gave the same error number at the same location but with this inner text:

"Invalid expression . found, table qualifier not allowed"

Does anyone see anything I missed here?
--
David Cornelius
Cornelius Concepts

David,

you seem to use a wrong syntax, try this:

BEGIN
  EXECUTE IMMEDIATE
   'SELECT (LockedCount/2 <> TRUNC(LockedCount/2)), ' +
   '       LockedByGUID, ' +
   '       s.Name, ' +
   '       LockedTimeStamp INTO ?,?,?,? ' +
   'FROM ' + TableName + ' ' +
   'JOIN Staff s ON LockedByGUID = s.GUID ' +
   'WHERE GUID = ? '
   USING IsLocked, LockedByStaffGUID, LockedByStaffName,
LockedAtTimeStamp, RecordGUID;
END

Uli

David


I think the problem is how you're expecting EXECUTE IMMEDIATE to run. Think of it as shelling out to a separate process.- there isn't communication between it and the rest of the SP. The line number and error messages are complicated by this lack of communication (now watch Tim destroy my argument


What you need to do is write the SP so its all integral.Something along the lines of

ALTER PROCEDURE "LockInfo" (
  IN "TableName" VARCHAR(50),
  IN "RecordGUID" GUID,
  OUT "IsLocked" BOOLEAN,
  OUT "LockedByStaffGUID" GUID,
  OUT "LockedByStaffName" VARCHAR(30),
  OUT "LockedAtTimeStamp" TIMESTAMP)
BEGIN

DECLARE SQLCode VARCHAR DEFAULT '';
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

SET SQLCode =    'SELECT (LockedCount/2 <> TRUNC(LockedCount/2)) INTO ?, ' +
  '       LockedByGUID INTO ?, ' +
  '       s.Name INTO ?, ' +
  '       LockedTimeStamp INTO ? ' +
  'FROM ' + TableName + ' ' +
  'JOIN Staff s ON LockedByGUID = s.GUID ' +
  'WHERE GUID = ? ';

PREPARE InfoStmt FROM SQLCode;
OPEN InfoCursor USING IsLocked, LockedByStaffGUID, LockedByStaffName, LockedAtTimeStamp, RecordGUID;

 IF IsLocked IS NULL THEN
   SET IsLocked = False;
 END IF;
END


Totally untested and not something I'm familiar with so be prepared for it to blow up and destroy your PC.



Roy Lambert

Uli

Nicely spotted - I totally missed that.


Roy Lambert

Uli,

<< you seem to use a wrong syntax, try this:

BEGIN
  EXECUTE IMMEDIATE
   'SELECT (LockedCount/2 <> TRUNC(LockedCount/2)), ' +
   '       LockedByGUID, ' +
   '       s.Name, ' +
   '       LockedTimeStamp INTO ?,?,?,? ' + >>

That was it!  All this time, and I thought the "INTO ?" part was supposed to be applied to each field. I guess I've never used more than one at a time before.

Thank you for pointing that out to me!  
--
David Cornelius
Cornelius Concepts