SQL Tools Sample Programs

These are generic SQL Tools programs that use PowerBASIC-specific syntax.

Versions of these program that have been modified for both PowerBASIC and Visual Basic  are provided with SQL Tools.  The SQL-Dump sample database is also provided.

Please note that these are intended to be simple examples. By no means are they intended to demonstrate all of the operations that SQL Tools can perform!

INSERT EXAMPLE

'==================== SQL-INS.BAS

'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 THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "Done!"
END IF

SQL_Shutdown

UPDATE EXAMPLE

'==================== SQL-UPD.BAS

'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 THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "Done!"
END IF

SQL_Shutdown

SELECT EXAMPLE

'==================== SQL-SEL.BAS

'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 THEN
SQL_MsgBox "Errors detected." + SQL_ErrorQuickAll, MSGBOX_OK
ELSE
'Copy matching record(s) to text file
OPEN "\SQLTOOLS\SAMPLES\SQL-SEL.TXT" FOR OUTPUT AS #1

DO
SQL_Fetch %NEXT_ROW
IF SQL_EOD THEN EXIT DO
IF SQL_ErrorPending THEN EXIT DO
PRINT #1, SQL_ResColText(%ALL_COLs)
LOOP
END IF

'Close the output file
CLOSE #1

SQL_Shutdown

DELETE EXAMPLE

'==================== SQL_DEL.BAS

'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 THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "Done!"
END IF

SQL_Shutdown

CREATE TABLE EXAMPLE

'==================== SQL-ATBL.BAS

'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 THEN
EXIT FOR
END IF

NEXT

END IF

'Check for errors
IF SQL_ErrorPending THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "Done!"
END IF

SQL_Shutdown

DROP TABLE EXAMPLE

'==================== SQL-DTBL.BAS

'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 THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "Done!"
END IF

SQL_Shutdown

TABLE INFO EXAMPLE 1

'==================== SQL-INF1.BAS

SUB TableInfoExample2

'The SQL-INF1 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: ", sResult

sResult = 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 THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "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 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

'==================== SQL-INF2.BAS

SUB TableInfoExample2

'The SQL-INF2 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 lTableCount

ProcessResult "Table Number: ", Str$(lTableNumber)

sResult = SQL_TblInfoStr(lTableNumber, %TABLE_NAME)
ProcessResult "Table Name: ", sResult

sResult = SQL_TblInfoStr(lTableNumber, %TABLE_TYPE)
ProcessResult "Table Type: ", sResult

lResult = 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 THEN
MSGBOX "Errors detected." + SQL_ErrorQuickAll
ELSE
MSGBOX "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 THEN
PRINT #1, ""
PRINT #1, sText + " ERROR"
PRINT #1, SQL_ErrorQuickAll
PRINT #1, ""
ELSE
PRINT #1, sText + sResult
END IF

END FUNCTION