will you provide new functions to repeat using dpiStmt

[dwxiayi]

I find function: // prepare a statement and return it for subsequent execution/fetching int dpiConn_prepareStmt(dpiConn conn, int scrollable, const char sql, uint32_t sqlLength, const char *tag, uint32_t tagLength, dpiStmt **stmt); that means when executing a sql, we should call dpiConn_prepareStmt one time. will you some functions likes： 1.allocate dpiStmt 2.execute sql 3.get affectedrows 4.get resultset 5.close resultset 6.repeat 1-5 7.close dpiStmt thinks.

[anthony-tuininga]

Can you elaborate further on what you are hoping to see? Perhaps with an example? Currently, the way you repeat executions is as follows:

// set up statement to execute and perform all binds and defines (one-time) dpiConn_prepareStmt() dpiStmt_bindByPos() dpiStmt_bindByPos() ... dpiStmt_define() dpiStmt_define() ...

// execute and fetch (multiple times) modify bind variable values by manipulating dpiData structures dpiStmt_execute() dpiStmt_fetch() examine defined variable values by reading dpiData structures ...

How you were you thinking this could be improved?

[dwxiayi]

I'm sorry,step6 is repeat 2-5,step2 with different sql. In occi it provide functions： virtual Statement Connection::createStatement(const UString &sql); virtual void Statement::setSQL(const string &sql); virtual Status Statement::execute(const string &sql = ""); virtual ResultSet Statement::executeQuery( const string &sql = ""); virtual unsigned int Statement::executeUpdate( const string &sql = ""); so I can re-use Statement,with one call createStatement. In ocilib it provide same functions,likes: OCI_Statement OCI_StatementCreate(OCI_Connection con); int OCI_Prepare( OCI_Statement stmt, const char sql); int OCI_Execute( OCI_Statement stmt); int OCI_ExecuteStmt( OCI_Statement stmt, const char *sql);

[anthony-tuininga]

Since you closed this I assume you are no longer interested in these changes?

[dwxiayi]

this days，I try to change my wrapper oracle-db-driver from occi to oci or other libs writed by C language，I think it may has good performance and more functions.however，I come up against difficulties，they are above oci，OCI seem don't support coroutine(coroutine can see as user state thread, when dbserver is busy for data-search、commit or other things，dbclient will change stack,and execute other coroutine do something in one thread, dbclient will continue work on dbserver respond(data recv), so it will have a good performance, useing less threads or processes), I use coroutine lib(https://github.com/Tencent/libco), but it behave ok when less than 20 coroutine execute in one thread, but when more than 20 coroutine execute sql, the process will be killed by system. so if this question not be solved, change occi to other lib is meaningless.

[dwxiayi]

hi anthony-tuininga, do you interested in coroutine?

[anthony-tuininga]

I am interested in anything that will improve performance! If you want to use coroutines with ODPI-C, you will need to create a session pool (dpiPool) and then for each thread/coroutine you need to acquire a connection from that pool. OCI does not provide (currently anyway!) the ability to execute multiple statements asynchronously using one connection. But if you use a session pool you can get a very reasonable approximation. :-) Perhaps try that and see how it works for you?