Please help me make it beyond 'hacker' status. An ODBC problem.

[Herb Bowers]

Hello All, I guess I'm the new kid on the block.

I have been trying to access MS SQL Server databases through ODBC without much luck. I am running SQL Express. The following are the steps I used to get to where I am now.

1. I ran 'EX_ODBC_07' written by José. The partial results are on page 1 of the attached PDF.

2. I ran 'EX_ODBC_12' written by José and added the SQL Server to the list. (I think that was what I was suppose to do.) The data I supplied is on pages 2-8 of the same PDF. When tested, I got a connect.

3. Then I ran 'EX_ODBC_06' written by guess who? You do relly nice work José. It worked just fine.

4. Then I changed the connection string in 'EX_ODBC_06' from the ACCESS data base to M$ SQL Server so I could open good old 'NorthWnd'. It did not work and the screen displayed the following message:

           SqlState: IM008
           Native ERROR: 0
           [Microsoft][ODBC SQL Server Driver]Dialog failedSqlState: IM008
           Native ERROR: 0
           [Microsoft][ODBC SQL Server Driver]Dialog failed

5. I have been programming professionally(?) for 40 years; 5 years in 'assembly' and the rest in 'Business Basic'. Unfortunately the dialects were Early 'DG', 'Point 4', Qantel' 'Basic 4', 'Thoroughbred' and 'BBx/j'. All straight procedural languages; no mouse, no SQL, no nothing. This lanquage is really cool. I'm catching on slowly but slowly, but, this ODBC stuff is driving me wacko. I am trying to get both MSSQL and MySQL going. Please, if someone could help I would be eternally gratefull. (And if you come on down to Tampa I'll buy ya a beer or two.

Oh, one more thing. José, I cannot believe the amount of material you (and others ) are providing this community. The tutorials posted here and elsewhere have made learning fun. Plus, you are inspiring me to take some really big systems I have developed and turn them into possibly really great systems.

Thank you much,
Herb   

This is the code for 'EX_ODBC_06'. The problem area is between the <><><> separators.

Code Select Expand


'****************************************************************************************
' Example of use of the ODBC raw API functions
' SED_PBCC  -  Use the PBCC compiler
' ****************************************************************************************

#COMPILE EXE
#INCLUDE "ODBCAPI352.INC"

' ========================================================================================
' Shows the DATA
' ========================================================================================

SUB ShowTablesData (BYVAL hDbc AS DWORD, BYREF szInTableTypes AS ASCIIZ)

   LOCAL hStmt AS DWORD
   LOCAL cbbytes AS LONG
   LOCAL r AS INTEGER

   LOCAL szTableCatalogName AS ASCIIZ * 256
   LOCAL szTableSchemaName AS ASCIIZ * 256
   LOCAL szTableName AS ASCIIZ * 129
   LOCAL szTableType AS ASCIIZ * 129
   LOCAL szRemarks AS ASCIIZ * 256

   IF ISFALSE hDbc THEN EXIT SUB
   r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
   IF ISFALSE hStmt THEN EXIT SUB

   IF LEN(szInTableTypes) THEN
      r = SQLTables (hStmt, _
                     BYVAL %NULL, 0, _          ' ALL catalogues
                     BYVAL %NULL, 0, _          ' ALL schemas
                     BYVAL %NULL, 0,_           ' ALL tables
                     szInTableTypes, %SQL_NTS)  ' Table types
   ELSE
      r = SQLTables (hStmt, _
                     BYVAL %NULL, 0, _          ' ALL catalogues
                     BYVAL %NULL, 0, _          ' ALL schemas
                     BYVAL %NULL, 0,_           ' ALL tables
                     BYVAL %NULL, 0)            ' ALL table types
   END IF

   IF SQL_SUCCEEDED(r) THEN
      r = SQLBindCol (hStmt, 1, %SQL_C_CHAR, szTableCatalogName, SIZEOF(szTableCatalogName), cbbytes)
      r = SQLBindCol (hStmt, 2, %SQL_C_CHAR, szTableSchemaName, SIZEOF(szTableSchemaName), cbbytes)
      r = SQLBindCol (hStmt, 3, %SQL_C_CHAR, szTableName, SIZEOF(szTableName), cbbytes)
      r = SQLBindCol (hStmt, 4, %SQL_C_CHAR, szTableType, SIZEOF(szTableType), cbbytes)
      r = SQLBindCol (hStmt, 5, %SQL_C_CHAR, szRemarks, SIZEOF(szRemarks), cbbytes)
      DO
         r = SQLFetch(hStmt)   ' Fetch the DATA
         IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
         PRINT "----------------------------------"
         PRINT "szTableCatalogName: " szTableCatalogName
         PRINT "szTableSchemaName: " szTableSchemaName
         PRINT "szTableName: " szTableName
         PRINT "szTableType: " szTableType
         PRINT "szRemarks: " szRemarks
         PRINT "----------------------------------"
         WAITKEY$
         CLS
      LOOP
   END IF

   SQLFreeStmt hStmt, %SQL_CLOSE           ' Closes the cursor
   SQLFreeHandle %SQL_HANDLE_STMT, hStmt   ' Frees the statement HANDLE

END SUB

' ========================================================================================

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL r AS INTEGER
   LOCAL hEnv AS DWORD
   LOCAL hDbc AS DWORD
   LOCAL hStmt AS DWORD
   LOCAL szInConnectionString AS ASCIIZ * 1025
   LOCAL szOutConnectionString AS ASCIIZ * 1025

   ' ALLocates the environment HANDLE
   r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
   IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION

   ' Tells TO the driver manager that is an application that uses the ODBC driver 3.x
   r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
   IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate

   ' ALLocates the connection HANDLE
   r =  SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
   IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate


'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

   ' Connection STRING (Original - Working)

   ' szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
   '                        "DBQ=biblio.mdb;UID=;PWD=;"

'------------------------------------------------------------------------------

   ' Connection STRING (NOT So Original - Non-Working)

   szInConnectionString = "DRIVER=SQL Server;" & _
                          "DBQ=C:\northwnd.mdf;" & _
                          "UsageCount=2;" & _
                          "SQLLevel=1;" & _
                          "FileUsage=0;" & _
                          "DriverODBCVer=03.50;" & _
                          "ConnectFunctions=YYY;" & _
                          "APILevel=2;CPTimeout=60"

'------------------------------------------------------------------------------
    ' This is what is output TO screen:
   
           'SqlState: IM008
           'Native ERROR: 0
           '[Microsoft][ODBC SQL Server Driver]Dialog failedSqlState: IM008
           'Native ERROR: 0
           '[Microsoft][ODBC SQL Server Driver]Dialog failed

'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>


   ' Connects with the ODBC driver
   r = SQLDriverConnect (hDbc, _
                        %HWND_DESKTOP, _
                        szInConnectionString, _
                        LEN(szInConnectionString), _
                        szOutConnectionString, _
                        SIZEOF (szOutConnectionString), _
                        BYVAL %NULL, _
                        %SQL_DRIVER_COMPLETE)

   ' Check FOR errors
   IF ISFALSE SQL_SUCCEEDED(r) THEN
      STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
      GOTO Terminate
   END IF

   ShowTablesData (hDbc, "TABLE")
Terminate:

   ' Closes the connection
   IF hDbc THEN
      SQLDisconnect(hDbc)
      SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
   END IF
   ' Frees the environment HANDLE
   IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

 
Hi Herb,

Must be a problem of the connection string. Here is a good site to get connection strings for different database managers: http://www.connectionstrings.com/?carrier=sqlserver2005

Judging by the syntax for a trusted connection:

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

and the note

Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

The connection string that you need must be something like:

szInConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=northwnd;Trusted_Connection=yes;"

Hope this helps.

[Herb Bowers]

You are "THE MAN". The winning answer is:

zInConnectionString = "Driver={SQL Native Client};Server=SASI\SQLEXPRESS;Database=northwind;Trusted_Connection=yes"

I never realized the server was 'SQL Native Client' and not SRL Server.

Dumy me...when I installed the script to build the database I spelled 'northwnd' as 'notthwind'. Thank you so much, and that is a great spot for connection strings. A very valuable tip.

Thanks again,
Herb 

[Frederick J. Harris]

Hi Herb!

     Glad to see somebody besides me uses ODBC.  Its my preferred DB access technique.  I'll post some code you might try.  I'd like to know if it works for you.  I was intending to make a tutorial out of this but never really finished it to my satisfaction.  What the program does is create an SQL Server database in whatever directory you run the program from.  Then it creates a simple table with a few fields, writes a couple records, then dumps the records.  Below is the output on my computer...

Code Select Expand


szComputerName  = NRFORSILW51
cnSql.szCnIn=DRIVER=SQL Server;SERVER=NRFORSILW51\SQLEXPRESS;
Attempting to Connect.  May Take 10 Seconds Or So.  Please Wait......
Database Connection Succeeded!
Processing, Please Wait.....
Database Creation Succeeded!
sql.szCnIn=DRIVER=SQL Server;SERVER=NRFORSILW51\SQLEXPRESS;DATABASE=TestData;DBQ=C:\PwrBasic\PBCC40\MyProjects\MkSqlDB\TestData.mdf;
Attempting to Connect.  May Take 10 Seconds Or So.  Please Wait......
Table1 Successfully Created!
szQuery=INSERT INTO table1 (Id,Float_Point,Date_Field,Text_Field) VALUES(?,?,?,?);

                                                       SQLExecute(hStmt)
iId      Double           Date           String         0=SQL_SUCCESS
========================================================================
1        3.14159       11/15/1952     My Birthday              0
2        1.23456       6/30/1969      Walk On Moon?            0
3        15.1234       1/1/2006       Some String              0
4        .54321        4/1/2006       April Fools Day!         0
Data Insertion Successful!

Now Attempt To Dump Data.

szQuery=SELECT Table1.Id,Table1.Float_Point,Table1.Date_Field,Table1.Text_Field
FROM Table1;

iId      Double           Date           String
=====================================================
1        3.14159       11/15/1952     My Birthday
2        1.23456       6/30/1969      Walk On Moon?
3        15.1234       1/1/2006       Some String
4        .54321        4/1/2006       April Fools Day!

Data Dump Apparently Worked?
It Worked!


What I don't believe I have worked out too well is getting it to work for users not logged in as administrator, i.e., using SQL Server authentication as opposed to Windows authentication.  Anyway, below is the program.  Don't feel too overwhelmed.  It is kind of involved.  If you run it I'd appreciate if you would let me know if it worked.
(Oh, bye the way, its a console compiler program.  If you don't have that you'll have to output to a text file)

Code Select Expand


#Compile  Exe
#Dim All
#Register None
#include  "Sqltypes.inc"
#include  "Sql32.inc"
#include  "Sqlext32.inc"
#Include  "Win32Api.inc"


Type tagODBC                        'This type helps by keeping related
  szCnIn          As Asciiz*256     'ODBC handles and connection attributes
  szCnOut         As Asciiz*256     'together.  You only have to pass one
  iBytes          As Integer        'address to called procedures rather
  hEnvr           As Dword          'than several
  hConn           As Dword
  blnConnected    As Long
End Type


Type DiagRec                        'The function for retrieving error
  iNativeErrPtr   As Long           'info is somewhat taxing and this
  iTextLenPtr     As Integer        'type groups the variables needed
  szErrMsg        As Asciiz*256     'by SQLGetDiagRec() together so
  szSqlState      As Asciiz*6       'you only need to declare one var.
End Type


Type ConnectionString
  szDriver        As Asciiz*32      'ODBC can be used to connect to
  szServer        As Asciiz*32      'disparate data sources, i.e.,
  szDatabase      As Asciiz*48      'databases proper, Excel files, etc.
  szDBQ           As Asciiz*64      'This type helps by grouping together
  szDefaultDir    As Asciiz*32      'various connection attributes used
  szDriverId      As Asciiz*4       'by various data sources.  Only a few
  szFIL           As Asciiz*16      'are used by any particular data source,
  szMaxBufferSize As Asciiz*8       'but the type can be used in connection
  szPageTimeout   As Asciiz*4       'string construction functions such as
  szConnStr       As Asciiz*128     'MakeConnectionString() below.
End Type


Sub ODBCConnect(sql As tagODBC)     'Once you've loaded sql.szCnIn (Connection String In)
  Local dr As DiagRec               'you can pass it to this function through the tagODBC
                                    'type for connection to a data source.
  Call SQLAllocHandle(%SQL_HANDLE_ENV,%SQL_NULL_HANDLE,sql.hEnvr)
  Call SQLSetEnvAttr(sql.hEnvr,%SQL_ATTR_ODBC_VERSION,ByVal %SQL_OV_ODBC3,%SQL_IS_INTEGER)
  Call SQLAllocHandle(%SQL_HANDLE_DBC,sql.hEnvr,sql.hConn)
  Print "Attempting to Connect.  May Take 10 Seconds Or So.  Please Wait......"
  If SQLDriverConnect _             'SQLDriverConnect()!  Its big, bad, mean and ugly!
     ( _
       sql.hConn, _        'Input Parameter  - Connection Handle
       0, _                'Input Parameter  - Parent Window Handle (optional)
       sql.szCnIn, _       'Input Parameter  - Your Connection String
       Len(sql.szCnIn), _  'Input Parameter  - String Length of Connection String
       sql.szCnOut, _      'Output Parameter - The Connection String As Modified By ODBC
       256, _              'Input Parameter  - Length of szCnOut Buffer
       sql.iBytes, _       'Output Parameter - Number of Bytes ODBC Used in szCnOut
       %SQL_DRIVER_NOPROMPT _   'Real Men Don't Need ODBC Prompts!
     ) _
     =%SQL_ERROR Then
     Print "%SQL_ERROR"
     Call SQLGetDiagRec _
     ( _
       %SQL_HANDLE_DBC, _  'Type of handle for which diagnostics are required
       Sql.hConn, _        'If SQLDriverConnect() fails, the connection handle goes here.
       1, _                'Usually, only 1 record is returned, but there can be more.
       dr.szSqlState, _    'Five character ODBC error code
       dr.iNativeErrPtr, _ 'Driver specific error code
       dr.szErrMsg, _      'Textural error message
       512, _              'Size of buffer allocated to receive textural error message
       dr.iTextLenPtr _    'Length of textural error message actually placed in buffer.
     )
     Print dr.szSqlState
     Print dr.szErrMsg
     Print "SQLDriverConnect() Returned %SQL_ERROR!":Print
     Call SQLFreeHandle(%SQL_HANDLE_DBC,sql.hConn)  'Free Connection Handle
     Call SQLFreeHandle(%SQL_HANDLE_ENV,sql.hEnvr)  'Free Environment Handle
     sql.blnConnected=%FALSE
  Else
     sql.blnConnected=%TRUE
  End If
End Sub


Sub ODBCDisconnect(sql As tagODBC)
  Call SQLDisconnect(sql.hConn)                  'Disconnect From Data Source
  Call SQLFreeHandle(%SQL_HANDLE_DBC,sql.hConn)  'Free Connection Handle
  Call SQLFreeHandle(%SQL_HANDLE_ENV,sql.hEnvr)  'Free Environment Handle
End Sub


Sub MakeConnectionString(cn As ConnectionString)
  Select Case cn.szDriver                               'To connect to SQL Server without
    Case "SQL Server"                                   'connecting to any specific DATABASE,
      If cn.szDBQ="" Then                               'all you need specify is the DRIVER
         cn.szConnStr= _                                'name and the SERVER name.  This may
         "DRIVER="     & cn.szDriver        & ";" & _   'be useful, for example, to CREATE a
         "SERVER="     & cn.szServer        & ";"       'DATABASE.
      Else
         cn.szConnStr= _                                'If, when you connect, you have a
         "DRIVER="     & cn.szDriver        & ";" & _   'specific DATABASE you want to connect,
         "SERVER="     & cn.szServer        & ";" & _   'to, then you also must specify the
         "DATABASE="   & cn.szDatabase      & ";" & _   'DATABASE (for example 'TestData'), and
         "DBQ="        & cn.szDBQ           & ";"       'the DBQ (database qualifier - essentially
      End If                                            'a path to the main DB file).
    Case "Microsoft Access Driver (*.mdb)"
      cn.szConnStr= _                                   'With Microsoft Jet (Access) databases,
      "DRIVER="        & cn.szDriver        & ";" & _   'the DRIVER and path to the file surfices
      "DBQ="           & cn.szDBQ           & ";"       'to make a connection.
    Case "Microsoft Excel Driver (*.xls)"
      cn.szConnStr= _                                   'Likewise for Excel *.xls files.  The
      "DRIVER="        & cn.szDriver        & ";" & _   'DRIVER and path to the *.xls file will
      "DBQ="           & cn.szDBQ           & ";"       'get you connected.
  End Select
End Sub


Sub NullOutConStr(cn As ConnectionString)
  cn.szDriver=""      : cn.szServer=""
  cn.szDatabase=""    : cn.szDBQ=""
  cn.szDefaultDir=""  : cn.szDriverId=""
  cn.szFIL=""         : cn.szMaxBufferSize=""
  cn.szPageTimeout="" : cn.szConnStr=""
End Sub


Function blnCreateDataBase(sql As tagODBC,strDBName As String) As Long
  Local szBuffer As Asciiz*512
  Local strBuffer As String
  Local dwBuffer As Dword
  Local hStmt As Dword
  Local dr As DiagRec

  dwBuffer=512
  Call GetCurrentDirectory(dwBuffer,szBuffer)    'For this demo I'm creating an SQL Server
  strBuffer= _                                   'database in whatever directory you decide
  "CREATE DATABASE " & strDBName & " " & _       'to run this program from.  SQL Server
  "ON " & _                                      'databases in the latest versions of SQL
  "(" & _                                        'Server show up as part of the regular
    "NAME=" & Chr$(39) & strDBName & Chr$(39) & "," & _
    "FILENAME=" & Chr$(39) & szBuffer & "\" & strDBName & ".mdf" & Chr$(39) & "," & _
    "SIZE=10," & _
    "MAXSIZE=50," & _                            'file system and are comprised of a *.mdf
    "FILEGROWTH=5" & _                           'file (the main database file) and a *.ldf
  ") LOG ON " & _                                'file (the database log).  These files need
  "(" & _                                        'not be under the SQL Server installation
    "NAME=" & Chr$(39) & strDBName & "Log" & Chr$(39) & "," & _
    "FILENAME=" & Chr$(39) & szBuffer & "\" & strDBName & ".ldf" & Chr$(39) & "," & _
    "SIZE=5," & _
    "MAXSIZE=25," & _                            'directory, but can be anywhere you wish
    "FILEGROWTH=5MB" & _                         'to place them.
  ");"
  Call SQLAllocHandle(%SQL_HANDLE_STMT,sql.hConn,hStmt)
  If SQLExecDirect(hStmt,ByVal StrPtr(strBuffer),%SQL_NTS)=%SQL_ERROR Then
     Call SQLGetDiagRec _     'SQL Data Definition Language statements (DDL) are executed
     ( _                      'against the database through the ODBC function SQLExecDirect().
       %SQL_HANDLE_STMT, _    'Notice that if a statement fails execution the first parameter
       hStmt, _               'to SQLGetDiagRec() is SQL_HANDLE_STMT.  The second parameter is                   
       1, _                   'the actual statement handle.  The other parameters are as
       dr.szSqlState, _       'previously described.  Note the archecture of this app.  The
       dr.iNativeErrPtr, _    'database connection logic is completely seperated from logic                   
       dr.szErrMsg, _         'that interacts with the database through SQL.  The necessary                   '
       512, _                 'connection handle can be passed through a parameter to each
       dr.iTextLenPtr _       'of the functions with database work to do, and all each function
     )                        'needs to do is allocate a statement handle, allocate a local
     Print dr.szSqlState      'buffer into which an SQL statement can be placed, and either
     Print dr.szErrMsg        'execute the statement as done here and exit if there are no
     blnCreateDataBase=%FALSE 'resultsets created by the SQL statement, or include logic to
  Else                        'retrieve resultset data.
     blnCreateDataBase=%TRUE
  End If
  Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
End Function


Function blnCreateTable(sql As tagODBC,strTableName As String) As Long
  Local szQuery As Asciiz*256
  Local hStmt As Dword
  Local dr As DiagRec

  szQuery= _
  "CREATE TABLE " & strTableName & " " & _
  "(" & _
    "Id          int            NOT NULL, " & _ 'Note that all interactions with the
    "Float_Point float              NULL, " & _ 'underlying database are through Sql
    "Date_Field  smalldatetime      NULL, " & _ 'statements
    "Text_Field  nvarchar(32)       NULL"   & _
  ");"
  Call SQLAllocHandle(%SQL_HANDLE_STMT,sql.hConn,hStmt)
  If SQLExecDirect(hStmt,szQuery,%SQL_NTS)=%SQL_ERROR Then
     Print "Table Creation Failure!"
     Call SQLGetDiagRec _
     ( _
       %SQL_HANDLE_STMT, _
       hStmt, _
       1, _
       dr.szSqlState, _
       dr.iNativeErrPtr, _
       dr.szErrMsg, _
       512, _
       dr.iTextLenPtr _
     )
     Print dr.szSqlState
     Print dr.szErrMsg
     blnCreateTable=%FALSE
  Else
     Print "Table1 Successfully Created!"
     Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
     blnCreateTable=%TRUE
  End If
End Function

Sub ParseDate(strDate As String,strFormat As String,strDelimiter As String,ts As tagTIMESTAMP_STRUCT)
  Local strDt As String
  Register i As Long

  strDt=strDate
  Select Case As Const$ UCase$(strFormat)    'If your program has dates in formats such as
    Case "MDY"                               '11/15/1952, 11-15-1952, 15.11.1952, 1952,11,15,
      For i=1 To Len(strDt)                  'then these strings need to be parsed to tease
        If Mid$(strDt,i,1)=strDelimiter Then 'out the day, month and year numbers for transfer
           ts.month=Val(Left$(strDt,i-1))    'to an ODBC tagTIMESTAMP_STRUCT.  This structure
           strDt=Right$(strDt,Len(strDt)-i)  'is defined in SqlTypes.inc like so...
           Exit For                          '
        End If                               '         TYPE tagTIMESTAMP_STRUCT
      Next i                                 '           year     AS INTEGER     '2 bytes
      For i=1 To Len(strDt)                  '           month    AS WORD        '2 bytes   
        If Mid$(strDt,i,1)=strDelimiter Then '           day      AS WORD        '2 bytes
           ts.day=Val(Left$(strDt,i-1))      '           hour     AS WORD        '2 bytes
           ts.year=Val(Right$(strDt,4))      '           minute   AS WORD        '2 bytes
           Exit For                          '           second   AS WORD        '2 bytes
        End If                               '           fraction AS DWORD       '4 bytes
      Next i                                 '         END TYPE                 '16 bytes total
    Case "DMY"                               '
      For i=1 To Len(strDt)                  'ParseDate() takes four parameters as follows...
        If Mid$(strDt,i,1)=strDelimiter Then '
           ts.day=Val(Left$(strDt,i-1))      '
           strDt=Right$(strDt,Len(strDt)-i)  'Sub ParseDate
           Exit For                          '(
        End If                               ' strDate      As String,  'e.g., "11/15/1952"
      Next i                                 ' strFormat    As String,  'e.g., "mdy"
      For i=1 To Len(strDt)                  ' strDelimiter As String,  'e.g., "\", "-", "."
        If Mid$(strDt,i,1)=strDelimiter Then ' ts           As tagTIMESTAMP_STRUCT
           ts.month=Val(Left$(strDt,i-1))    ')
           ts.year=Val(Right$(strDt,4))      '
           Exit For                          '
        End If                               'Example - Call ParseDate("11/15/1952","mdy","/",ts)
      Next i
    Case "YMD"
      For i=1 To Len(strDt)
        If Mid$(strDt,i,1)=strDelimiter Then
           ts.year=Val(Left$(strDt,i-1))
           strDt=Right$(strDt,Len(strDt)-i)   
           Exit For
        End If
      Next i
      For i=1 To Len(strDt)
        If Mid$(strDt,i,1)=strDelimiter Then
           ts.month=Val(Left$(strDt,i-1))
           ts.day=Val(Right$(strDt,2))
           Exit For
        End If
      Next i
  End Select
End Sub


Function blnInsert(sql As tagODBC) As Byte
  Local iId As Long,iJnk As Long
  Local iStr As Long,iRet As Long
  Local ts As tagTIMESTAMP_STRUCT
  Register i As Long,j As Long
  Local szQuery As Asciiz*100
  Local szString As Asciiz*32
  Local strDate As String
  Local dblNum As Double
  Local hStmt As Dword
  Local dr As DiagRec

  If SQLAllocHandle(%SQL_HANDLE_STMT,sql.hConn,hStmt)=%SQL_SUCCESS Then 'Allocate statement handle
     szQuery= _
     "INSERT INTO table1 " & _    ''data to and from db
     "(" & _                      ''in sql statements
       "Id," & _                  ''INSERT, UPDATE,
       "Float_Point," & _         ''DELETE, and SELECT
       "Date_Field," & _          ''are the main ones!
       "Text_Field" & _
     ") " & _
     "VALUES(?,?,?,?);"
     Print "szQuery=";szQuery
     Print
     iStr=%SQL_NTS
     If SQLPrepare(hStmt,szQuery,%SQL_NTS)=0 Then  'Prepare parameterized SQL statement
        SQLBindParameter(hStmt,1,%SQL_PARAM_INPUT,%SQL_C_LONG,%SQL_INTEGER,0,0,iId,0,iJnk)
        SQLBindParameter(hStmt,2,%SQL_PARAM_INPUT,%SQL_C_DOUBLE,%SQL_FLOAT,0,0,dblNum,0,iJnk)
        SQLBindParameter(hStmt,3,%SQL_PARAM_INPUT,%SQL_C_TYPE_DATE,%SQL_TYPE_TIMESTAMP,16,0,ts,0,iJnk)
        SQLBindParameter(hStmt,4,%SQL_PARAM_INPUT,%SQL_C_CHAR,%SQL_CHAR,31,0,szString,32,iStr)
        Print "                                                       SQLExecute(hStmt)"
        Print " iId      Double           Date           String         0=SQL_SUCCESS  "
        Print "========================================================================"
        For i=1 To DataCount/4
          j=1
          iId=Val(Read$(4*(i-1)+j)):Incr j      'I would venture to say that the biggest
          dblNum=Val(Read$(4*(i-1)+j)):Incr j   'impediments to using the raw ODBC Api
          strDate=Read$(4*(i-1)+j):Incr j       'as your database technology are the
          Call ParseDate(strDate,"mdy","/",ts)  'SQLBindParameter() and SQLBindCol()
          szString=Read$(4*(i-1)+j)             'functions.  These are complicated, and
          iRet=SQLExecute(hStmt)                'I've decided to 'chicken out', and refer
          If iRet<>%SQL_SUCCESS Then            'you to the documentation.  On the bright
             Call SQLGetDiagRec _               'side, once you've figured out how to get
             ( _                                'some particular variable type such as a
               %SQL_HANDLE_STMT, _              'long or a double into or out of a specific
               hStmt, _                         'RDBMS, then you can re-use the BindParameter/
               1, _                             'BindCol info in all your future database work.
               dr.szSqlState, _                 'In other words, you don't have to figure it
               dr.iNativeErrPtr, _              'out from scratch for every procedure you
               dr.szErrMsg, _                   'write.  To help you, when I set up this demo
               512, _                           'I created a table structure containing four
               dr.iTextLenPtr _                 'of the most common variable types as fields,
             )                                  'i.e., an integer, a float, a character string,
             Print dr.szSqlState                'and a date, so at least you won't have to
             Print dr.szErrMsg                  'figure those out.  When you make an app and 
          Else                                  'you need to use SQLBindParameter(), it is the
             Print iId; _                       'eighth parameter that ODBC uses to fetch the
             tab(10)dblNum; _                   'data out of for transfer to the database
             tab(25)strDate; _                  'through the parameterized SQL statement.  In
             tab(40)szString; _                 'the first SQLBindParameter() above the eighth
             Tab(64)iRet                        'parameter is iID (the ID field), and that
          End If                                'variable is the first Local in this procedure.
        Next i                                  'Further, that variable's value is read out of
        SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)   'the Data statements for transfer to the
        blnInsert=%TRUE                         'database.  In most real apps it will come from
     Else                                       'an input routine in your app or a file.
        Print "SQLPrepare() In blnInsert() Failed!"
        blnInsert=%FALSE
     End If
  Else
     Print "Couldn't Get Statement Handle In blnInsert()!"
     blnInsert=%FALSE
  End If
  'Embedded Data  -- in real world program usually from open file
  Data 1, 3.14159, 11/15/1952, "My Birthday"
  Data 2, 1.23456, 6/30/1969, "Walk On Moon?"
  Data 3, 15.1234, 1/1/2006, "Some String"
  Data 4, 0.54321, 4/1/2006, "April Fools Day!"
