Module sql.pkg

***************************************************************************** *** SQL.pkg *** *** *** *** Author: Ben Weijers *** *** Data Access Wordwide *** *** 14 February 2000 *** *** *** *** Purpose: *** *** Embedded SQL manager. *** *** *** *** Changes: *** *** *** *** March 14, 2001. CLI Connectivity Kits 2.0.0.13 *** *** Allocate memory before call_driver command with spaces instead *** *** of binary zeroes. *** *** *** *** June 18, 2010. CLI Connectivity Kits 5.1.0.76 *** *** Made a change to reserve space for a decimal point when getting *** *** decimal date through embeded SQL. *** *** *** *****************************************************************************

sql.pkg (view source)
• import declarations
  • cli.pkg (view source)
  • Case.mac (view source)
• Integer_embsqlghoSQLHandleMngr
• IntegerSQLResult
• cSQLStatement : Array
  ***************************************************************************** *** Class : cSQLStatement *** *** Purpose: Statement object for embedded sql *** *** *** *** Description: *** *** An object is created for each statement. These will be children *** *** of cSQLConnection objects which manage the create and destruction *** *** of these. *** *****************************************************************************
  • Construct_object(Integer Image)
    • IntegerphCLIHandle
    • StringpsDriverID
    • IntegerphStmtConnection
    • IntegerpiLastColumn
    • IntegerpiLastArgument
    • IntegerpiBindFile
  • StoreHandleInfo(Integer iCLIHandle String sDrvrId Integer iParentHandle)
    *** *** Procedure: StoreHandleInfo *** Purpose : Store basic information about the statement handle. ***
  • FreeHandle()
    *** *** Procedure: FreeHandle *** Purpose : Free a handle. Since a handle is stored in an *** cSQLStatement object, we destroy the object. ***
  • HandleError(Integer ihstmt Integer ihdbc String sDrvrId String sOriginMsg)
    *** *** Procedure: HandleError *** Puprose : Handle an error that has occured while checking properties. ***
    • IntegeriErrHandle
    • IntegeriErrNum
    • StringsLocationInfo
  • StmtError(Integer iErrNum String sErrText String sOriginMsg)
    *** *** Procedure: StmtError *** Purpose : Handle general error not related to the properties ***
    • IntegeriErrHandle
    • StringsLocationInfo
  • SQLIllegalAttribute(String sErrText String sOriginMsg)
    *** *** Procedure: SQLIllegalAttribute *** Purpose : Handle an illegal attribute error ***
    • IntegerbReport
  • IntegerSQLPrivateColumnIsLegal(Integer ihdbc String sDrvrId Integer ihstmt Integer iCol)
    *** *** Function: SQLPrivateColumnIsLegal *** Purpose : Check if a column number is legal ***
    • IntegerbLegal
    • IntegeriNumColumns
  • SQLClose()
    *** *** Procedure: SQLClose *** Purpose : Close a statement and free all alllocated resources ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • StringsDrvrId
    • StringsEmpty
  • SQLPrepare(String sStatement)
    *** *** Procedure: SQLPrepare *** Purpose : Prepare a statement for execution ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • StringsDrvrId
  • SQLExecute()
    *** *** Procedure: SQLExecute *** Purpose : Execute a prepared statement ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • StringsDrvrId
    • StringsEmpty
  • SQLExecDirect(String sStatement)
    *** *** Procedure: SQLExecDirect *** Purpose : Prepare and execute a statement ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • StringsDrvrId
  • IntegerSQLFetch()
    *** *** Procedure: SQLFetch *** Purpose : Fetch the next row *** Returns : 0 = No more data *** <>0 = Success ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriResult
    • StringsDrvrId
    • StringsEmpty
  • StringSQLNextColumn()
    *** *** Function: SQLNextColumn *** Purpose : Get the next column ***
    • IntegeriCol
    • StringsResult
  • StringSQLColumnValue(Integer iCol)
    *** *** Function: SQLColumnValue *** Purpose : Return the value of a column in a fetched row of a given *** statement. ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • StringsResult
  • StringSQLPrivateColumnValue(Integer iCol Integer ihdbc String sDrvrId Integer ihstmt)
    • IntegeriVoid
    • IntegeriColSize
    • IntegeriColPrecision
    • StringsResult
    • StringsEmpty
    • IntegerbLegalColumn
  • SQLBindFile(Integer iFileNum)
    *** *** Procedure: SQLBindFile *** Purpose : Add a file to the files used to place the statement result in. ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • StringsDrvrId
    • StringsFileType
    • IntegerbIsOpen
  • StringSQLGetData(Integer iCol Integer iLen)
    ************************************************************************* *** Function: SQLGetData *** *** Purpose : Get data of a column. *** *************************************************************************
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • IntegeriColSize
    • IntegeriResult
    • StringsDrvrId
    • StringsResult
    • StringsEmpty
    • IntegerbLegalColumn
  • IntegerSQLColumnMap(String sColname)
    *** *** Function: SQLColumnMap *** Purpose : Map a column name to a number, if the name id not found no *** error is generated the returned number will be 0 (zero) in *** that case ***
    • Integerihdbc
    • Integerihstmt
    • IntegeriNumColumns
    • IntegeriColCount
    • IntegeriColNum
    • StringsDrvrId
    • StringsCurColName
  • StringSQLStmtAttribute(Integer iAttribId)
    *** *** Function: SQLStmtAttribute *** Purpose : Get a statement attribute ***
    • Integerihdbc
    • Integerihstmt
    • StringsResult
    • StringsDrvrId
  • StringSQLPrivateStmtAttribute(Integer iAttribId Integer ihdbc String sDrvrId Integer ihstmt)
    • StringsResult
    • IntegeriLen
    • IntegerbLegalAttrib
  • StringSQLColAttribute(Integer iCol Integer iAttribId)
    *** *** Function: SQLColAttribute *** Purpose : Get a column's attribute ***
    • Integerihdbc
    • Integerihstmt
    • StringsResult
    • StringsDrvrId
  • StringSQLPrivateColAttribute(Integer iCol Integer iAttribId Integer ihdbc String sDrvrId Integer ihstmt)
    • StringsResult
    • StringsEmpty
    • IntegerbIsStringAttribute
    • IntegeriLen
    • IntegeriVoid
    • IntegerbLegalAttrib
    • IntegerbLegalColumn
  • SQLSetProcedureName(String sProcName String sSchemaArg)
    *** *** Procedure: SQLSetProcedureName *** Purpsoe : Preapre for calling a stored procedure, pass the procedure name. ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriVoid
    • StringsSchema
    • StringsEmpty
  • SQLSetNextArgument(String sArgument)
    *** *** Function: SQLSetNextArgument *** Purpose : Pass the next argument ***
    • IntegeriArgnum
  • SQLSetArgument(Integer iArgnum String sArgument)
    *** *** Procedure: SQLSetArgument *** Purpose : Set the next argument ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriVoid
    • StringsEmpty
  • SQLCall()
    *** *** Procedure: SQLCall *** Purpose : Call a stored procedure ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriVoid
    • StringsEmpty
  • StringSQLGetNextArgument()
    *** *** Function: SQLGetNextArgument *** Purpose : Get the next argument ***
    • IntegeriArgnum
    • StringsResult
  • StringSQLGetArgument(Integer iArgnum)
    *** *** Function: SQLGetArgument *** Purpose : Get the specified argument from a procedure ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriVoid
    • StringsEmpty
    • StringsArgument
    • IntegeriLen
  • StringSQLReturnValue()
    *** *** Function: SQLReturnValue *** Purpose : Get the reutn value of a stored function ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriVoid
    • StringsEmpty
    • StringsRetval
    • IntegeriLen
  • IntegerSQLNextResultSet()
    *** *** Function: SQLNextResultSet *** Purpose : Switch to the next set *** Returns : 0 = No more result sets *** <>0 = Switched to next set ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriResult
    • StringsEmpty
  • SQLFetchActivatesBuffer(Integer iFileNum Integer bState)
    *** *** Procedure: SQLFetchActivatesBuffer *** Purpose : Setup a file that will be acivated after a succesfull *** fetch on the statement ***
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • IntegeriResult
    • IntegerbIsOpen
    • IntegeriVoid
    • StringsFileType
  • StringSQLGetMessage(Integer iMessageNum)
    • Integerihdbc
    • Integerihstmt
    • StringsDrvrId
    • StringsMessage
    • IntegeriVoid
  • StringRemoveComponentIdentifier(String sDiagMessage)
    *** *** Function: RemoveComponentIdentifier *** Purpose : Remove the component identifier from a diagnostic message ***
  • StringDFDateToSQLDate(Date dDFDate)
    *** *** Function: DFDateToSQLDate *** Purpose : Convert a DataFlex date to a SQL Date. Use dummy zero dates. ***
    • IntegerhoCLIHandler
    • StringsSQLDate
    • oDFSQLCLIHandler : cCLIHandler
      *** Create a CLI handler object
  • StringSQLDateToDFDate(String sSQLDate)
    *** *** Function: SQLDateToDFDate *** Purpose : Convert a SQL date to a DataFlex Date. Use dummy zero dates. *** { Visibility=Private }
    • IntegerhoCLIHandler
    • DatedDFDate
    • oSQLDFCLIHandler : cCLIHandler
      *** Create a CLI handler object
• cSQLConnection : Array
  ***************************************************************************** *** Class : cSQLConnection *** *** Purpose: Connection object for embedded SQL *** *** *** *** Description: *** *** This provides the ability to create a connection for a driver. *** *** It will contain child statement objects *** *****************************************************************************
  • Construct_object(Integer Image)
    • IntegerphCLIHandle
    • StringpsDriverID
    • IntegerpiBindFile
  • StoreHandleInfo(Integer iCLIHandle String sDrvrId Integer iParentHandle)
    *** *** Procedure: StoreHandleInfo *** Purpose : Store basic information about the statement handle. ***
  • FreeHandle()
    *** *** Procedure: FreeHandle *** Purpose : Destroys handle object and all child statement objects ***
  • IntegerSQLConnect(string sDrvrID String sConnect)
    *** *** Function: SQLConnect *** Purpose : Make a connectin to an embedded SQL server *** Returns : The DataFlex handle to the connection ***
    • Integerihdbc
    • StringsEmpty
  • IntegerSQLFileConnect(Integer iFileNum)
    *** *** Function: SQLFileConnect *** Purpose : Use a connection of an already open file ***
    • Integerihdbc
    • IntegerbIsOpen
    • StringsDrvrId
    • StringsEmpty
  • SQLDisconnect()
    *** *** Procedure: SQLDisconnect *** Purpose : Disconnect from an embedded SQL server and free allocated *** resources ***
    • Integerihdbc
    • IntegeriVoid
    • StringsDrvrId
    • StringsEmpty
  • IntegerCreateHandle()
    *** *** Function: CreateHandle *** Purpose : Creates a child statement handle ***
    • IntegerhSQL
    • oSQLStatement : cSQLStatement
  • IntegerSQLOpen()
    *** *** Function: SQLOpen *** Prupose : Open a statement for use ***
    • IntegerhDFHandle
    • Integerihdbc
    • Integerihstmt
    • IntegeriVoid
    • StringsDrvrId
    • StringsEmpty
