SQL Interface Reference

  Previous topic Next topic  

CachéQuery_New( hConnection ) -> hQuery

The function allocates memory to for subsequent operations and returns a handle to it. Subsequent CachéQuery_*() functions expect <hQuery> as their first parameter. If the function fails a value of 0 is returned otherwise a positive integer.

<hConnection> is the handle to Caché connection obtained through CachéAddConnection() or CachéAddConnectionEx() functions. Defaults to current connection.

 

CachéQuery_Prepare( hQuery, cSqlStatement ) -> lSuccess

The function request the server to prepares for <cSqlStatement>. Returns success or failure of the operation.

<cSqlStatement> is a character string representing a valid SQL statement. Typically and in its simplest form statement could look like this:
'SELECT * FROM MyTable WHERE Salary < ?'
though in practice this could be as complex as per the need in a given situation.

To explain about SQL statements is beyond the scope of this documentation. I suggest you to look for it in some good book on SQL or though MSDN knowlege base. Alternative you can look into Caché Database Server's accompnying documentation.

 

CachéQuery_Close( hQuery ) -> lSuccess

The function closes the query but does not releases the resource from the server. Query can still be executed with different parameters. Until is query is destroyed with CachéQuery_Destroy() and <hQuery> remains in scope, it can be reused. No preparation is required again. Function returns the success or failure of such operation.

 

CachéQuery_Destroy( hQuery ) -> lSuccess

The function effectively free-up the resources occupied at the server. The query cannot be executed again beyond this point. The function returns success or failure of the operation.

 

CachéQuery_GetNumCols( hQuery ) -> nNumCols

The function returns an integer specifying number of columns the query is subject to return. 0 if there are no columns.

 

CachéQuery_GetColSqlTypes( hQuery ) -> aColSqlTypes

The function returns a one-dimensional array of numeric elements representing SQL types of returned columns. Please refer to CachéRDD.ch file for the possible values. All number types are mapped to "N" type for RDD purposes.

 

CachéQuery_GetColTypes( hQuery ) -> aColTypes

The function returns a one-dimensional array of character values representing the CLIPPER types of returned columns. The possible values will be - "N", "C", "D", "L".

 

CachéQuery_GetColNames( hQuery ) -> aColNames

The function returns a one-dimensional array of character values representing the names of the columns which is assigned by the SQL engine. Though the names will be the same which exist on the table structure in the database, but still there will be lot cases where SQL engine may have to rename them. Typically this situation may arise when in the statement fields with same name are retrieved from two different tables. This is hard to determine in advance what their names will be when query is executed. This function is handy to know the column names in advance so that other actions be taken accordingly.

 

CachéQuery_GetNumParam( hQuery ) -> nNumParam

The function returns the number of parameters the SQL engine will be expecting at the time of execution. Based on this information in combination with < aParTypes> below, application can fetch the required values dynamically and interactively from the user.

 

CachéQuery_GetParSqlTypes( hQuery ) -> aParSqlTypes

The function returns a one-dimensional array of numeric elements representing SQL types of parameters which SQL engine will be expecting at the time of execution.

 

CachéQuery_GetParTypes( hQuery ) -> aParTypes

The function returns a one-dimensional array of character values representing the CLIPPER types of parameters which SQL engine will be expecting at the time of execution. The possible values will be - "N", "C", "D", "L". See above for explanation how this information can be used in real-world.

 

CachéQuery_Execute( hQuery, aParam ) -> lSuccess

The function attempts to execute the <hQuery> and posts <aParam> on server stack and returns success or failure of the opeartion.

If the function fails then there could be a possibility, at the first instance, that one/some of the passed <aParam> might of of wrong type or number of parameters are less than the query is expecting. The success and failure of a query greatly depends on accuracy of <aParam>. CachéRDD does not throw an error neither it checks for the accuracy of data types of passed parameters. It is the responsibility of the developer to pass correct number of parameters in correct order with expected types.

 

CachéQuery_Next( hQuery ) -> lSkipped

CachéRDD supports only forwad cursors. The function attempts to skip to next row. If there are any more rows available in the execution results the function returns true otherwise false. Once a row is skipped it is not possible to return to that row in any manner.

do while ( CachéQuery_Next( hQuery ) )
aadd( aResult, CachéQuery_GetAllFields( hQuery ) )
enddo

The above code snippet retrieves all rows in a query.

 

CachéQuery_GetField( hQuery, nColumnIndex ) -> xValue

The function returns <xValue> of ordinal position <nColumnIndex> in the returned columns for the current row in the cursor. Type of <xValue> can be determined by <aColTypes[ nField ]> ( see above ).

 

CachéQuery_GetFieldByName( hQuery, cColumnName ) -> xValue

The function returns <xValue> of <cColumnName> in the returned columns for the current row in the cursor. Be careful to supply <cColumnName> as determined by <aColNames> ( see above ).

 

CachéQuery_GetAllFields( hQuery ) -> aFieldValues

The function returns a one-dimensional array of xValues representing all columns in the query result. Typically this corresponds to a field values of a record in a table.