End Function

Function blnDumpData(sql As tagODBC) As Long
  Local ts As tagTIMESTAMP_STRUCT
  Local szQuery As Asciiz*100
  Local szString As Asciiz*30
  Local strDate As String
  Local dblNum As Double
  Local iId,iJnk As Long
  Local hStmt As Dword

  'we'll now dump data we just INSERTed
  If SQLAllocHandle(%SQL_HANDLE_STMT,sql.hConn,hStmt)=0 Then 'By now you may have noticed that
     szQuery= _                     'all the procedures that interact with the database through
     "SELECT " & _                  'SQL begin with SQLAllocHandle() to obtain a statement handle.
       "Table1.Id," & _             
       "Table1.Float_Point," & _
       "Table1.Date_Field," & _
       "Table1.Text_Field " & _
     "FROM " & _
       "Table1;"
     Print "Now Attempt To Dump Data.":Print
     Print "szQuery=";szQuery:Print
     Call SQLBindCol(hStmt,1,%SQL_C_ULONG,iId,0,iJnk)        'SQLBindCol() is not as 'bad' as
     Call SQLBindCol(hStmt,2,%SQL_C_DOUBLE,dblNum,0,iJnk)    'SQLBindParameter().  You'll notice
     Call SQLBindCol(hStmt,3,%SQL_C_TYPE_DATE,ts,0,iJnk)     'that the third parameter from the
     Call SQLBindCol(hStmt,4,%SQL_C_CHAR,szString,30,iJnk)   'end is the important one into which
     Print " iId      Double           Date           String"'ODBC places values from the database
     Print "====================================================="  'fields.  These 'buffers' are
     If SQLExecDirect(hStmt,szQuery,%SQL_NTS)=0 Then                'the local variables you
        Do While SQLFetch(hStmt)<>%SQL_NO_DATA                      'define in the procedure to
           strDate=Trim$(Str$(ts.month))+"/"+Trim$(Str$(ts.day))+"/"+Trim$(Str$(ts.year))
           Print iId;tab(10)dblNum;tab(25)strDate;tab(40)szString   'place the database field data.
        Loop
        Call SQLCloseCursor(hStmt)
        Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
        blnDumpData=%TRUE:Print
     Else
        Print "SQLPrepare() In blnInsert() Failed!"
        blnDumpData=%FALSE
     End If
  Else
     Print "Couldn't Get Statement Handle In blnInsert()!"
     blnDumpData=%FALSE
  End If
