Skip to Content.
Sympa Menu

freetds - Re: [freetds] FreeTDS Digest, Vol 197, Issue 4

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Bruce Seely <bseely AT stsci.edu>
  • To: "freetds AT lists.ibiblio.org" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] FreeTDS Digest, Vol 197, Issue 4
  • Date: Tue, 28 Jul 2020 18:07:30 +0000


The problem is that, from an sql call, we get the contents of the columns,
but while SQLColAttribute is collecting the column names, it fails.
When it fails, SQLGetDiagRec produces no reason for the failure, so we don’t
know what is wrong with the SQLColAttribute invocation.

Below is
- the definition of SQLGetDiagRec
- the code that calls SQLGetDiagRec to collect messages
- an example of SQLGetDiagRec working correctly sql = "use pps_lrp_alpha”
- an example where SQLColAttribute gets an error, and no reason is returned
sql = "select * from ppsdb_version”

Sorry to dump so much stuff, but I wanted to try to proactively answer an
many questions as possible.

The code is in Lisp (sorry), so SQLGetDiagRec is called through a foreign
function interface.
This is the declaration of SQLGetDiagRec as a foreign function:

(def-foreign-call-sc (SQLGetDiagRec "SQLGetDiagRec")
((type :short)
(handle odbc-handle)
(recnum :short)
(state (* :char))
(error-code (* odbc-int-box))
(error-message (* :char))
(size-error-message :int)
(used-error-message (* odbc-int-box)))

:arg-checking nil ; due to contraversial design in ff, must turn off arg ck
:returning :short)

odbc-int-box is defined as:
(def-foreign-type (odbc-int-box (:accessor odbc-int-box))
(:struct (data :int)))

