NAME

iodbc - Perl extension for the iODBC API

SYNOPSIS

This man page is not intended to be a manual on the ODBC API. Please see an ODBC manual for extended documentation on the usage of ODBC commands.

To use iodbc functions:

use iodbc;

DESCRIPTION

Everything in the extension follows the ODBC core API except that all function calls are pass by value. The one exception is SQLBindCol which needs to be passed a reference to a scalar. For More information please seek a ODBC manual and remember that this extension only works with core implementations.

Functions

The following functions are included with this iodbc extension. Parameters marked [i] are for input into the function while parameters marked [r] return data from a function.

• SQLAllocEnv

  Syntax

  SQLAllocEnv($hEnv);

  Parameters

  $hEnv

  • [o] The environment handle. This will be passed to other functions.

• SQLAllocConnect

  Syntax

  SQLAllocConnect($hEnv, $hDbc);

  Parameters

  $hEnv

  • [i] The environment handle as it is returned from SQLAllocEnv().

  $hDbc

  • [o] The connection handle. This will be passed to other functions.

• SQLConnect

  Syntax

  SQLConnect($hDbc, $pvDsn, $ivfDsnSize, $pvUid, $ivfUidSize, $pvPwd, $ivfPwdSize);

  Parameters

  $hDbc

  • [i] The connection handle as it is returned from SQLAllocConnect().

  $pvDsn

  • [i] The data source name. This should be a string.

  $ivfDsnSize

  • [i] The size of $pvDsn. It should be SQL_NTS. This stands for a null terminated string.

  $pvUid

  • [i] The user id. This should also be a string.

  $ivfUidSize

  • [i] The size of $pvUid. It should also be SQL_NTS.

  $pvPwd

  • [i] The password. Once again it should be a string.

  $ivfPwdSize

  • [i] The size of pvPwd. Once again it should be SQL_NTS

• SQLAllocStmt

  Syntax

  SQLAllocStmt($hDbc, $hStmt);

  Parameters

  $hDbc

  • [i] The connection handle as it is returned from SQLAllocConnect().

  $hStmt

  • [o] The statement handle. This will be passed to other functions.

• SQLGetCursorName

  Syntax

  SQLGetCursorName($hStmt, $pvCursor, $ivCursorMax, $ivCursorSize);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $pvCursor

  • [o] The cursor name associated with the $hStmt. This will be returned from the function as a string of maximum length $ivCursorMax.

  $ivCursorMax

  • [i] The maximum size of $ivCursor.

  $ivCursorSize

  • [i] The actual size of the string available for return to $pvCursor.

• SQLSetCursorName

  Syntax

  SQLSetCursorName($hStmt, $pvCursor, $ivfCursorSize);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $pvCursor

  • [i] The Cursor Name to be associated with the $hStmt. This should be a string.

  $ivfCursorSize

  • [i] The size of $pvCursor. This should be SQL_NTS.

• SQLPrepare

  Syntax

  SQLPrepare($hStmt, $pvSql, $ivfSqlSize);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $pvSql

  • [i] The SQL statement to be prepared. This should be a string.

  $ivfSqlSize

  • [i] The size of $Sql. This should be SQL_NTS.

• SQLExecute

  Syntax

  SQLExecute($hStmt);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

• SQLExecDirect

  Syntax

  SQLExecDirect($hStmt, $pvSql, $ivfSqlSize);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $pvSql

  • [i] The SQL statement to be prepared. This should be a string.

  $ivfSqlSize

  • [i] The size of $pvSql. This should be SQL_NTS.

• SQLRowCount

  Syntax

  SQLRowCount($hStmt, $ivNumRows);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $ivNumCols

  • [o] The number of rows affected by the SQL statement just executed in $hStmt. This works for UPDATE, INSERT and DELETE statements.

• SQLNumResultCols

  Syntax

  SQLNumResultCols($hStmt, $ivNumCols);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $ivNumCols

  • [o] The Number of columns returned in a result set of a SQL statement in $hStmt.

• SQLDescribeCol

  Syntax

  SQLDescribeCol($hStmt, $ivCol, $pvColName, $ivColNameMax, $ivColNameSize, $fSqlType, $ivPrecision, $ivScale, $fNullable);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $ivCol

  • [i] The function will return a description of this column.

  $pvColName

  • [o] A string that contains the name of column $ivCol

  $ivColNameMax

  • [i] The maximum size of the column name to return to $pvColName

  $ivColNameSize

  • [o] The size of the column name available to return to $pvColName

  $fSqlType

  • [o] The type of data contained in column $ivCol.

  $ivPrecision

  • [o] The precision of column $ivCol.

  $ivScale

  • [o] The scale of column $ivCol.

  $fNullable

  • [o] Returns whether column $ivCol allows null values.

• SQLColAttributes

  Syntax

  SQLColAttributes($hStmt, $ivCol, $fType, $pvAttrib, $ivAttribMax, $ivAttribSize, $ivAttrib);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $ivCol

  • [i] The function will return a attributes from column $ivCol.

  $fType

  • [i] The type of attribute to return.

  $pvAttrib

  • [o] The attribute. This will be returned from the function as a string.

  $ivAttribMax

  • [i] The maximum size of $pvAttrib

  $ivAttribSize

  • [o] The size of the attribute string available to return to $pvAttrib.

  $ivAttrib

  • [o] The attribute. This will be returned as an integer.

