Executing statements


Detailed Description

Executing SQL statements or PL/SQL blocks is really simple with OCILIB.

First, call OCI_StatementCreate() to allocate a statement handle. Then :

These two steps can be done together by calling OCI_ExecuteStmt() that parses and executes in one go.

To find out if the statement has affected any rows, call OCI_GetAffectedRows()

Finally, release the statement and its resources with OCI_StatementFree()

Note:
A statement can be parsed once and executed as many times as needed (see Binding variables section)

A OCI_Statement can be used to prepare and/or execute different SQL and PL/SQL statements as many times as needed. For example, if the SQL processing of an application is sequential, only one statement handle is required

OCILIB supports nested levels of SQL statement processing. An application can loop through the resultset of the statement handle A, executing statement B and fetching statement C at every loop, and so on ...

Example
#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
  
    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);
 
    /* prepare and execute in 2 steps */

    OCI_Prepare(st, "delete from test_fetch where code > 10");
    OCI_Execute(st);

    /* prepare/execute in 1 step */

    OCI_ExecuteStmt(st, "delete from test_fetch where code > 1");

    printf("%d row deleted", OCI_GetAffectedRows(st));

    OCI_Commit(cn);

    OCI_Cleanup();
 
    return EXIT_SUCCESS;
}


Functions

OCI_EXPORT OCI_Statement *OCI_API OCI_StatementCreate (OCI_Connection *con)
 Create a statement object and return its handle.
OCI_EXPORT boolean OCI_API OCI_StatementFree (OCI_Statement *stmt)
 Free a statement and all resources associated to it (resultsets, ....).
OCI_EXPORT boolean OCI_API OCI_Prepare (OCI_Statement *stmt, const mtext *sql)
 Prepare a SQL statement or PL/SQL block.
OCI_EXPORT boolean OCI_API OCI_Execute (OCI_Statement *stmt)
 Execute a prepared SQL statement or PL/SQL block.
OCI_EXPORT boolean OCI_API OCI_ExecuteStmt (OCI_Statement *stmt, const mtext *sql)
 Parse and execute a SQL statement or PL/SQL block.
OCI_EXPORT const mtext *OCI_API OCI_GetSql (OCI_Statement *stmt)
 Return the last SQL or PL/SQL statement parsed by the statement.
OCI_EXPORT unsigned int OCI_API OCI_GetSqlErrorPos (OCI_Statement *stmt)
 Return the error position in the SQL statement where the error occurred in case of SQL parsing error.
OCI_EXPORT unsigned int OCI_API OCI_GetAffectedRows (OCI_Statement *stmt)
 Return the number of rows affected by the SQL statement.
OCI_EXPORT unsigned int OCI_API OCI_GetSQLCommand (OCI_Statement *stmt)
 Return the Oracle SQL code the command holded by the statement handle.
OCI_EXPORT const mtext *OCI_API OCI_GetSQLVerb (OCI_Statement *stmt)
 Return the verb of the SQL command holded by the statement handle.


Function Documentation

OCI_EXPORT boolean OCI_API OCI_Execute ( OCI_Statement stmt  ) 

Execute a prepared SQL statement or PL/SQL block.

Parameters:
stmt - Statement handle
Returns:
TRUE on success otherwise FALSE

Definition at line 1476 of file statement.c.

References OCI_Connection::autocom, OCI_Statement::con, OCI_Connection::cxt, OCI_Connection::err, OCI_Statement::err_pos, OCI_Statement::exec_mode, OCI_Statement::nb_iters, OCI_Commit(), OCI_ReleaseResultsets(), OCI_Statement::status, OCI_Statement::stmt, and OCI_Statement::type.

Referenced by OCI_ExecuteStmt(), OCI_ExecuteStmtFmt(), OCI_ImmediateFmt(), OCI_ServerEnableOutput(), and OCI_ServerGetOutput().

OCI_EXPORT boolean OCI_API OCI_ExecuteStmt ( OCI_Statement stmt,
const mtext *  sql 
)

Parse and execute a SQL statement or PL/SQL block.

Parameters:
stmt - Statement handle
sql - SQL order - PL/SQL block
Returns:
TRUE on success otherwise FALSE

Definition at line 1572 of file statement.c.

References OCI_Execute(), and OCI_Prepare().

