ESQL Example
Example
We have created a DataFlex program that executes the sample select statement used in Introduction to SQL. The program contains two procedures: MessageSample and CommandSample, which use the Message and Command interfaces, respectively.
We will not go into detail on the methods and commands used in the sample program. For a detailed description, see Message Interface.
The statement is:
select name, address, city
from customer
where state = 'CA'
order by name;
To avoid confusing the reader, we have chosen to use the showln command to output the query result. The program connects to a local SQL Server database server. If we were to change the program to DB2 on the database MyDatabase, we would need to change the following line:
#REPLACE CS_CONNECT "DSN=MyDatabase "
Additionally, we would need to pass DB2_DRV_ID to the SQLConnect method and command.
//*****************************************************************************
//*** Chapter3.src
//*** Author: Ben Weijers
//*** Data Access Nederland
//*** September 2000
//*** Purpose: Embedded SQL sample program. Uses the message interface and the command interface. Shows the query:
//*** select name, address, city
//*** from customer
//*** where state = 'CA'
//*** order by name;
//*****************************************************************************
Use Windows
Use MSSQLDRV
Use SQL
Global_variable Integer AKey
#REPLACE CS_CONNECT "SERVER=(local);Trusted_Connection=yes;Database=Order"
#REPLACE CS_QUERY "select name, address, city from customer where state = 'CA' order by name;"
//*** Procedure: MessageInterface
//*** Purpose: Execute the query and show its result using the message interface
Procedure MessageInterface
String sName
String sAddress
String sCity
Integer hoSQL
Integer hdbc
Integer hstmt
Integer iFetchResult
//*** The SQL handle manager object
Object oSQLHandler Is A cSQLHandleManager
Move Self To hoSQL
End_Object // oSQLHandler
//*** Connect to the database server
Send SQLSetConnect Of hoSQL MSSQLDRV_ID CS_CONNECT
Get SQLConnect Of hoSQL "" "" To hdbc
If (hdbc <> 0) Begin
//*** Open a statement
Get SQLOpen Of hdbc To hstmt
If (hstmt <> 0) Begin
//*** Execute the statement, we do it only once so use direct execution
Send SQLExecDirect Of hstmt CS_QUERY
//*** Traverse the result set
Repeat
Get SQLFetch Of hstmt To iFetchResult
If (iFetchResult <> 0) Begin
Get SQLColumnValue Of hstmt 1 To sName
Get SQLColumnValue Of hstmt 2 To sAddress
Get SQLColumnValue Of hstmt 3 To sCity
Showln sName ", " sAddress ", " sCity
End
Until (iFetchResult = 0)
Send SQLClose Of hstmt
End
Send SQLDisconnect Of hdbc
End
//*** Destroy the SQL handle manager object
Send Destroy Of hoSQL
End_Procedure
Send MessageInterface
A second sample program shows calling the procedure sp_help in a SQL Server database. The sp_help procedure is a predefined SQL Server stored procedure. It takes one argument, the name of an object, and returns zero if successful, or one if it fails. Depending on the type of the object passed as an argument, it will generate a number of result sets. For more information on the sp_help procedure, see the SQL Server documentation.
//*****************************************************************************
//*** sp_help.src
//*** Author: Ben Weijers
//*** Data Access Nederland
//*** September 2000
//*** Purpose: Call the sp_help procedure for the vendor table in the order database.
//*****************************************************************************
Use Windows
Use MSSQLDRV
Use SQL
Global_variable Integer AKey
#REPLACE CS_CONNECT "SERVER=(local);Trusted_Connection=yes;Database=Order"
Procedure CallSp_Help
String sLabel
String sColumn
Integer hoSQL
Integer hdbc
Integer hstmt
Integer iFetchResult
Integer iNumCols
Integer iCol
Integer iResult
Integer iNextSetResult
//*** The SQL handle manager object
Object oSQLHandler Is A cSQLHandleManager
Move Self To hoSQL
End_Object // oSQLHandler
//*** Connect to the database server
Send SQLSetConnect Of hoSQL MSSQLDRV_ID CS_CONNECT
Get SQLConnect Of hoSQL "" "" To hdbc
If (hdbc <> 0) Begin
//*** Open a statement
Get SQLOpen Of hdbc To hstmt
If (hstmt <> 0) Begin
//*** Setup the procedure
Send SQLSetProcedurename Of hstmt "SP_HELP"
Send SQLSetArgument Of hstmt 1 "VENDOR"
//*** Call the procedure
Send SqlCall Of hstmt
//** Check the return value
Get SQLReturnValue Of hstmt To iResult
If (iResult = 0) Begin
Repeat
Get SQLStmtAttribute Of hstmt SQLSTMTATTRIB_COLUMNCOUNT To iNumCols
If (iNumCols > 0) Begin
For iCol From 1 To iNumCols
Get SQLColAttribute Of hstmt iCol SQLCOLATTRIB_LABEL To sLabel
Show sLabel
If (iCol < iNumCols) Show ", "
Loop
Showln
End
Repeat
Get SQLFetch Of hstmt To iFetchResult
If (iFetchResult <> 0) Begin
For iCol From 1 To iNumCols
Get SQLColumnValue Of hstmt iCol To sColumn
Show sColumn
If (iCol < iNumCols) Show ", "
Loop
Showln
End
Until (iFetchResult = 0)
Get SQLNextResultSet Of hstmt To iNextSetResult
If ((iNextSetResult <> 0) And (iNumCols > 0)) Showln "*******************************************************"
Until (iNextSetResult = 0)
End
Send SQLClose Of hstmt
End
Send SQLDisconnect Of hdbc
End
//*** Destroy the SQL handle manager object
Send Destroy Of hoSQL
End_Procedure
Send CallSp_Help
Inkey AKey