I notice that ISQL is able to get the line number that an error was found on when running bad SQL. What ODBC API calls does it use to achieve this?

With other databases you would use a combination of SQLGetDiagRec and SQLGetDiagField. This doesn't seem to be the case for SQL Anywhere.

Thanks,

Error message from Interactive SQL... How does it know that the error is on line 4 column 1

alt text

asked 17 Apr '10, 22:01

Brad%20Wery's gravatar image

Brad Wery
382192126
accept rate: 0%

edited 21 Apr '10, 01:19

Comment Text Removed

Just to confirm, this is from ASA V7, right? And is this DBISQL session using the JDBC-ODBC bridge, or is it using jConnect?

(21 Apr '10, 02:18) Glenn Paulley

This is ASA 7. In Interactive SQL I just connect to a registered ODBC data source. Now I see on the advanced tab that there is an option selected called jConnect 5. I guess that's what it's using.

(21 Apr '10, 02:58) Brad Wery

While DBISQL does parse its input to determine the rudiments of the input SQL syntax, DBISQL has no idea precisely where an error in a statement or procedure occurred - only the server does. There is no specific mechanism for the server to return a line number or statement offset when an SQL statement is rejected, short of the error message itself. This is true with both JDBC/ODBC and with jConnect.

What you're seeing in DBISQL V7 is an estimate from DBISQL as to where the error occurred. That estimate, in your case, is simply the last line of the input. If you connect the V7 DBISQL client to a Version 11 server, try the following statement:

SELECT
*
FLUM
dept

and you'll see that DBISQL again reports the error on line 4, column 1 (the last line of the input) but the server message (properly) says

Error -131: Syntax error near 'flum' on line 3

permanent link

answered 21 Apr '10, 14:11

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

edited 21 Apr '10, 15:15

1

FLUM is ANSI-compliant, is it not? Is it planned for a future version?

(21 Apr '10, 15:45) Breck Carter

Okay, fair enough. Thanks for your help.

(21 Apr '10, 18:24) Brad Wery

@Breck: That's why they are introducing the "reserved_keywords" option... cf. Glenn's article http://iablog.sybase.com/paulley/2010/04/keywords-and-upgrades :)

(22 Apr '10, 15:05) Volker Barth

If using the iAnywhere Type 1 driver, which uses ODBC underneath JDBC, DBISQL (eventually) causes the issuing of an ODBC SQLGetDiagRecW() call (the "W" denoting the unicode version) to get error information on a statement. You can see this sequence if using the Type 1 JDBC driver and enable ODBC tracing. Here is an example:

In a DBISQL window, I code the (erroneous) procedure

create procedure foo (in x integer, in y integer )
begin
   declare z integer;
   declare bar cursor for select * from ;
   select z = 50;
end

and press F9. The error "Syntax error near ';' on line 4" is returned to DBISQL. The ODBC trace reveals:

DBISQLG         41ec-40ac   ENTER SQLPrepareW 
        HSTMT               04C12B58
        WCHAR *             0x03371800 [     137] "create procedure foo (in x integer, in y integer )\ abegin \ a declare z integer;\ a declare bar cursor for select * from ;\ a select z = 50;\ aend"
        SDWORD                   137

DBISQLG         41ec-40ac   EXIT  SQLPrepareW  with return code -1 (SQL_ERROR)
        HSTMT               04C12B58
        WCHAR *             0x03371800 [     137] "create procedure foo (in x integer, in y integer )\ abegin \ a declare z integer;\ a declare bar cursor for select * from ;\ a select z = 50;\ aend"
        SDWORD                   137

DIAG [42000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error near ';' on line 4 (-131)

DBISQLG         41ec-40ac   ENTER SQLGetDiagRecW 
        SQLSMALLINT                  3 
        SQLHANDLE           04C12B58
        SQLSMALLINT                  1 
        SQLWCHAR *          0x04B7F28C (NYI) 
        SQLINTEGER *        0x04B7F288
        SQLWCHAR *          0x04B7F29C (NYI) 
        SQLSMALLINT               1024 
        SQLSMALLINT *       0x04B7F298

DBISQLG         41ec-40ac   EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 
        SQLHANDLE           04C12B58
        SQLSMALLINT                  1 
        SQLWCHAR *          0x04B7F28C (NYI) 
        SQLINTEGER *        0x04B7F288 (-131)
        SQLWCHAR *          0x04B7F29C (NYI) 
        SQLSMALLINT               1024 
        SQLSMALLINT *       0x04B7F298 (78)

DBISQLG         41ec-40ac   ENTER SQLGetDiagRecW 
        SQLSMALLINT                  3 
        SQLHANDLE           04C12B58
        SQLSMALLINT                  2 
        SQLWCHAR *          0x04B7F28C (NYI) 
        SQLINTEGER *        0x04B7F288
        SQLWCHAR *          0x04B7F29C (NYI) 
        SQLSMALLINT               1024 
        SQLSMALLINT *       0x04B7F298

DBISQLG         41ec-40ac   EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT                  3 
        SQLHANDLE           04C12B58
        SQLSMALLINT                  2 
        SQLWCHAR *          0x04B7F28C (NYI) 
        SQLINTEGER *        0x04B7F288
        SQLWCHAR *          0x04B7F29C (NYI) 
        SQLSMALLINT               1024 
        SQLSMALLINT *       0x04B7F298

DBISQLG         41ec-40ac   ENTER SQLFreeStmt 
        HSTMT               04C12B58
        UWORD                        3 <SQL_RESET_PARAMS>

The SQLGetDiagRecW() call returns the complete error message in the first call. The error message itself is generated by the server - there is no separate parameter returned that indicates the line number the error occurred on.

Note the (NYI) seemingly returned by ODBC is a result of using the unicode variant of SQLGetDiagRec() - the ODBC trace can't handle unicode arguments, so the output tends to get garbled in the trace.

permanent link

answered 20 Apr '10, 16:00

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

I see in newer version of SQL Anywhere that the line number is in the error message. I currently parse the error message to get this information. While testing on SQL Anywhere 7, I noticed that the line number was not in the error message. ISQL is still able to get the line number. This tells me that there is another way to get it. Maybe an SQL Anywhere function (not an ODBC function call).

(20 Apr '10, 16:10) Brad Wery

I don't know offhand what that mechanism might be. Do you have a concrete example to show me?

(20 Apr '10, 21:47) Glenn Paulley
Comment Text Removed
Comment Text Removed

I added a screen shot to the question. SQLGetDiagRecW, for me, only returns "Table 'dept' not found". No where can I find how to get the line number or column.

(21 Apr '10, 01:20) Brad Wery
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125

question asked: 17 Apr '10, 22:01

question was seen: 1,671 times

last updated: 21 Apr '10, 15:15