To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=101841
------- Additional comments from [email protected] Fri May 15 14:22:22 +0000 2009 ------- For SQL Server, Sybase (& probably Oracle & DB/2) I found the best hybrid method so far: Use a prepared statement hybrid using SQL Server's "exec()" command. Simply prepare a typical SQL string (insert, whatever) and call SQL Server's (originally Sybase's) "exec ( ... )" function with the SQL string simply embedded as a prepared statement "?" arg marker. FWIW this has both advantages of static string SQL (no field-by-field parameter matching necessary) and being wicked fast, too. It appears to me to be 10x faster than using macro-basic's oStatement.executeUpdate(...). CODE <CODE>: dbContext = createUnoService("com.sun.star.sdb.DatabaseContext") oDataSource = dbContext.getByName("mydb") oDBcon = oDataSource.GetConnection("userid","password") ' set up the SQL parameter using the prep'd statement "?" marker, calling SQL Server's ' exec() dynamic runtime command function oPrepStmt = oDBcon.prepareStatement( "exec ( ? )" ) for i = 1 to 1000 ' match the "?" marker as parameter "1", using setString() to make an entire ' SQL insert string as a prep'd statement parameter. oPrepStmt.setString( 1, "insert into ztest01 ( col00 ) values ( '" & cstr(now()) & "' )" ) oPrepStmt.execute() ' clear out the old statement, ready for the next one.... oPrepStmt.clearParameters() next i oPrepStmt.close oPrepStmt.dispose() oDBcon.close oDBcon.dispose() </CODE> ============ I'm assuming the performance gain would be equivalent for all other SQL operations as well. Oracle's PL-SQL equivalent is EXEC SQL EXECUTE IMMEDIATE <sql command>. DB/2's SQL-PL probably has it too. Obviously if one's DBMS-of-choice doesn't have an EXEC command handy there's probably a way to make a stored procedure wrapper for SQL commands to take advantage of the speed gain from preparedStatement that emulates the convenience of "static" string-based SQL, avoiding the typical problem of parameter matching of prepared statements (i.e. insert into ztable (col00, col001, col002) values ( ? , ? , ? ). FWIW there's also a problem somewhere in sdbc:odbc bridge or MS's ODBC that oPreparedStatement.setDate( com.sun.star.util.Date ) will yield a "option/feature not implemented" error, so string-built SQL is more than just a convenience in OO Basic... --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