End Function


Function blnProcessData(cn As ConnectionString,sql As tagODBC) As Long
  Local szBuffer As Asciiz*512
  Local dwBuffer As Dword

  Print "Database Connection Succeeded!"
  Print "Processing, Please Wait....."
  If blnCreateDatabase(sql,"TestData") Then
     Call ODBCDisconnect(sql)                    'If database creation succeeds, we'll close the
     Print "Database Creation Succeeded!"        'database and re-open it to the database we just
     dwBuffer=512                                'created on SQL Server.
     Call GetCurrentDirectory(dwBuffer,szBuffer) 'We'll build the path to our to be created SQL
     cn.szDBQ=szBuffer+"\"+"TestData"+".mdf"     'Server database from whatever directory you
     cn.szDatabase="TestData"                    'place this file into.  MakeConnectionString() 
     Call MakeConnectionString(cn)               'will then take this info to build an ODBC
     sql.szCnIn=cn.szConnStr                     'connection string. 
     Print "sql.szCnIn="sql.szCnIn
     Call ODBCConnect(sql)
     If sql.blnConnected=%TRUE Then
        If blnCreateTable(sql,"Table1") Then
           If blnInsert(sql) Then
              Print "Data Insertion Successful!":Print
              If blnDumpData(sql)=%TRUE Then
                 Print "Data Dump Apparently Worked?"
                 blnProcessData=%TRUE
              Else
                 Print "Couldn't Dump Data!"
                 blnProcessData=%FALSE
              End If
           Else
              Print "Data Insertion Failure!"
              blnProcessData=%FALSE
           End If
        Else
           Print "Table Creation Failed!"
           blnProcessData=%FALSE
        End If
        Call ODBCDisconnect(sql)
     End If
  Else
     Print "Database Creation Failed!"
     blnProcessData=%FALSE
  End If
