![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
![]() |
Tue, Sep 8 2015 3:31 PM | Permanent Link |
David Cornelius Cornelius Concepts | I have a stored procedure that runs an EXECUTE IMMEDIATE statement and I've looked and looked at it and cannot figure out what the problem is. I'll probably post another question to the forum shortly, but the error I get in my application says "An error was found in the statement at line 6 and column 4".
But how do I count the lines and columns so I know where to look for the error? Is this referring to the stored procedure or the embedded EXECUTE statement? Assuming it's the EXECUTE statement, the original version was only 3 lines long, but since it's a string and built dynamically, I would think it would be considered all one line. So then the error is NOT in the EXECUTE statement? There's not much more to that stored procedure. I broke the string out onto several lines and re-ran the app but got the same error with the same line/column numbers. I could post the SQL here, but I'm really curious how the lines and columns are calculated in EDB error messages as I've had this trouble before, albeit to a lesser extent. This time, I'm really puzzled and I'd like to be able to understand this for the future. Thanks! -- David Cornelius Cornelius Concepts |
Wed, Sep 9 2015 2:56 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | David
I have the same problem and I bet Tim's official answer is "it gets complicated" I generally try starting with 0 and if that doesn't seem to help start with 1 ![]() Roy Lambert |
Thu, Sep 10 2015 12:46 AM | Permanent Link |
David Cornelius Cornelius Concepts | So do we count characters and divide by the average number of characters per line? Is it based on the number of fields or expressions in the SELECT clause? Might just be the number of semicolons and commas.
![]() -- David Cornelius Cornelius Concepts |
Thu, Sep 10 2015 8:48 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | David
I find the wild guess approach best! Roy Lambert |
Sat, Sep 12 2015 8:36 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | David,
<< I have a stored procedure that runs an EXECUTE IMMEDIATE statement and I've looked and looked at it and cannot figure out what the problem is. I'll probably post another question to the forum shortly, but the error I get in my application says "An error was found in the statement at line 6 and column 4". But how do I count the lines and columns so I know where to look for the error? Is this referring to the stored procedure or the embedded EXECUTE statement? >> Here's an example: CREATE PROCEDURE "TestExecute" () BEGIN EXECUTE IMMEDIATE 'CREATE TABLE "MyTable" ( "ID" INTEGERS, "LastUpdated" TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP() )'; END This will result in this: --------------------------- ElevateDB Manager --------------------------- ElevateDB Error #700 An error was found in the statement at line 3 and column 22 (Expected data type but instead found INTEGERS) --------------------------- OK --------------------------- If you look, you will see that line 3, column 22 is the location of the beginning of the text for the EXECUTE IMMEDIATE. In other words, the line/column information is for the *procedure*, not the EXECUTE IMMEDIATE. Tim Young Elevate Software www.elevatesoft.com |
Sun, Sep 13 2015 3:18 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
Can't remember if I've asked for this before but it would be most helpful if the line giving the error was displayed. Roy Lambert |
Thu, Sep 17 2015 12:02 AM | Permanent Link |
David Cornelius Cornelius Concepts | <<If you look, you will see that line 3, column 22 is the location of the beginning of the text for the EXECUTE IMMEDIATE. In other words, the line/column information is for the *procedure*, not the EXECUTE IMMEDIATE.>>
Thanks, Tim! -- David Cornelius Cornelius Concepts |
Thu, Sep 17 2015 12:09 AM | Permanent Link |
David Cornelius Cornelius Concepts | So how is the count when there are parameters to the stored procedure? For example in my other post about multiple INTOs, I listed the following SQL:
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; ... and the error was in line 6 and column 4. As listed, that's in the middle of the list of parameters. Does EDB put all the parameters on the same line when parsing? If so, line 6 would after the EXECUTE IMMEDIATE line (which was found to contain the error). And what starts on column 4 here? I guess this was the part that was confusing me. Of course I know now where the problem was in this particular example, I'm just trying to understand how the position is calculated internally. Thanks, -- David Cornelius Cornelius Concepts |
Thu, Sep 17 2015 9:04 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | David
<< and the error was in line 6 and column 4. As listed, that's in the middle of the list of parameters. Does EDB put all the parameters on the same line when parsing? If so, line 6 would after the EXECUTE IMMEDIATE line (which was found to contain the error). And what starts on column 4 here? >> That's not what I'm seeing here. I'm seeing this: --------------------------- ElevateDB Manager --------------------------- ElevateDB Error #700 An error was found in the statement at line 10 and column 2 (Expected end of expression but instead found INTO) --------------------------- OK --------------------------- Line 10, Column 2 is the beginning of the EXECUTE IMMEDIATE SQL, which is correct. Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 18 2015 9:31 AM | Permanent Link |
David Cornelius Cornelius Concepts | << That's not what I'm seeing here. I'm seeing this:
Line 10, Column 2 is the beginning of the EXECUTE IMMEDIATE SQL, which is correct.>> The line and column numbering is based on how the stored procedure is declared! To make the post easier to read, I had word-wrapped each of the parameters to its own line, but in the database where I was testing, they were on one or two lines. I didn't think spacing would matter. Without changing any of the code but simply wrapping the lines differently and re-running the SP, I got the error at line 5, column 20, which in the different format, pointed to the same point of the code. I'm so accustomed to compilers removing all lines and spacing, it just didn't occur to me that it would work this way! Now I know--and it'll be much easier to pin-point problems in the future. ![]() Thanks, -- David Cornelius Cornelius Concepts |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |