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
andDELETE
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 theSQLBindCol()
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()
andSQLTransact()
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).
70 POD Errors
The following errors were encountered while parsing the POD:
- Around line 207:
Expected text after =item, not a bullet
- Around line 237:
Expected text after =item, not a bullet
- Around line 249:
Expected text after =item, not a bullet
- Around line 278:
Expected text after =item, not a bullet
- Around line 290:
Expected text after =item, not a bullet
- Around line 302:
Expected text after =item, not a bullet
- Around line 314:
Expected text after =item, not a bullet
- Around line 326:
Expected text after =item, not a bullet
- Around line 338:
Expected text after =item, not a bullet
- Around line 350:
Expected text after =item, not a bullet
- Around line 379:
Expected text after =item, not a bullet
- Around line 391:
Expected text after =item, not a bullet
- Around line 421:
Expected text after =item, not a bullet
- Around line 433:
Expected text after =item, not a bullet
- Around line 445:
Expected text after =item, not a bullet
- Around line 457:
Expected text after =item, not a bullet
- Around line 486:
Expected text after =item, not a bullet
- Around line 498:
Expected text after =item, not a bullet
- Around line 510:
Expected text after =item, not a bullet
- Around line 539:
Expected text after =item, not a bullet
- Around line 551:
Expected text after =item, not a bullet
- Around line 563:
Expected text after =item, not a bullet
- Around line 593:
Expected text after =item, not a bullet
- Around line 623:
Expected text after =item, not a bullet
- Around line 635:
Expected text after =item, not a bullet
- Around line 647:
Expected text after =item, not a bullet
- Around line 676:
Expected text after =item, not a bullet
- Around line 688:
Expected text after =item, not a bullet
- Around line 718:
Expected text after =item, not a bullet
- Around line 730:
Expected text after =item, not a bullet
- Around line 760:
Expected text after =item, not a bullet
- Around line 772:
Expected text after =item, not a bullet
- Around line 784:
Expected text after =item, not a bullet
- Around line 796:
Expected text after =item, not a bullet
- Around line 808:
Expected text after =item, not a bullet
- Around line 820:
Expected text after =item, not a bullet
- Around line 832:
Expected text after =item, not a bullet
- Around line 844:
Expected text after =item, not a bullet
- Around line 856:
Expected text after =item, not a bullet
- Around line 885:
Expected text after =item, not a bullet
- Around line 897:
Expected text after =item, not a bullet
- Around line 909:
Expected text after =item, not a bullet
- Around line 921:
Expected text after =item, not a bullet
- Around line 933:
Expected text after =item, not a bullet
- Around line 945:
Expected text after =item, not a bullet
- Around line 957:
Expected text after =item, not a bullet
- Around line 986:
Expected text after =item, not a bullet
- Around line 998:
Expected text after =item, not a bullet
- Around line 1010:
Expected text after =item, not a bullet
- Around line 1022:
Expected text after =item, not a bullet
- Around line 1034:
Expected text after =item, not a bullet
- Around line 1046:
Expected text after =item, not a bullet
- Around line 1074:
Expected text after =item, not a bullet
- Around line 1108:
Expected text after =item, not a bullet
- Around line 1120:
Expected text after =item, not a bullet
- Around line 1132:
Expected text after =item, not a bullet
- Around line 1144:
Expected text after =item, not a bullet
- Around line 1156:
Expected text after =item, not a bullet
- Around line 1168:
Expected text after =item, not a bullet
- Around line 1180:
Expected text after =item, not a bullet
- Around line 1192:
Expected text after =item, not a bullet
- Around line 1221:
Expected text after =item, not a bullet
- Around line 1233:
Expected text after =item, not a bullet
- Around line 1263:
Expected text after =item, not a bullet
- Around line 1293:
Expected text after =item, not a bullet
- Around line 1305:
Expected text after =item, not a bullet
- Around line 1317:
Expected text after =item, not a bullet
- Around line 1347:
Expected text after =item, not a bullet
- Around line 1377:
Expected text after =item, not a bullet
- Around line 1405:
Expected text after =item, not a bullet