End Function


Function PBMain() As Long
  Local szComputerName As Asciiz*64
  Local cn As ConnectionString
  Local cnSql As tagODBC
  Local dwSize As Dword

  Call NullOutConStr(cn)   'Not really necessary
  cn.szDriver="SQL Server" 'Driver name
  dwSize=32
  Call GetComputerName(szComputerName,dwSize)'Api Funnction to get computer name
  Print "szComputerName  = "szComputerName
  cn.szServer=szComputerName & "\SQLEXPRESS" 'Sql Express wants the computer name & \SQLEXPRESS
  Call MakeConnectionString(cn)              'Sql Server really only needs the computer name & Server name to
  cnSql.szCnIn=cn.szConnStr                  'connect.  MakeConnectionString() then concatenates the two
  Print "cnSql.szCnIn="cnSql.szCnIn          'connection attributes into a single string.
  Call ODBCConnect(cnSql)                    'Try to connect.  If this fails we'll try 'localhost' in the hope MSDE
  If cnSql.blnConnected Then                 'is installed.  If it succeeds, we'll call blnProcessData() to
     If blnProcessData(cn,cnSql) Then        'create the table (table1), insert some test data, then
        Print "It Worked!"                   'dump the data
     Else
        Print "Something Didn't Work!  Rats!!!"
     End If
  Else  'If we get to here the program couldn't connect to SQL Server Express.  MSDE requires
     cn.szServer="localhost" 'localhost as a server name.  Now we'll try that.
     Call MakeConnectionString(cn)
     cnSql.szCnIn=cn.szConnStr
     Print "cnSql.szCnIn="cnSql.szCnIn
     Call ODBCConnect(cnSql)
     If cnSql.blnConnected Then
        If blnProcessData(cn,cnSql) Then
           Print "It Worked!"
        Else
           Print "Something Didn't Work!  Rats!!!"
        End If
     Else
        Print "Can't Connect Nohow!"
     End If
  End If
  WaitKey$

  PBMain=0