• SQLBindCol

  Syntax

  SQLBindCol($hStmt, $ivCol, $fType, $svValue, $ivValueMax, $ivValueSize);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $ivCol

  • [i] The function will bind this column.

  $fType

  • [i] The data type to bind.

  $svValue

  • [i] A reference to a scalar that will store the data from a result set.

  $ivValueMax

  • [i] The maximum size allowed for the scalar referenced by $svValue

  $ivValueSize

  • [i] Size available to return to the scalar referenced by $svValue before SQLFetch() is called.

• SQLFetch

  Syntax

  SQLFetch($hStmt);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  Notes

  SQLFetch() returns data from each bound column to the scalar that was referenced by the $svValue parameter when the SQLBindCol() function was called.

• SQLError

  Syntax

  SQLError($hfEnv, $hfDbc, $hfStmt, $pvSqlState, $fNativeError, $pvErrorMsg, $ivErrorMsgMax, $ivErrorMsgSize);

  Parameters

  $hfEnv

  • [i] The environment handle as it is returned from SQLAllocEnv() or SQL_NULL_HENV.

  $hfDbc

  • [i] The connection handle as it is returned from SQLAllocConnect() or SQL_NULL_HDBC.

  $hfStmt

  • [i] The statement handle as it is returned from SQLAllocStmt() or SQL_NULL_HSTMT.

  $pvSqlState

  • [o] This returns the SQLSTATE as a string.

  $fNativeError

  • [o] This returns a Native Error Code.

  $pvErrorMsg

  • [o] This returns an Error Message as a string.

  $ivErrorMsgMax

  • [i] The maximum size of $pvErrorMsg

  $ivErrorMsgSize

  • [o] The size of the string available to return to $pvErrorMsg

• SQLFreeStmt

  Syntax

  SQLFreeStmt($hStmt, $fOption);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

  $fOption

  • [i] The action to be taken by the function.

• SQLCancel

  Syntax

  SQLCancel($hStmt);

  Parameters

  $hStmt

  • [i] The statement handle as it is returned from SQLAllocStmt().

• SQLTransact

  Syntax

  SQLTransact($hEnv, $hDbc, $fType);

  Parameters

  $hEnv

  • [i] The environment handle as it is returned from SQLAllocEnv().

  $hDbc

  • [i] The connection handle as it is returned from SQLAllocConnect.

  $fType

  • [i] The type of transaction to take

• SQLDisconnect

  Syntax

  SQLDisconnect($hDbc);

  Parameters

  $hDbc

  • [i] The connection handle as it is returned from SQLAllocConnect().

• SQLFreeConnect

  Syntax

  SQLFreeConnect($hDbc);

  Parameters

  $hDbc

  • [i] The connection handle as it is returned from SQLAllocConnect().

• SQLFreeEnv

  Syntax

  SQLFreeEnv($hEnv);

  Parameters

  $hEnv

  • [i] The connection handle as it is returned from SQLAllocEnv().

EXAMPLES

use iodbc;

#

#To start I allocate handles and connect to my favorite data source. Notice I check every return code.

checkretcode(&SQLAllocEnv($henv));

checkretcode(SQLAllocConnect($henv, $hdbc));

checkretcode(SQLConnect($hdbc, "favorite_datsource", SQL_NTS, "user", SQL_NTS, "password", SQL_NTS));

checkretcode(SQLAllocStmt($hdbc,$hstmt));

#

#Then I execute a simple SQL statement

checkretcode(SQLExecDirect($hstmt, "SELECT * FROM sample_table", SQL_NTS));

#Bind column one to $rgbValue

checkretcode(SQLBindCol($hstmt, 1, SQL_C_DEFAULT, \$rgbValue, 24, SQL_NULL_DATA));

#Fetch all the rows and print them out

while(checkretcode(SQLFetch($hstmt))==SQL_SUCCESS){

print "$rgbValue\n";

}

#

#Finally close up shop by freeing statements and disconnecting.

checkretcode(SQLFreeStmt($hstmt, SQL_DROP));

checkretcode(SQLDisconnect($hdbc));

checkretcode(SQLFreeConnect($hdbc));

checkretcode(SQLFreeEnv($henv));

#

#This subroutine checks the return code to make sure that the function executed correctly

sub checkretcode {

my $retcode = shift;

if (($retcode==SQL_SUCCESS)||($retcode==SQL_NO_DATA_FOUND)) {

return $retcode;

} else {

die "some error";

}

}

TO DO

• Further testing. In particular add SQLCancel() and SQLTransact() to the test scripts.

• Add ODBC 1.0 and then ODBC 2.0 support.

• Put an DBD/DBI face on top of iODBC.

• Improvement of the documentation.

BUGS

Let me know if you find any.

AUTHOR

J. Michael Mahan, mahanm@nextwork.rose-hulman.edu

SEE ALSO

perl(1).

cpanm iodbc

perl -MCPAN -e shell
install iodbc