Home > Class Reference > ENSLIB namespace > %Library.SQLGatewayConnection

%Library.SQLGatewayConnection

class %Library.SQLGatewayConnection extends %Library.RegisteredObject

The SQLGatewayConnection class provides low level access to the SQLGateway The user should understand how to make ODBC calls in order to use this class Most of the calls which return %Status also set the ..sqlcode property. The user should check ..sqlcode if return status is not $$$OK. The user can use a %SQLGatewayConnection class as follows:
  #include %occInclude
  	//Create new Gateway connection object
  	set gc=##class(%SQLGatewayConnection).%New()
  	If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
  		  
  	//Make connection to target DSN
  	s pDSN="Cache Samples"
  	s usr="_system"
  	s pwd="SYS"
  	set sc=gc.Connect(pDSN,usr,pwd,0) 
  	If $$$ISERR(sc) quit sc
  	if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")
  		  
  	set sc=gc.AllocateStatement(.hstmt) 
  	if $$$ISERR(sc) quit sc
  		  
  	//Prepare statement for execution
  	set pQuery= "select * from Sample.Person"
  	set sc=gc.Prepare(hstmt,pQuery) 
  	if $$$ISERR(sc) quit sc
  	//Execute statement
  	set sc=gc.Execute(hstmt)
  	if $$$ISERR(sc) quit sc
  	//Get list of columns returned by query
  	set sc=gc.DescribeColumns(hstmt, .columnlist) 
  	if $$$ISERR(sc) quit sc
  	 
  	//display column headers delimited by ":"
  	set numcols=$listlength(columnlist)-1  //get number of columns
  	for colnum=2:1:numcols+1 {
  		    Write $listget($listget(columnlist,colnum),1),":"
  	  		}
  	write !
  	 
  	//Return first 200 rows	  
  	set sc=gc.Fetch(hstmt)
  	if $$$ISERR(sc) quit sc
  	s rownum=1
  	while((gc.sqlcode'=100) && (rownum<=200)) {
  	      	for ii=1:1:numcols {
  		      	s sc=gc.GetData(hstmt, ii, 1, .val)
  		      	w " "_val
  		      	if $$$ISERR(sc) break
  	      	}
  	      	s rownum=rownum+1
  	 		write !
  	 		set sc=gc.Fetch(hstmt)
  			if $$$ISERR(sc) break
  
  	  		}
  	    
  	  //Close cursor and then disconnect
  	set sc=gc.CloseCursor(hstmt)
  	if $$$ISERR(sc) quit sc
  	  
  	set sc=gc.Disconnect()
  	  
  	Quit sc
  

Properties

property ConnectionHandle as %Binary;
Property methods: ConnectionHandleGet(), ConnectionHandleIsValid(), ConnectionHandleSet()
property ConnectionName as %String;
Property methods: ConnectionNameDisplayToLogical(), ConnectionNameGet(), ConnectionNameIsValid(), ConnectionNameLogicalToDisplay(), ConnectionNameLogicalToOdbc(), ConnectionNameNormalize(), ConnectionNameSet()
property DLLHandle as %Binary;
Property methods: DLLHandleGet(), DLLHandleIsValid(), DLLHandleSet()
property DLLName as %String;
Property methods: DLLNameDisplayToLogical(), DLLNameGet(), DLLNameIsValid(), DLLNameLogicalToDisplay(), DLLNameLogicalToOdbc(), DLLNameNormalize(), DLLNameSet()
property DSN as %String;
Property methods: DSNDisplayToLogical(), DSNGet(), DSNIsValid(), DSNLogicalToDisplay(), DSNLogicalToOdbc(), DSNNormalize(), DSNSet()
property GatewayStatus as %Integer [ Calculated ];
Property methods: GatewayStatusDisplayToLogical(), GatewayStatusGet(), GatewayStatusIsValid(), GatewayStatusLogicalToDisplay(), GatewayStatusNormalize()
property LegacyMode as %Integer;
Property methods: LegacyModeDisplayToLogical(), LegacyModeGet(), LegacyModeIsValid(), LegacyModeLogicalToDisplay(), LegacyModeNormalize(), LegacyModeSet()
property Password as %String;
Property methods: PasswordDisplayToLogical(), PasswordGet(), PasswordIsValid(), PasswordLogicalToDisplay(), PasswordLogicalToOdbc(), PasswordNormalize(), PasswordSet()
property User as %String;
Property methods: UserDisplayToLogical(), UserGet(), UserIsValid(), UserLogicalToDisplay(), UserLogicalToOdbc(), UserNormalize(), UserSet()
property sqlcode as %Integer;
Property methods: sqlcodeDisplayToLogical(), sqlcodeGet(), sqlcodeIsValid(), sqlcodeLogicalToDisplay(), sqlcodeNormalize(), sqlcodeSet()

Methods

method AllocateStatement(ByRef hstmt) as %Status
Method AllocateStatement(hstmt) invokes SQLAllocHandle and creates the corresponding structures in the SQL Gateway.
method BindParameter(hstmt, ParameterNumber As %Integer, IOType As %Integer, ValueType As %Integer, ParameterType As %Integer, ColumnSize As %Integer, DecimalDigits As %Integer, StrLen As %Integer)
BindParameter is calling SQLBindParameter with the corresponding values, the buffer length is guaranteed to be not less then StrLen.
method BindParameters(hstmt, ptype As %List, dtype As %List, precision As %List, scale As %List, cd As %List) as %Status
BindParameters is used for binding all the parameters for a given statement handle.
Arguments :
  • hstmt - Statement handle
  • ptype - input/ouput types of the parameters in the $list format
  • dtype - SQL datatypes of parameters in the $list format
  • precision - $list of the correponding precision values - used for reseving buffers for parameter values
  • scale - $list of the correponding scale values
  • cd - $list of column sizes

They are used in the following places of the SQLBindParameter function

SQLRETURN SQLBindParameter(
    SQLHSTMT         StatementHandle,   // hstmt
    SQLUSMALLINT     ParameterNumber,
    SQLSMALLINT      InputOutputType,   // $list element from ptype
    SQLSMALLINT      ValueType,
    SQLSMALLINT      ParameterType,     // $list element from dtype
    SQLUINTEGER      ColumnSize,        // $list element from cd
    SQLSMALLINT      DecimalDigits,     // $list element from scale
    SQLPOINTER       ParameterValuePtr ,
    SQLINTEGER       BufferLength,
    SQLINTEGER *     StrLen_or_IndPtr);
method CloseCursor(hstmt) as %Status
Method CloseCursor(hstmt) invokes SQLCloseCursor .
method ColumnPrivileges(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ColumnName As %String) as %Status