ODBC Database !!

[Ralph Berger]

Hello Paul,
Hello David,

GPF's with SQL fcts are most lickely from insufficent allocated
memory. You'll have to check the RetVals form SQLAllocEnv,
SQLAllocConnect and SQLAllocStmt before you try SQLConnect or
SQLExecDirect.


rgds
Ralph


------------------

[Paul Dwyer]

Thanks!

I was getting a couple of reports of GPFs on some systems. (just not on mine! )

Cheers

------------------

Paul Dwyer
Network Engineer
Aussie in Tokyo
(Paul282 at VB-World)

[David L Morris]

Paul,

I was getting a GPF when running your example just now against
a Sybase 11 database until I made the following adjustments:-

REDIM fields(1 TO colcount1)
REDIM cbValues(1 TO colcount1)

FOR i = 1 TO 15
'cbValues(i) = %SQL_NTS 'The GPF seemed to occur here
NEXT
FOR i = 1 TO ColCount1
'I changed this line at the end >>
Ret = SQLBindCol(hstmt, i, %SQL_C_CHAR, fields(i), SIZEOF(fields(i)), %SQL_NTS ) 'cbValues(i) )
NEXT

Now it works fine.

Thanks for the example.

David

------------------

[Paul Dwyer]

I got it to work!

Thanks again Ralph, I'm sure you can still see bits of your code in this.

This is a full project for people who want to load the contents of a select SQL statement into an array (and in this case from there to a text box)
just copy and paste and you're done. It's still a bit rough around the edges, particularly in error checking. It also needs a doEvents statement if reccount mod 100 = 0 or something to let you quit early, but for those VB programmers like me who were wondering what PB did for ODBC without forking dough out for 3rd party componants this ought to get you on your way

#COMPILE EXE
OPTION EXPLICIT

#INCLUDE "win32api.inc"
#INCLUDE "SQL32.INC"      ' downloadable from PB's FTP site
#INCLUDE "SQLEXT32.INC"   ' downloadable from PB's FTP site

%FORM1_LBLPASS            = 100
%FORM1_LBLUID             = 105
%FORM1_LBLDSN             = 110
%FORM1_LBLSQL             = 113
%FORM1_TXTDATA            = 115
%FORM1_TXTDSN             = 120
%FORM1_TXTUSERID          = 125
%FORM1_TXTPASSWORD        = 130
%FORM1_CMDGETDATA         = 135
%FORM1_TXTSQL             = 140


DECLARE SUB ShowDialog_Form1(BYVAL hParent&)
DECLARE CALLBACK FUNCTION Form1_DLGPROC


DECLARE CALLBACK FUNCTION CBF_FORM1_CMDGETDATA()


GLOBAL hForm1&    ' Dialog handle
GLOBAL Result AS LONG  'dialog result


FUNCTION PBMAIN

    CALL ShowDialog_Form1(0)

END FUNCTION


SUB ShowDialog_Form1(BYVAL hParent&)
    LOCAL Style&, ExStyle&

    Style& = %WS_POPUP OR %DS_MODALFRAME OR %WS_CAPTION OR %WS_MINIMIZEBOX OR %WS_SYSMENU OR %DS_CENTER
    ExStyle& = 0

    DIALOG NEW hParent&, "Database Test App", 0, 0,  300,  212, Style&, ExStyle& TO hForm1&

    CONTROL ADD LABEL, hForm1&,  %FORM1_LBLPASS,  "Password:", 5, 30, 45, 12
    CONTROL ADD LABEL, hForm1&,  %FORM1_LBLUID,  "Username:", 5, 17, 45, 12
    CONTROL ADD LABEL, hForm1&,  %FORM1_LBLDSN,  "DSN:", 5, 5, 43, 12
    CONTROL ADD LABEL, hForm1&,  %FORM1_LBLSQL,  "SQL :", 130, 5, 90, 12
    CONTROL ADD TEXTBOX, hForm1&,  %FORM1_TXTDATA,  "", 3, 44, 294, 166, _
        %WS_CHILD OR %WS_VISIBLE OR %ES_MULTILINE OR %ES_WANTRETURN OR %ES_LEFT OR %ES_AUTOVSCROLL OR %WS_VSCROLL OR %WS_HSCROLL OR %WS_TABSTOP, _
        %WS_EX_CLIENTEDGE
    CONTROL ADD TEXTBOX, hForm1&,  %FORM1_TXTDSN,  "", 53, 5, 72, 11, _
        %WS_CHILD OR %WS_VISIBLE OR %ES_AUTOHSCROLL OR %WS_TABSTOP, _
        %WS_EX_CLIENTEDGE
    CONTROL ADD TEXTBOX, hForm1&,  %FORM1_TXTUSERID,  "", 53, 17, 72, 11, _
        %WS_CHILD OR %WS_VISIBLE OR %ES_AUTOHSCROLL OR %WS_TABSTOP, _
        %WS_EX_CLIENTEDGE
    CONTROL ADD TEXTBOX, hForm1&,  %FORM1_TXTPASSWORD,  "", 53, 30, 72, 11, _
        %WS_CHILD OR %WS_VISIBLE OR %ES_AUTOHSCROLL OR %ES_PASSWORD OR %WS_TABSTOP, _
        %WS_EX_CLIENTEDGE
    CONTROL ADD TEXTBOX, hForm1&,  %FORM1_TXTSQL,  "Select * FROM MyTable", 152, 5, 140, 11, _
        %WS_CHILD OR %WS_VISIBLE OR %ES_AUTOHSCROLL  OR %WS_TABSTOP, _
        %WS_EX_CLIENTEDGE

    CONTROL ADD "Button", hForm1&,  %FORM1_CMDGETDATA,  "Get Data", 240, 27, 53, 15, _
        %WS_CHILD OR %WS_VISIBLE OR %BS_PUSHBUTTON OR %WS_TABSTOP CALL CBF_FORM1_CMDGETDATA

    DIALOG SHOW MODAL  hForm1& TO result

END SUB


CALLBACK FUNCTION CBF_FORM1_CMDGETDATA

   IF CBCTLMSG=%BN_CLICKED THEN

      LOCAL DSN AS ASCIIZ * 25
      LOCAL UserID AS ASCIIZ * 25
      LOCAL Password AS ASCIIZ * 25
      LOCAL SQL AS ASCIIZ * 500

      CONTROL GET TEXT hForm1&, %FORM1_TXTDSN TO DSN
      CONTROL GET TEXT hForm1&, %FORM1_TXTUSERID TO UserID
      CONTROL GET TEXT hForm1&, %FORM1_TXTPASSWORD TO Password
      CONTROL GET TEXT hForm1&, %FORM1_TXTSQL TO SQL

      SQL = TRIM$(SQL)

      '' ODBC Session
      LOCAL a_hEnv AS DWORD
      LOCAL a_hDBC AS DWORD
      LOCAL hstmt AS DWORD
      LOCAL ret AS LONG
      LOCAL Ret2 AS INTEGER

      '' Data Fields
      DIM fields() AS ASCIIZ * 226
      DIM cbValues() AS LONG
   
      LOCAL ColCount1 AS INTEGER

      LOCAL i AS INTEGER   'generic loop var

      ' Connect via ODBC
      Ret = SQLAllocEnv(a_hEnv) ' Allocates the SQL environment
      Ret = SQLAllocConnect(a_hEnv, a_hDBC) ' Allocates connection
      Ret = SQLConnect(a_hDBC, DSN, %SQL_NTS, UserID, %SQL_NTS, Password, %SQL_NTS)
   
      IF RET <> 0 THEN
         ' check ret for bad userid, or DSN and display error
     '    EXIT FUNCTION
      END IF

      ' Create a statement to select data
      Ret = SQLAllocStmt(a_hDBC, hstmt)
      ret = SQLExecDirect(hstmt, SQL , LEN(SQL))          '%SQL_NTS was origonally instead of LEN("Select * FROM weapons")

      IF ret = %SQL_ERROR OR ret = %SQL_SUCCESS_WITH_INFO THEN
         MSGBOX "Check Your SQL statement!",0,"Error in SQLExecDirect"
         EXIT FUNCTION
      END IF

      ' Bind the parameters for the select query
      Ret2 = SQLNumResultCols(hstmt,ColCount1)       ' (This WORKDS!)

      MSGBOX STR$(colcount1) & " Columns found!"
   
      REDIM fields(1 TO colcount1)
      REDIM cbValues(1 TO colcount1)
   
      FOR i = 1 TO 15
         cbValues(i) = %SQL_NTS
      NEXT

      FOR i = 1 TO ColCount1
         Ret = SQLBindCol(hstmt, i, %SQL_C_CHAR, fields(i), SIZEOF(fields(i)), cbValues(i) )
      NEXT

      IF ret = %SQL_ERROR OR ret = %SQL_SUCCESS_WITH_INFO THEN
         MSGBOX STR$(Ret),0,"Error in SQLBindCol"
         EXIT FUNCTION
      END IF

      ' Read a record and set bookmark to next one
      Ret = SQLFetch(hstmt)

      IF ret = %SQL_ERROR OR ret = %SQL_SUCCESS_WITH_INFO THEN
         MSGBOX STR$(Ret),0,"Error in SQLFetch"
         EXIT FUNCTION
      END IF

      DIM RecCount  AS LONG
      DIM TextData AS STRING

      WHILE NOT (ret2 = %SQL_NO_DATA )

         FOR i = 1 TO ColCount1
            TextData = TextData & TRIM$(fields(i)) & CHR$(9)
         NEXT

         TextData = TextData & $CRLF
         ret2 = SQLFetch(hstmt)  '(GET NEXT ROW !!!!!!!!!!!!!!!!!)

      WEND

      CONTROL SET TEXT hForm1&, %FORM1_TXTDATA, TextData
      'MSGBOX TextData   , 0, "SQLFetch got data"

      ' Free the ODBC resources
      Ret = SQLFreeStmt(hstmt, %SQL_DROP)
      Ret = SQLDisconnect(a_hDBC) ' Disconnecting and deallocating.
      Ret = SQLFreeConnect(a_hDBC)
      Ret = SQLFreeEnv(a_hEnv)

    END IF
    
END FUNCTION

------------------

Paul Dwyer
Network Engineer
Aussie in Tokyo
(Paul282 at VB-World)

[This message has been edited by Paul Dwyer (edited October 12, 2000).]