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

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

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

David


I find the wild guess approach best!

Roy Lambert

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

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

<<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

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

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

<< 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