Example Programs
Here's the BASIC source code for a simple program that opens a database, retrieves all of the data from a table, and exports it to a comma-and-quote-delimited text file:
'open a text file for output...
Open "\SQL-DUMP.TXT" For Output As #1'open the database...
SQL_OpenDatabase "MyData.DSN"
'(SQL Tools supports both DSN and DSN-less connections)'execute a SQL statement...
SQL_Statement IMMEDIATE, "SELECT * FROM ADDRESSBOOK"'retrieve the data...
DoSQL_Fetch NEXT_ROW
If SQL_EndOfData Then Exit Do
Print #1, SQL_ResultColumnText(ALL_COLs)Loop
Close #1
SQL_CloseDatabase
That's just ten lines of BASIC code, and it's a fully functioning SQL Tools program!But don't be fooled by the simplicity of that code. Take another look at the SQL Tools Pro feature list and you'll see that it provides everything you'll need to build powerful, flexible, multi-user database programs. Whether you're a novice or a seasoned SQL guru, SQL Tools has what you need!
More Example Code
INSERT Example
'------------------ INSERT EXAMPLE
'The SQL-INS sample program inserts one
'record into the SQL-Dump database.Dim sSQLStatement As String
Dim sFullName As String
Dim sStreetAddress As String
Dim sCity As String
Dim sState As String
Dim sZipCode As String
'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Assign new record's values to the variables
sFullName = "James Jones"
sStreetAddress = "444 Fourth Avenue"
sCity = "Pittsburg"
sState = "PA"
sZipCode = "16900"'Build the SQL Statement
sSQLStatement = "INSERT INTO ADDRESSBOOK " + _
"(FullName, StreetAddress, City, State, ZipCode) " + _
"VALUES " + _
"('" + sFullName + "','" + sStreetAddress + "','" + _
sCity + "','" + sState + "','" + sZipCode + "')"'Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done!"
End If
SQL_Shutdown
'------------------ UPDATE EXAMPLE
'The SQL-UPD sample program updates one record in the SQL-Dump
'database, changing an address to add "Apt. A".Dim sSQLStatement As String
Dim sStreetAddress As String
'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Specify the record to be changed
sStreetAddress = "444 Fourth Avenue"'Build the SQL Statement
sSQLStatement = "UPDATE AddressBook " + _
"SET StreetAddress = '" + sStreetAddress + " Apt. A" + "'" + _
"WHERE StreetAddress = '" + sStreetAddress + "'"'Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done!"
End If
SQL_Shutdown
'------------------ SELECT EXAMPLE
'The SQL-SEL sample program searches for records in the SQL-Dump
'database ADDRESSBOOK table which have names that end in 'Jones'.Dim sSQLStatement As String
Dim sNameSearch As String'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Specify the search criteria: "ends with Jones"
sNameSearch = "%Jones"'Build the SQL Statement
sSQLStatement = "SELECT FullName, StreetAddress " + _
"FROM AddressBook " + _
"WHERE FullName Like '" + sNameSearch + "'"
'Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement'Check for errors
If SQL_ErrorPending = True ThenSQL_MsgBox SQL_ErrorQuickAll, MSGBOX_OK
Else
'Copy matching record(s) to text file
Open "\SQLTOOLS\SAMPLES\SQL-SEL.TXT" For Output As #1Do
SQL_Fetch NEXT_ROW
If SQL_EOD Then Exit Do
If SQL_ErrorPending = True Then Exit Do
Print #1, SQL_ResColText(ALL_COLs)Loop
End If
'Close the output file
Close #1SQL_Shutdown
'------------------ DELETE EXAMPLE
'The SQL-DEL sample program deletes one
'record from the SQL-Dump database.Dim sSQLStatement As String
Dim sFullName As String
'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Specify the record to be deleted
sFullName = "James Jones"'Build the SQL Statement
sSQLStatement = "DELETE FROM AddressBook " + _
"WHERE FullName = '" + sFullName + "'"'The statement now looks like this:
'DELETE FROM AddressBook WHERE FullName = 'James Jones''Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done!"
End If
SQL_Shutdown
'------------------ CREATE-TABLE EXAMPLE
'The SQL-ATBL sample program Adds one
'table to the SQL-Dump database.Dim sSQLStatement As String
Dim StateName(1) As String
Dim Abbreviation(1) As String
Dim lCounter As Long
'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Build the SQL Statement
sSQLStatement = "CREATE TABLE StateNames " + _
"(StateName VARCHAR ,Abbreviation VARCHAR)"'Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement'Check for errors
If SQL_ErrorPending = False Then'The new table was created.
'Assign new record values to variables.
StateName(0) = "Hawaii"
Abbreviation(0) = "HI"
StateName(1) = "Alaska"
Abbreviation(1) = "AK"
'(Add new elements to these arrays if you want to complete the table.)
For lCounter = 0 To UBound(StateName())'Build the SQL Statement
sSQLStatement = "INSERT INTO StateNames (StateName, Abbreviation) " + _
"VALUES ('" + StateName(lCounter) + "','" + _
Abbreviation(lCounter) + "')"
'Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement
'Check for errors
If SQL_ErrorPending = True Then Exit ForNext
End If
'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done!"
End If
SQL_Shutdown
'------------------ DROP-TABLE EXAMPLE
'The SQL-DTBL sample program drops one table
'from the SQL-Dump database.Dim sSQLStatement As String
'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Build the SQL Statement
sSQLStatement = "DROP TABLE StateNames"'Submit the SQL Statement to the database
SQL_Stmt SQL_STMT_IMMEDIATE, sSQLStatement
'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done!"
End If
SQL_Shutdown
'------------------ TABLE-INFO EXAMPLE #1
Sub TableInfoExample1
'This sample program retrieves database information from the
'SQL-Dump database. It returns one table's basic information.Dim sSQLStatement As String
Dim sColumnName As String
Dim sResult As String
Dim lTableNumber As Long
Dim lColumnNumber As Long
Dim lResult As Long'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Find the ADDRESSBOOK table...
lTableNumber = SQL_TblNumber("ADDRESSBOOK", "")
ProcessResult "Table Number: ", Str$(lTableNumber)'Get various information...
sResult = SQL_TblInfoStr(lTableNumber, TABLE_NAME)
ProcessResult "Table Name: ", sResultsResult = SQL_TblInfoStr(lTableNumber, TABLE_TYPE)
ProcessResult "Table Type: ", sResult
Print #1, ""lResult = SQL_TblColCount(lTableNumber)
ProcessResult "Column Count: ", Str$(lResult)
Print #1, ""'Get information about table's columns...
For lColumnNumber = 1 To SQL_TblColCount(lTableNumber)
sColumnName = SQL_TblColInfoStr(lTableNumber, _
lColumnNumber, _
TBLCOL_COLUMN_NAME)ProcessResult "Column Name: ", sColumnName
lResult = SQL_TblColNumber(lTableNumber,sColumnName )
ProcessResult "Column Number:", Str$(lResult)sResult = SQL_TblColInfoStr(lTableNumber, _
lColumnNumber, _
TBLCOL_TYPE_NAME)ProcessResult "Column Type: ", sResult
Print #1, ""Next
Print #1, ""
'Close the database
SQL_CloseDB'Close output file
Close #1'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done! See results in SQL-INF1.TXT."
End If
SQL_Shutdown
END SUB
Function ProcessResult(sText As String, sResult As String) As String
If SQL_ErrorPending = True Then
Print #1, ""
Print #1, sText + " ERROR"
Print #1, SQL_ErrorQuickAll
Print #1, ""Else
Print #1, sText + sResult
End If
End Function
'------------------ TABLE-INFO EXAMPLE #2
Sub TableInfoExample2
'This sample program retrieves database information from the
'SQL-Dump database. It returns information about all tables.Dim sSQLStatement As String
Dim sResult As String
Dim lTableCount As Long
Dim lTableNumber As Long
Dim lColumnNumber As Long
Dim lResult As Long'Initialize SQL Tools
SQL_Authorize &H........
SQL_Init'Open the database
SQL_OpenDB "\SQLTOOLS\SAMPLES\SQL-DUMP.DSN"'Open SQL-INFO.TXT for information output
Open "\SQLTOOLS\SAMPLES\SQL-INF2.TXT" For Output As #1'Count Tables in the database
lTableCount = SQL_TblCount
ProcessResult "Table Count: ", Str$(lTableCount)
Print #1, ""'Get Table Name, Table Type, and other information
For lTableNumber = 1 To lTableCountProcessResult "Table Number: ", Str$(lTableNumber)
sResult = SQL_TblInfoStr(lTableNumber, TABLE_NAME)
ProcessResult "Table Name: ", sResultsResult = SQL_TblInfoStr(lTableNumber, TABLE_TYPE)
ProcessResult "Table Type: ", sResultlResult = SQL_TblColCount(lTableNumber)
ProcessResult "Column Count: ", Str$(lResult)Print #1,
Next
Print #1, ""
'Close the database
SQL_CloseDB'Close output file
Close #1'Check for errors
If SQL_ErrorPending = True ThenLabel1.Caption = "Errors detected."
MsgBox SQL_ErrorQuickAllElse
Label1.Caption = "Done! See results in SQL-INF2.TXT."
End If
SQL_Shutdown
End Sub
Function ProcessResult(sText As String, sResult As String) As String
If SQL_ErrorPending = True Then
Print #1, ""
Print #1, sText + " ERROR"
Print #1, SQL_ErrorQuickAll
Print #1,""Else
Print #1, sText + sResult
End If
End Function
