J. Michael Mahan

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).

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