Referenced by OCI_DatabaseShutdown(), OCI_DatabaseStartup(), OCI_Immediate(), and OCI_ServerDisableOutput().

OCI_EXPORT unsigned int OCI_API OCI_GetAffectedRows ( OCI_Statement stmt  ) 

Return the number of rows affected by the SQL statement.

Parameters:
stmt - Statement handle
The returned value is :

Note:
For SELECTs statements, use OCI_GetRowCount() instead

Definition at line 3087 of file statement.c.

References OCI_Statement::con, OCI_Connection::err, and OCI_Statement::stmt.

OCI_EXPORT const mtext* OCI_API OCI_GetSql ( OCI_Statement stmt  ) 

Return the last SQL or PL/SQL statement parsed by the statement.

Parameters:
stmt - Statement handle

Definition at line 3061 of file statement.c.

References OCI_Statement::sql.

OCI_EXPORT unsigned int OCI_API OCI_GetSQLCommand ( OCI_Statement stmt  ) 

Return the Oracle SQL code the command holded by the statement handle.

Parameters:
stmt - Statement handle
Warning:
OCI_GetSQLCommand() must be called after the statement has be executed because that's the server engine that computes the SQL command code
Returns:
The SQL command code of the statement otherwise OCI_UNKOWN

Definition at line 3162 of file statement.c.

References OCI_Statement::con, OCI_Connection::err, and OCI_Statement::stmt.

Referenced by OCI_GetSQLVerb().

OCI_EXPORT unsigned int OCI_API OCI_GetSqlErrorPos ( OCI_Statement stmt  ) 

Return the error position in the SQL statement where the error occurred in case of SQL parsing error.

Parameters:
stmt - Statement handle
Note:
Positions start at 1.

Definition at line 3074 of file statement.c.

References OCI_Statement::err_pos.

OCI_EXPORT const mtext* OCI_API OCI_GetSQLVerb ( OCI_Statement stmt  ) 

Return the verb of the SQL command holded by the statement handle.

Parameters:
stmt - Statement handle
Warning:
OCI_GetSQLVerb() must be called after the statement has be executed because that's the server engine that computes the SQL command code
Note:
The SQL command verb list is available in Oracle documentations and guides
Returns:
The SQL command verb of the statement otherwise NULL

Definition at line 3187 of file statement.c.

References OCI_GetSQLCommand().

OCI_EXPORT boolean OCI_API OCI_Prepare ( OCI_Statement stmt,
const mtext *  sql 
)

Prepare a SQL statement or PL/SQL block.

Parameters:
stmt - Statement handle
sql - SQL order or PL/SQL block
Note:
With version 1.3.0 and above, do not call this function for fetched statement handle (REF cursors)
Returns:
TRUE on success otherwise FALSE

Definition at line 1417 of file statement.c.

References OCI_Statement::con, OCI_Connection::err, OCI_Statement::sql, OCI_Statement::status, OCI_Statement::stmt, and OCI_Statement::type.

Referenced by OCI_ExecuteStmt(), OCI_ExecuteStmtFmt(), OCI_ImmediateFmt(), OCI_PrepareFmt(), and OCI_ServerEnableOutput().

OCI_EXPORT OCI_Statement* OCI_API OCI_StatementCreate ( OCI_Connection con  ) 

Create a statement object and return its handle.

Parameters:
con - Connection handle
Returns:
A statement handle on success otherwise NULL

Definition at line 1337 of file statement.c.

References OCI_Connection::stmts.

Referenced by OCI_DatabaseShutdown(), OCI_DatabaseStartup(), OCI_Immediate(), OCI_ImmediateFmt(), and OCI_ServerEnableOutput().

OCI_EXPORT boolean OCI_API OCI_StatementFree ( OCI_Statement stmt  ) 

Free a statement and all resources associated to it (resultsets, ....).

Parameters:
stmt - Connection handle
Returns:
TRUE on success otherwise FALSE

Definition at line 1362 of file statement.c.

References OCI_Statement::con, and OCI_Connection::stmts.

Referenced by OCI_DatabaseShutdown(), OCI_DatabaseStartup(), OCI_Immediate(), OCI_ImmediateFmt(), and OCI_ServerDisableOutput().


Generated on Thu Jul 30 17:41:54 2009 for OCILIB (C Driver for Oracle) by  doxygen 1.5.4