End Function


[Herb Bowers]

Hi Fred,

Thank you so much for the great piece of code. I ran it last night and it worked great. The results follow:

Code Select Expand

szComputerName  = DELL
cnSql.szCnIn=DRIVER=SQL Server;SERVER=DELL\SQLEXPRESS;
Attempting to Connect.  May Take 10 Seconds Or So.  Please Wait......
Database Connection Succeeded!
Processing, Please Wait.....
Database Creation Succeeded!
sql.szCnIn=DRIVER=SQL Server;SERVER=DELL\SQLEXPRESS;DATABASE=TestData;DBQ=C:\Documents and Settings\Herb\Desktop\pbodbc30\TestData.mdf;
Attempting to Connect.  May Take 10 Seconds Or So.  Please Wait......
Table1 Successfully Created!
szQuery=INSERT INTO table1 (Id,Float_Point,Date_Field,Text_Field) VALUES(?,?,?,?);

                                                       SQLExecute(hStmt)
iId      Double           Date           String         0=SQL_SUCCESS
========================================================================
1        3.14159       11/15/1952     My Birthday              0
2        1.23456       6/30/1969      Walk On Moon?            0
3        15.1234       1/1/2006       Some String              0
4        .54321        4/1/2006       April Fools Day!         0
Data Insertion Successful!

