Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Mysterious error in EXECUTE IMMEDIATE |
Tue, Sep 8 2015 6:24 PM | Permanent Link |
David Cornelius Cornelius Concepts | 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 |
Wed, Sep 9 2015 3:08 AM | Permanent Link |
Uli Becker | 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 |
Wed, Sep 9 2015 3:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 9 2015 5:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
Nicely spotted - I totally missed that. Roy Lambert |
Thu, Sep 10 2015 10:12 AM | Permanent Link |
David Cornelius Cornelius Concepts | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |