|
|
Home » U++ Library support » U++ SQL » OleDB + parameters = problem
OleDB + parameters = problem [message #19782] |
Sat, 17 January 2009 18:16 |
Novo
Messages: 1362 Registered: December 2006
|
Ultimate Contributor |
|
|
Code:
void Test::remove_person(void)
{
StringBuffer sql;
Sql stmt(db_session);
sql << "DELETE FROM person ";
sql << "WHERE id = ?";
try
{
stmt.SetStatement(sql);
stmt.SetParam(0, sql_person(0));
stmt.ExecuteX();
}
catch (const SqlExc& e)
{
sql_person.CancelRemove();
Exclamation("[* " + DeQtfLf(e) + "]");
}
}
Problem: Execute(OleDB): "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (Microsoft JET Database Engine)
SQL error: DELETE FROM person WHERE id = ?"
The same code was working fine when I wasn't using parameters, and was substituting value into the SQL statement.
Similar approach with parameters works fine with SQLITE database.
Am I missing something?
Regards,
Novo
|
|
|
Re: OleDB + parameters = problem [message #19786 is a reply to message #19782] |
Mon, 19 January 2009 10:14 |
|
mirek
Messages: 13984 Registered: November 2005
|
Ultimate Member |
|
|
Well, I have to admit I am not using parameters mode too much recently; usually it buys you nothing.
Anyway, out of curiosity, I have tried in my current application to replace some SqlExp with explicit params:
#if 1
sql.SetParam(0, cat.GetKey());
sql.Execute("select ID, TEXT from ITEM_TYPE where ITEM_CATEGORY_ID = ? order by TEXT");
#else
SqlBool wh;
if(!IsNull(cat.GetKey()))
wh = ITEM_CATEGORY_ID == cat.GetKey();
sql * Select(ID, TEXT).From(ITEM_TYPE).Where(wh).OrderBy(TEXT);
#endif
In any case, maybe you can try inverse conversion - from SetParam to SqlExp....
BTW, StringBuffer buys you nothing here over plain String and you can simplify your life intensely by using global SQL (as long as you have only single main rdbms):
void Test::remove_person(void)
{
try
{
SQL.SetParam(0, sql_person(0));
SQL.ExecuteX("DELETE FROM person WHERE id = ?");
}
catch (const SqlExc& e)
{
sql_person.CancelRemove();
Exclamation("[* " + DeQtfLf(e) + "]");
}
}
Mirek
|
|
|
Re: OleDB + parameters = problem [message #19789 is a reply to message #19786] |
Tue, 20 January 2009 23:34 |
Novo
Messages: 1362 Registered: December 2006
|
Ultimate Contributor |
|
|
Thanks for your answer.
luzr wrote on Mon, 19 January 2009 04:14 | Well, I have to admit I am not using parameters mode too much recently; usually it buys you nothing.
|
I haven't check how SqlExp with explicit params works, but using parameters with SQL is very important. If you aren't using parameters, then server has to parse your SQL all the time. And very often parsing (and creating an execution plan) is more expensive than executing itself.
Quote: |
In any case, maybe you can try inverse conversion - from SetParam to SqlExp....
|
Actually, I'm planing to move from SetParam to my own database code as soon as I will be able to integrate it into TheIDE build system, which is unfortunately very hard to use in workspace-like way.
Regards,
Novo
|
|
|
|
|
|
Re: OleDB + parameters = problem [message #19807 is a reply to message #19793] |
Fri, 23 January 2009 05:55 |
Novo
Messages: 1362 Registered: December 2006
|
Ultimate Contributor |
|
|
luzr wrote on Wed, 21 January 2009 08:14 |
OTOH, it is still supported. Note also that one nice way how to keep very frequent SQL statements precompiled on the server is to have them static:
void Test::remove_person(void)
{
static Sql stmt("DELETE FROM person WHERE id = ?");
Mirek
|
Precompiled statements get invalidated after you execute a DDL-statement, if I recall that correctly. I think that managing of precompiled statements is more complicated than just declaring a statement static.
Regards,
Novo
|
|
|
Re: OleDB + parameters = problem [message #19808 is a reply to message #19806] |
Fri, 23 January 2009 06:03 |
Novo
Messages: 1362 Registered: December 2006
|
Ultimate Contributor |
|
|
Novo wrote on Thu, 22 January 2009 23:48 | I can try to build drivers for other databases and develop more tests. But that will take some time.
|
B.T.W. Improving of UPP-build system would help a lot in this task.
Regards,
Novo
|
|
|
Re: OleDB + parameters = problem [message #19811 is a reply to message #19807] |
Fri, 23 January 2009 11:36 |
|
mirek
Messages: 13984 Registered: November 2005
|
Ultimate Member |
|
|
Novo wrote on Thu, 22 January 2009 23:55 |
luzr wrote on Wed, 21 January 2009 08:14 |
OTOH, it is still supported. Note also that one nice way how to keep very frequent SQL statements precompiled on the server is to have them static:
void Test::remove_person(void)
{
static Sql stmt("DELETE FROM person WHERE id = ?");
Mirek
|
Precompiled statements get invalidated after you execute a DDL-statement, if I recall that correctly. I think that managing of precompiled statements is more complicated than just declaring a statement static.
|
How so? That would mean invalidating secondary cursor, IMO, impossible.
Consider:
Sql sql1, sql2;
sql1 * Select(ID).From(DATA).Where(mycondition);
while(sql1.Fetch())
sql2 * Update(DATA)(FIELD, value).Where(ID == sql1[ID]);
Any RDBMS worth of its name must support such loop. And if this loop works, I believe that declaring cursor static should work too (but I might be wrong, of course).
Mirek
|
|
|
Re: OleDB + parameters = problem [message #19812 is a reply to message #19806] |
Fri, 23 January 2009 11:52 |
|
mirek
Messages: 13984 Registered: November 2005
|
Ultimate Member |
|
|
OK, you have been proved right w.r.t. prepared statements and speed, at least with MSSQL:
void Benchmark()
{
Sql sql;
sql * Select(ID).From(SUBJECT);
Vector<int> id;
while(sql.Fetch())
id.Add(sql[0]);
for(int i = 0; i < id.GetCount(); i++) {
RTIMING("Unprepared");
sql % Select(TEXT).From(SUBJECT_ATTR).Where(ATTR == "NAME" && SUBJECT_ID == id[i]);
}
sql.SetStatement("select TEXT from SUBJECT_ATTR where ATTR = ? and SUBJECT_ID = ?");
for(int i = 0; i < id.GetCount(); i++) {
RTIMING("Prepared");
sql.Run("NAME", id[i]);
sql.Fetch();
}
}
TIMING Prepared : 79.99 ms - 273.94 us (80.00 ms / 292 ), min: 0.00 ns, max: 1.00 ms, nesting: 1 - 292
TIMING Unprepared : 483.99 ms - 1.66 ms (484.00 ms / 292 ), min: 0.00 ns, max: 15.00 ms, nesting: 1 - 292
Well, SqlExp cache will be fun Thanks for makeing me aware about this.
Mirek
|
|
|
|
Re: OleDB + parameters = problem [message #19820 is a reply to message #19813] |
Sat, 24 January 2009 17:58 |
Novo
Messages: 1362 Registered: December 2006
|
Ultimate Contributor |
|
|
luzr wrote on Fri, 23 January 2009 05:59 | Anyway, it seems like DDL does not have such bad effect on precompiled statements after all:
|
I checked my code.
In case of DDL statement and:
ADO - (I was using it with JET and MSSQL Server) I commit transaction and clear statement cache.
SQLITE - clear statement cache and finalize current statement (I'm using old API).
Interbase/Firebird - clear statement cache, rollback transaction, execute DDL, commit transaction. (This database has slightly different transaction model).
ODBC – rollback transaction, execute DDL, commit transaction. Cache is not affected. Probably, this is a bug, or it was tested only with MS SQL Server.
ORACLE - clear statement cache, execute DDL, mark all open statements as invalidated by DDL (they should be prepared again).
CTLIB (Sybase and MS SQL Server) – no problem with cache.
Microsoft has different APIs to work with MS SQL Server. Prepare-Bind-Execute model is implemented using special stored procedures, which are often called by a high-level API. You can check that in FreeTDS ODBC driver. It is open-source.
Regards,
Novo
|
|
|
Re: OleDB + parameters = problem [message #19821 is a reply to message #19820] |
Sat, 24 January 2009 20:19 |
|
mirek
Messages: 13984 Registered: November 2005
|
Ultimate Member |
|
|
Novo wrote on Sat, 24 January 2009 11:58 |
luzr wrote on Fri, 23 January 2009 05:59 | Anyway, it seems like DDL does not have such bad effect on precompiled statements after all:
|
I checked my code.
In case of DDL statement and:
ADO - (I was using it with JET and MSSQL Server) I commit transaction and clear statement cache.
SQLITE - clear statement cache and finalize current statement (I'm using old API).
Interbase/Firebird - clear statement cache, rollback transaction, execute DDL, commit transaction. (This database has slightly different transaction model).
ODBC – rollback transaction, execute DDL, commit transaction. Cache is not affected. Probably, this is a bug, or it was tested only with MS SQL Server.
ORACLE - clear statement cache, execute DDL, mark all open statements as invalidated by DDL (they should be prepared again).
CTLIB (Sybase and MS SQL Server) – no problem with cache.
Microsoft has different APIs to work with MS SQL Server. Prepare-Bind-Execute model is implemented using special stored procedures, which are often called by a high-level API. You can check that in FreeTDS ODBC driver. It is open-source.
|
Well, sorry, my mistake, I have messed DDL with DML...
Maybe that why I have not ever encountered the problem in practice.
Seriously, do you often issue DDL during the application run?
IMO, in any multiuser environment, such thing is extremely unlikely because regular users do not have rights to do DDL.
OTOH, if I ever add automated prepared statement cache with SqlExp, "Invalidate" seems like a good idea, right?
Mirek
Mirek
|
|
|
Re: OleDB + parameters = problem [message #19822 is a reply to message #19821] |
Sun, 25 January 2009 04:44 |
Novo
Messages: 1362 Registered: December 2006
|
Ultimate Contributor |
|
|
luzr wrote on Sat, 24 January 2009 14:19 |
Seriously, do you often issue DDL during the application run?
|
Happens all the time in my unit-tests.
Seriously, such situation is possible when you create a database schema and populate it with initial data.
Everything, what is theoretically possible, will be done by an user.
Quote: |
OTOH, if I ever add automated prepared statement cache with SqlExp, "Invalidate" seems like a good idea, right?
|
That's right. Actually, cache of prepared statements has sense only in case of using parameters, otherwise there is no reason to reuse statements.
Regards,
Novo
|
|
|
Goto Forum:
Current Time: Sat Jun 08 08:44:18 CEST 2024
Total time taken to generate the page: 0.02101 seconds
|
|
|