Now Attempt To Dump Data.

szQuery=SELECT Table1.Id,Table1.Float_Point,Table1.Date_Field,Table1.Text_Field
FROM Table1;

iId      Double           Date           String
=====================================================
1        3.14159       11/15/1952     My Birthday
2        1.23456       6/30/1969      Walk On Moon?
3        15.1234       1/1/2006       Some String
4        .54321        4/1/2006       April Fools Day!

Data Dump Apparently Worked?
It Worked!


Now for the fun part; I will be trying to fathom the code. With José's jump start and your very comprehensive and well documented example, I look forward to it.

I don't understand why ODBC is not a major topic either here or on the PB BB. It seems to me that SQL, in a non-embedded form, would be almost mandatory for anyone writing major business applications. Especially, since PowerBasic has yet to offer the Linux experience. I looked at some of the ISAM offerings and although the price is right (how can you beat free), I just don't have the comfort level to delve too deeply. I hope you will reconsider your thoughts about a tutorial. Perhaps that would provide the stimulus for others to take the plunge.

Thanks again, Fred. If I make any discoveries perhaps I'll try some newbie posts to see if I might generate some discussions. I'm dying to discuss concurrency as it pertains to record locking and threads. I'm really confused about the best way to implement it in PowerBasic (or any other place). Then, I can follow up with an easier subject: I would really like to know the meaning of life. 

