[freetds] FreeTDS Digest, Vol 197, Issue 4

Bruce Seely bseely at stsci.edu
Tue Jul 28 14:07:30 EDT 2020


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



More information about the FreeTDS mailing list