• cSQLHandleManager : Array
  ***************************************************************************** *** Class : cSQLHandlemanager *** *** Purpose: Manager for embedded SQL handles. *** *** *** *** Description: *** *** We want to be able to use multiple drivers and multiple *** *** connections within a driver and multiple statements within a *** *** connection. To allow this we have palced our own handle logic on *** *** top of the driver handle logic. This way we can handle the *** *** situation where two driver handles may be equal. *** *****************************************************************************
  • Construct_object(Integer Image)
    • StringpsDefaultDriver
    • StringpsDefaultConnection
  • IntegerCreateHandle()
    *** *** Function: CreateHandle *** Purpose : Allocate memory for the desired type of handle ***
    • IntegerhSQL
    • oSQLConnection : cSQLConnection
      *** Create a connection object
  • SQLSetConnect(String sDriver String sConnect)
    *** *** Procedure: SQLSetConnect *** Purpose : Store default connection information ***
  • integerSQLConnect(String sDrvrID String sConnect)
    *** *** Function: SQLConnect *** Purpose : Make a connectin to an embedded SQL server *** Returns : The DataFlex handle to the connection ***
    • IntegerhDFHandle
    • IntegerbOK
  • IntegerSQLFileConnect(Integer iFileNum)
    *** *** Function: SQLFileConnect *** Purpose : Make an existing connection available for use with Embedded *** SQL. The connection is identified by the number opf a file *** that is open for that connection. ***
    • IntegerhDFHandle
    • IntegerbOK
• SQLSetConnect
  ***************************************************************************** *** Command: SQLSetConnect *** *** Purpose: Setup default connection information for embedded SQL. *** *** *** *** Syntax : SQLSetConnect sSDriverId sConnectString *** *****************************************************************************
• SQLConnect
  ***************************************************************************** *** Command: SQLConnect *** *** Purpose: Connect to a server for embedded SQL usage. *** *** *** *** Syntax : SQLConnect To hdbc *** *** SQLConnect [sDriverId sConnectString] To hdbc *** *****************************************************************************
• SQLFileConnect
  ***************************************************************************** *** Command: SQLFileConnect *** *** Purpsoe: Return the handle to a connection of an open file. This *** *** connection is (obviously) already opened. We are just using *** *** the same connection for Embedded SQL. This allows the *** *** programmer not to know connection details. *** *** *** *** Syntax : SQLFileConnect Filex To hdbc *** *****************************************************************************
• SQLDisconnect
  ***************************************************************************** *** Command: SQLDisconnect *** *** Purpose: Connect to a server for embedded SQL usage. *** *** *** *** Syntax : SQLDisConnect hdbc *** *****************************************************************************