[Frederick J. Harris]

Hi Herb!

      I'm glad that worked for you, and thanks for letting me know.  I tested it on several computers, but you know how that goes!  Actually, I have the old MSDE installed on several of my laptops, and on several others SQL Server Express.  That's why PBMain() goes on like it does.  If the one doesn't work, it tries for the other, and I believe the computer name is different.

      Not too many seem to like ODBC direct, but I expect I'll stick with it.  If I didn't use that ADO is a good alternative.  Over in the PowerBASIC forums Michael Mattias is the ODBC expert, so I expect one way or another you can get any questions answered either here or over there. 

      Actually, if you use wrapper functions around some of the worse ODBC function calls, its not too bad.  Once you have a connection handle, all you really need are statement handles for using SQL statements to interact with the db.  In many cases too I've found the info returned by SQLGetDiagnosticRec() to be helpful.

Fred

added later:

Herb,

      Over at PowerBASIC at ...

http://www.powerbasic.com/support/pbforums/showthread.php?t=24912&highlight=ODBC+Direct

is possibly a simpler version of the above program that just uses Microsoft Access.  What makes the program you just tried a little confusing is my use of a connection string builder routine.  You have already had your first brushes with connection strings in ODBC, and by this point you know how important they are.  One little mistake and you are sucking canal water.  In the SQL Server Express example you tried what I was playing with there that might be a bit confusing unless you understand my 'end game', was an attempt to try to 'wrap' the connection string building business into a boiler plate type routine where you just pass some bits of important information to the connection string builder wrapper such as...

