Issue 101841

The SQL table structure is simply:

CREATE TABLE ztest01(
col1 varchar(50)
,col2 varchar(50)
,col3 varchar(50)
) ;

One workaround I just discovered is to use the preparedStatement() method
instead. Although it means changing my SQL-building code & requires setting
specific types on the client side instead of implicit & explicit server-side
casting, it exhibits no memory leakage whatsoever. Another added advantage:
com.sun.star.sdb.OPreparedStatement.execute() is also vastly faster than
com.sun.star.sdb.OStatement.execute() or executeUpdate()

But it'd be really grand to have the memory leak in con.statement fixed.

Example:

dbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = dbContext.getByName("mydb01")
oDBCon = oDataSource.GetConnection("userid","password")

oPrepStmt = oDBcon.prepareStatement("INSERT INTO dbo.ztest01 _
                  ( col1, col2, col3 ) VALUES ( ?, ?, ? )")

for i = 1 to 100
   oPrepStmt.setString(1, "blah")
   oPrepStmt.setString(2, "blah")
   oPrepStmt.setString(3, "blah") 
   oPrepStmt.execute()
      
next i

sounds like a database issue

putting into DBA's QA pool

Just FYI,

I observed yesterday that a prepared statement also leaked memory if it was
issued (instantiated) repeatedly. This might be acceptable for now given that I
will be able to set up  a single prepared SQL statement & just clear & use only
the parameters as needed.

Thanks!

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...

I just tested it with a DEV300m94 which in that area is identical with a OOo 3.3
and I could not reproduce it with the embedded db (hsqldb). No leak which I can
see. Do you have a sample db which I could test? Or may the issue is fixed in
that release :-) Could you please check. Thanks.