• SQLOpen
  ***************************************************************************** *** Command: SQLOpen *** *** Purpose: Allocate the memory to stroe statement information. *** *** *** *** Syntax : SQLOpen hdbc To hstmt *** *****************************************************************************
• SQLClose
  ***************************************************************************** *** Command: SQLClose *** *** Purpose: Close a statement and free all associated memory. *** *** *** *** Syntax : SQLClose hstmt *** *****************************************************************************
• SQLPrepare
  ***************************************************************************** *** Command: SQLPrepare *** *** Purpose: Prepare a statement for execution. *** *** *** *** Syntax : SQLPrepare hstmt sStatement *** *****************************************************************************
• SQLExecute
  ***************************************************************************** *** Command: SQLExecute *** *** Purpose: Execute a prepared statement *** *** *** *** Syntax : SQLExecute hstmt *** *****************************************************************************
• SQLExecDirect
  ***************************************************************************** *** Command: SQLExecDirect *** *** Purpose: Prepare and execute a statement. *** *** *** *** Syntax : SQLExecDirect hstmt sStatement *** *****************************************************************************
• SQL$NextFile
  ***************************************************************************** *** Command: SQLFileFetch *** *** Purpose: Get a row from a statement result set. *** *** *** *** Syntax : SQLFileFetch hstmt [To File1 [File2 ...]] *** *****************************************************************************
• SQLFileFetch
• SQLBindFetchFile
  ***************************************************************************** *** Command: SQLBindFetchFile *** *** Purpose: Binds a file number to a statement so it can be used for *** *** fetching data. Only needed in non fileconnect. Note that *** *** can be assigned to the connection or the statement handle *** *** *** *** Syntax : SQLBindFileForFetch hstmt|hdbc FileName|FileNumber *** *****************************************************************************
• SQL$NextColumn
  ***************************************************************************** *** Command: SQLFetch *** *** Purpose: Get a row from a statement result set. *** *** *** *** Syntax : SQLFetch hstmt [To Var1 [Var2 ...]] *** *****************************************************************************
• SQLFetch
• SQLGetData
  ***************************************************************************** *** Command: SQLGetData *** *** Purpose: Get a column's data. *** *** *** *** Syntax : SQLGetData hstmt iColumnNUmber [Length iLen] To Var *** *****************************************************************************
• SQLColumnMap
  ***************************************************************************** *** Command: SQLColumnMap *** *** Purpose: Determine the column number based on the column name. *** *** *** *** Syntax : SQLColumnMap hstmt sColName To iColNumber *** *****************************************************************************
• SQLColAttribute
  ***************************************************************************** *** Command: SQLColAttribute *** *** Purpose: Get an attribute of a column in a statements result set. *** *** *** *** Syntax : SQLColAttribute hstmt iCol iAttrId To sAttrib *** *****************************************************************************
• SQLStmtAttribute
  ***************************************************************************** *** Command: SQLStmtAttribute *** *** Purpose: Get an atribute of a statement. *** *** *** *** Syntax : SQLStmtAttribute hstmt iAttribId To sAttrib *** *****************************************************************************
• SQL$SetNextArgument
  ***************************************************************************** *** Command: SQLCall *** *** Purpose: Call a stored procedure. *** *** *** *** Syntax : SQLCall hstmt ProcedureName SchemaName [Arg1 ...] [To Var] *** *** You can pass "" for SchemaName. *** *****************************************************************************
• SQL$GetNextArgument
• SQLCall
• SQLGetArguments
  ***************************************************************************** *** Command: SQLGetArguments *** *** Purpsoe: Get the arguments of a stored procedure. This command should *** *** be used when a procedure has multiple result sets. Some *** *** backends (SQL SErver for example) will return these as the *** *** last result set. This emans you first process all other *** *** result sets and then get the arguments and return value. *** *** *** *** Syntax : SQLGetArguments hstmt [Arg1...] [To Var] *** *****************************************************************************
• SQLNextResultSet
  ***************************************************************************** *** Command: SQLNextResultSet *** *** Purpose: Go the the next result set. This command should be used when *** *** a procedure call results in multiple result sets. Processing *** *** such procedures can be done by loping through the result *** *** sets. *** *** *** *** Syntax : SQLNextResultSet hstmt *** *****************************************************************************
• SQLFetchActivatesBuffer
  ***************************************************************************** *** Command: SQLFetchActivatesBuffer *** *** Purpose: Setup fetch behavior for a file. When set to true a *** *** succesfull fetch of the statement resutl set will *** *** automatically set the status of the file's bufer to *** *** DF_FILE_ACTIVE. *** *** *** *** Syntax : SQLFetchActivatesBuffer hstmt FileNUmber|FileName sState *** *****************************************************************************

References (1):

Field References (0):