The code calling SQLGetDiagRec:
(defun get-pending-messages-v3 (henv hdbc hstmt)
;; return all pending diagnostic messages
(with-stack-fobjects ((sqlstate '(:array :char 10))
(error-code 'odbc-int-box)
(err-buffer
'(:array :char #.SQL_MAX_MESSAGE_LENGTH))
(text-size 'odbc-int-box))
(let ((recnum 0)
(res nil))
(loop (let ((retval (SQLGetDiagRec
(if* hstmt
then SQL_HANDLE_STMT
elseif hdbc
then SQL_HANDLE_DBC
else SQL_HANDLE_ENV)
(or hstmt hdbc henv)
(incf recnum)
sqlstate
error-code
err-buffer
#.SQL_MAX_MESSAGE_LENGTH
text-size)))
(case retval
(#.SQL_SUCCESS
(push (list
(native-to-string
(fslot-address-typed
'foreign-string
:foreign
sqlstate
0))

(fslot-value-typed 'odbc-int-box
:foreign
error-code
:data)
(native-to-string
(fslot-address-typed
'foreign-string
:foreign
err-buffer
0)))
res))
(#.SQL_NO_DATA
(return) ; no more data, leave loop
)
(t (warn "Unexpected return code ~s from GetDiagRec"
retval)
(return)))))
(nreverse res))))


retval is the value returned by the SQLGetDiagRec call
retval is tested against #.SQL_SUCCESS (which is 0), #.SQL_NO_DATA (which is
100) and t which is the default.
if retval is #.SQL_SUCCESS, the returned string is added to the res variable
which is returned when there are no more messages

_______________________________________________
Here’s an example of SQLGetDiagRec working correctly…

I used a wrapper to print the types of the arguments used in the
SQLGetDiagRec call


sql is "use pps_lrp_alpha”
...
9[14]: (RR-SQL
#<ODBC-HSTMT ODBC-HSTMT #x7fb90040f0d0 @
#x110d45ddc2>
"use pps_lrp_alpha"
:DB #<ODBC-DB
"DSN=R2D2;UID=bseely;PWD=xxxxxxxxxxxx"
@
#x110d452ec2>)
>>doing (WITH-NATIVE-STRING-ODBC (SQL-STATEMENT SQL-STATEMENT DB)
(SQLEXECDIRECT HSTMT SQL-STATEMENT SQL_NTS))
10[14]: (GET-PENDING-MESSAGES-V3 NIL NIL
#<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>)
type: FIXNUM
handle: ODBC-HSTMT
recnum: FIXNUM
state: (SHORT-SIMPLE-ARRAY FOREIGN (4))
error-code: (SHORT-SIMPLE-ARRAY FOREIGN (2))
error-message: (SHORT-SIMPLE-ARRAY FOREIGN (129))
size-error-message: FIXNUM
used-error-message: (SHORT-SIMPLE-ARRAY FOREIGN (2))
11[14]: (SQLGETDIAGREC
3
#<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>
1
#<foreign object of class #:|anon-type-7|>
#<foreign object of class ODBC-INT-BOX>
#<foreign object of class #:|anon-type-6|>
512
#<foreign object of class ODBC-INT-BOX>)
11[14]: returned 0
-> retval: 0
-> res: (("01000" 5701
"[FreeTDS][SQL Server]Changed database context to
'pps_lrp_alpha'."))
type: FIXNUM
handle: ODBC-HSTMT
recnum: FIXNUM
state: (SHORT-SIMPLE-ARRAY FOREIGN (4))
error-code: (SHORT-SIMPLE-ARRAY FOREIGN (2))
error-message: (SHORT-SIMPLE-ARRAY FOREIGN (129))
size-error-message: FIXNUM
used-error-message: (SHORT-SIMPLE-ARRAY FOREIGN (2))
11[14]: (SQLGETDIAGREC
3
#<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>
2
#<foreign object of class #:|anon-type-7|>
#<foreign object of class ODBC-INT-BOX>
#<foreign object of class #:|anon-type-6|>
512
#<foreign object of class ODBC-INT-BOX>)
11[14]: returned 100
-> retval: 100
10[14]: returned
(("01000"
5701
"[FreeTDS][SQL Server]Changed database
context to 'pps_lrp_alpha'."))
!!! sql-statement: "use pps_lrp_alpha"
!!! hstmt: #<ODBC-HSTMT ODBC-HSTMT #x7fb90040f0d0 @ #x110d45ddc2>
!!! errmsg: (("01000" 5701
"[FreeTDS][SQL Server]Changed database context to
'pps_lrp_alpha'."))
!!! result: 1
9[14]: returned NIL



You can see that retval is first 0 (success), and res is (("01000" 5701
"[FreeTDS][SQL Server]Changed database context to
'pps_lrp_alpha'."))
then retval is 100 (no data)
and the message is returned


_____________________________________________________________________________________
Here’s an example where SQLColAttribute gets an error, and no reason is
returned…

9[14]: (RR-SQL
#<ODBC-HSTMT ODBC-HSTMT #x7fb90040f0d0 @
#x110d45ddc2>
"select * from ppsdb_version"
:DB #<ODBC-DB
"DSN=R2D2;UID=bseely;PWD=xxxxxxxxxxxx"
@
#x110d452ec2>)
>>doing (WITH-NATIVE-STRING-ODBC (SQL-STATEMENT SQL-STATEMENT DB)
(SQLEXECDIRECT HSTMT SQL-STATEMENT SQL_NTS))
<< and it returns 0

9[14]: returned NIL

+++++ in loop-over-results macro, called by with-row-results macro+++++
>>doing (SQLNUMRESULTCOLS HSTMT COUNT-VAR)
9[14]: (SQLNUMRESULTCOLS #<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>
#<foreign object of class
ODBC-SHORT-BOX>)
9[14]: returned 0
<< and it returns 0
9[14]: (GET-STRING-RESULT-COLUMN-ATTRIBUTE
#<ODBC-HSTMT ODBC-HSTMT #x7fb90040f0d0 @
#x110d45ddc2>
1 1011)


!! (get-string-result-column-attribute hstmt column attribute)
!! arguments of SQLCOLATTRIBUTE
!! hstmt: #<ODBC-HSTMT ODBC-HSTMT #x7fb90040f0d0 @
#x110d45ddc2>:
ODBC-HSTMT
!! column-number: 1:
FIXNUM
!! attribute-code: 1011:
FIXNUM
!! return-value: #<foreign object of class #:|anon-type-5|>:
(SHORT-SIMPLE-ARRAY

FOREIGN

(65))
!! buffer-length: 64:
FIXNUM
!! string-length: #<foreign object of class ODBC-SHORT-BOX>:
(SHORT-SIMPLE-ARRAY

FOREIGN

(2))
!! num-value: 0:
FIXNUM



10[14]: (SQLCOLATTRIBUTE #<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>
1 1011
#<foreign object of class
#:|anon-type-5|>
64
#<foreign object of class
ODBC-SHORT-BOX>
0)
10[14]: returned -1
10[14]: (GET-PENDING-MESSAGES-V3 NIL NIL
#<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>)
type: FIXNUM
handle: ODBC-HSTMT
recnum: FIXNUM
state: (SHORT-SIMPLE-ARRAY FOREIGN (4))
error-code: (SHORT-SIMPLE-ARRAY FOREIGN (2))
error-message: (SHORT-SIMPLE-ARRAY FOREIGN (129))
size-error-message: FIXNUM
used-error-message: (SHORT-SIMPLE-ARRAY FOREIGN (2))
11[14]: (SQLGETDIAGREC
3
#<ODBC-HSTMT
ODBC-HSTMT #x7fb90040f0d0
@
#x110d45ddc2>
1
#<foreign object of class #:|anon-type-7|>
#<foreign object of class ODBC-INT-BOX>
#<foreign object of class #:|anon-type-6|>
512
#<foreign object of class ODBC-INT-BOX>)
11[14]: returned 100
-> retval: 100
10[14]: returned NIL


the SQLCOLATTRIBUTE call returned -1
then GET-PENDING-MESSAGES-V3 calls SQLGETDIAGREC to get any messages
SQLGETDIAGREC returns 100, indicating that it has no data













On Jul 28, 2020, at 12:00 PM,
freetds-request AT lists.ibiblio.org<mailto:freetds-request AT lists.ibiblio.org>
wrote:

External Email - Use Caution

Send FreeTDS mailing list submissions to
freetds AT lists.ibiblio.org<mailto:freetds AT lists.ibiblio.org>

To subscribe or unsubscribe via the World Wide Web, visit
https://lists.ibiblio.org/mailman/listinfo/freetds
or, via email, send a message with subject or body 'help' to

freetds-request AT lists.ibiblio.org<mailto:freetds-request AT lists.ibiblio.org>

You can reach the person managing the list at
freetds-owner AT lists.ibiblio.org<mailto:freetds-owner AT lists.ibiblio.org>

When replying, please edit your Subject line so it is more specific
than "Re: Contents of FreeTDS digest..."
Today's Topics:

1. Obtaining diagnostic messages (Bruce Seely)
2. Re: Obtaining diagnostic messages (Igor Korot)

From: Bruce Seely <bseely AT stsci.edu<mailto:bseely AT stsci.edu>>
Subject: [freetds] Obtaining diagnostic messages
Date: July 27, 2020 at 1:55:54 PM EDT
To: "freetds AT lists.ibiblio.org<mailto:freetds AT lists.ibiblio.org>"
<freetds AT lists.ibiblio.org<mailto:freetds AT lists.ibiblio.org>>


How can I get the error reason when an error code is returned by a call to
SQLColAttribute?

SQLColAttribute is called with these arguments:
SQLHSTMT StatementHandle, odbc_hstmt
SQLUSMALLINT ColumnNumber, 1
SQLUSMALLINT FieldIdentifier, 1011
SQLPOINTER CharacterAttributePtr, STACK-ALLOCATED
(SHORT-SIMPLE-ARRAY FOREIGN (65))
SQLSMALLINT BufferLength, 255 (also tried 65)
SQLSMALLINT * StringLengthPtr, STACK-ALLOCATED (SHORT-SIMPLE-ARRAY
FOREIGN (2))
SQLLEN * NumericAttributePtr); 0

The returned value is -1.

When pending messages are retrieved using SQLGetDiagRec, no messages are
found.
What can I do to determine why the call is failing?

Thank you,
Bruce




From: Igor Korot <ikorot01 AT gmail.com<mailto:ikorot01 AT gmail.com>>
Subject: Re: [freetds] Obtaining diagnostic messages
Date: July 27, 2020 at 5:46:59 PM EDT
To: FreeTDS Development Group
<freetds AT lists.ibiblio.org<mailto:freetds AT lists.ibiblio.org>>


Hi,

On Mon, Jul 27, 2020 at 1:16 PM Bruce Seely
<bseely AT stsci.edu<mailto:bseely AT stsci.edu>> wrote:

How can I get the error reason when an error code is returned by a call to
SQLColAttribute?

SQLColAttribute is called with these arguments:
SQLHSTMT StatementHandle, odbc_hstmt
SQLUSMALLINT ColumnNumber, 1
SQLUSMALLINT FieldIdentifier, 1011
SQLPOINTER CharacterAttributePtr, STACK-ALLOCATED
(SHORT-SIMPLE-ARRAY FOREIGN (65))
SQLSMALLINT BufferLength, 255 (also tried 65)
SQLSMALLINT * StringLengthPtr, STACK-ALLOCATED (SHORT-SIMPLE-ARRAY
FOREIGN (2))
SQLLEN * NumericAttributePtr); 0

The returned value is -1.

When pending messages are retrieved using SQLGetDiagRec, no messages are
found.
What can I do to determine why the call is failing?

Can you show the code of how do you call SQLGetDiagRec() and what is
happening?

Thank you.


Thank you,
Bruce
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org<mailto:FreeTDS AT lists.ibiblio.org>
https://lists.ibiblio.org/mailman/listinfo/freetds



_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org<mailto:FreeTDS AT lists.ibiblio.org>
https://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page