FreeTDS Development Group

[Bruce Seely <bseely AT stsci.edu>]

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