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.
'****************************************************************************************
' 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
' ========================================================================================
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
Quote
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.
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 ;D ;D ;D
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...
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)
#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
Hi Fred,
Thank you so much for the great piece of code. I ran it last night and it worked great. The results follow:
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. ;)
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!
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
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
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. :o
Quote
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!