DATABASE=MS Access Database
DBQ="C:\Tallies\DBFiles\SaleData.mdb"

etc.  Then you let the wrapper build the ugly thing.  The idea seemed to work reasonably well in this example (in my opinion), and in my future work perhaps I'll build on it.  Just wanted to pass this along.  I know how hard it is to penetrate this stuff!

[Herb Bowers]

Hi Fred,

I have been reviewing the multiple database version. You mentioned you wanted to make a tutorial out of it? I have news for you; it already is a great tutorial. I have no problem reading and understanding the code. My problem is trying to learn the language syntax and nuances well enough to reproduce it. But, you have resolved that problem also. Last night I went on the PowerBasic forum and happened to find your SDK tutorial. Very impressive. It is exactly what I need. I copied it into a word document; ran a couple of macros; and now have a great offline manual. I was still studying it when the sun came up. Thanks to you, I think I am really starting to catch on.

This is my first experience with forums. I cannot believe the amount time and effort you, José, and others put into providing extremely valuable learning materials to people you don't really know. I guess it must be something like the movie "Pay It Forward". All I know for sure is that I am very gratitude. I hope I eventually come across something I can contribute.

Thank you, thank you, thank you to all.

Herb

[Frederick J. Harris]

That Windows Programming Tutorial is here on Jose's site too Herb at...

http://www.jose.it-berater.org/smfforum/index.php?board=285.0

And actually, I believe there may be a couple more here.  I'm glad the material is helpful!

Fred

[Herb Bowers]

Like I said, I am new to the forums experience. I just did a random walk around this one and now I am really impressed. 

[Frederick J. Harris]

This is my first experience with forums. I cannot believe the amount time and effort you, José, and others put into providing ...

Well, I've been around them for some time, but I still don't have one of those little pictures like the one of the cat you've got!