Marc, (the other one :-))
Thanks for your interest, i will test your "preparedstatement" code a
soon i maded some "test" storedprocedures on a MySQL base.
But i am still troubled about the use like it is is writen in the DEV guide.
Ther they say to use a "Preparecall" :
The method prepareCall()takes a an SQL statement that may contain one or more
'?|' in parameter placeholders. It returns a
com.sun.star.sdbc.CallableStatement
A CallableStatement is a com.sun.star.sdbcx.PreparedStatement
with two additional interfaces for out parameters:
prepareCall() of the interface com.sun.star.sdbc.XConnection
is used to declare parameters as out parameters. All out parameters must
be registered before a stored procedure is executed.
And just here i am lost: how works this registering of the parameters ?
I googled a lot about this "prepareCall()", but nobody gets it works .
Greetz
Fernand
BTW: Are Stored Procedures (when using OO as a frontend and the API with Basic code) any
help at all. Is there any advantage to store SQL statements in a Stored Procedure and not
in a "Prepared Statement" ?
Am Freitag, den 12.02.2010, 15:42 +0100 schrieb Fernand Vanrie:
Marc,
I'm another one, but maybe I can give a little help. ;)
Its even worse ,after further investigation i found no code (API +
basic) to run a stored procedure with parameters.
So I would be very happy i someone could give a hint :-)
Since stored procedures behave much like any other database object, like
tables, views and the like, I think a stored proc can be used just like
those.
What I have to offer is some snippets from an old testing program I
wrote once firing a prepared statement at a database. Maybe it helps you
and others to get the idea, and please report back if that technique can
be used for starting stored procedures:
<BASIC>
'... get a database context object and make a connection ...
sSQL = "UPDATE " & sTableName & " SET"
'... calculate some names ...
sSQL = sSQL & " WHERE " & colnames(keycolumn) & "= ?"
' get a prepared statement from the connection
oPst = oCon.prepareStatement(sSQL)
' set the parameter value(s) like the columns at any Statement
oPst.setInt(0, 42)
' ...
' this assumes no return value, I *think* for getting
' something back ExecuteSQL() has to be used
oPst.ExecuteUpdate()
</BASIC>
I have no idea if and how native sql mode is involved or influencing
here.
HTH and have fun,
Marc
thanks for any help
Fernand
you told : "That makes, in consequence, stored procedures unusable with
OpenOffice base".
Well, I probably need to refine that a bit, indeed: It makes stored
procedures unusable in OpenOffice base using the query editor.
This is countrary to what the DEV guide tells us ????
Stored procedures are server-side processes execute several SQL commands
in a single step, and can be embedded in a server language for stored
procedures with enhanced control capabilities. A procedure call usually
has to be parameterized, and the results are result sets and additional
out parameters. Stored procedures are handled by the method
|prepareCall()| of the interface com.sun.star.sdbc.XConnection
<http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XConnection.html>.
com::sun::star::sdbc::XPreparedStatement prepareCall( [in] string sql)
The method |prepareCall()| takes a an SQL statement that may contain one
or more '|?|' in parameter placeholders. It returns a
com.sun.star.sdbc.CallableStatement
<http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/CallableStatement.html>.
A |CallableStatement| is a com.sun.star.sdbcx.PreparedStatement
<http://api.openoffice.org/docs/common/ref/com/sun/star/sdbcx/PreparedStatement.html>
with two additional interfaces for out parameters:
com.sun.star.sdbc.XOutParameters
<http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XOutParameters.html>
is used to declare parameters as out parameters. All out parameters must
be registered before a stored procedure is executed.
greetz
Fernand
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
