cloud not return connections to pool

[uncle qian]

Hi, manager:

Thanks for libzdb first.

when I used libzdb like bellow, I found the connection cloud not return to the pool.

libzdb version: 3.2.2

compile configure: /configure --without-postgresql --without-sqlite --with-mysql --enable-protected

sample code:

URL_T url = URL_new("mysql://xx.xx.xx.xx:3306?user=xx&password=xx");

ConnectionPool_T sql_pool = ConnectionPool_new(url);
ConnectionPool_setInitialConnections(sql_pool, 5);
ConnectionPool_setMaxConnections(sql_pool, 10);
ConnectionPool_start(sql_pool);

in the multithread used connection like this:

int ret = 0;

Connection_T con = ConnectionPool_getConnection(sql_pool);
if(con == NULL) {
loge("db Connection failed.\n");
return -1;
}

TRY {
Connection_setQueryTimeout(con, 5 * 1000);
Connection_execute(con,
"sql strings here");
​}
CATCH(SQLException) {
printf("SQLException -- %s\n", Exception_frame.message);

​    ​    ​ret = -1;

}
FINALLY {
Connection_commit(con);
Connection_close(con);
}
END_TRY;
return ret;

the connection could not return to the pool

but when I did not used TRY{} CATCH()｛｝FINALLY{}, it works well.

Look forward to your reply and best wishes!

THANKS!

[Tildeslash]

The correct sequence should be something like this:

TRY {
    Connection_execute(con, "sql strings here");
    Connection_commit(con);
​} CATCH(SQLException) {
    printf("SQLException -- %s\n", Exception_frame.message);
​    ​​ret = -1;
} FINALLY {
    Connection_close(con);
}
END_TRY;

That is, you cannot commit in the finally block because you do not know if an error occurred, in which case commit might also fail with a new exception.

[uncle qian]

I tested it the way you said, and it worked very well, thanks a lot

‌

[Tildeslash]

Glad to hear that. I should also mention that Connection_commit should be preceded with Connection_beginTransaction So a complete transaction example would be something like this;

TRY {
    Connection_beginTransaction(con);
    Connection_execute(con, "sql strings here");
    Connection_commit(con);
​} CATCH(SQLException) {
    printf("SQLException -- %s\n", Exception_frame.message);
​    ​​ret = -1;
} FINALLY {
    Connection_close(con);
}
END_TRY;

In the CATCH block you might add Connection_rollback, but this is not necessary as Connection_close will automatically do a rollback if we are in a transaction which has not been committed.