So far i found a lot of things not working with Stored Procedures, but
finaly :-) i got a sort of workaround for a Stored Procedure on MySql
with parameters.
SUB TESTstoredprocedurecall
oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oBaseContext.getByName("mysql_native")
oCon = oDB.getConnection("user", "Password")
oStatement = oCon.createStatement()
sSQL1 = "call teststoredprocedure('Parm1', '¨Parm2')" ' must been pased
in right order
ostatement.execute(sSQL1)' gives True
oResultset1 = ostatement.getresultset
oResultSet1.next
print oResultset1.getstring(1)' confirmes the selection based on parameters
ostatement.close ' must been done otherwise we ran in a
Exception:'sdbc.SQLExepetion "Commands out of sync, you can not run this
command now"
'after this exception you need to restart OO or dispose oDB
ocon.close ' just to be sure everyting isclosed
end sub
this Exception "Commands out of sync, you can not run this command
now" make the use of "PreparedStatements" useless I will file a Issue
if this is realy a bug.
Hope this is usefull information for who trye to use stored procedures
Fernand
Marc Santhoff wrote:
Am Samstag, den 13.02.2010, 18:22 +0100 schrieb Marc Santhoff:
And just here i am lost: how works this registering of the
parameters ?
Dunno, my assumption was that it is not strictly necessary ... I tink
someone else has to answer this question.
A quick look into chapter 8 of the HSQL docs shows:
<quote>
The routine body is a SQL statement. In its simplest form, the body is a
single SQL statement. A simple example of a function is given below:
An example of the use of the function in an SQL statement is given
below:
SELECT an_hour_before(event_timestamp) AS notification_timestamp,
event_name FROM events;
</quote>
The other SQL statement for calling procedures, in contrast to
functions, would be "CALL".
So chances are good that a prepared statement on the OOo side of things
is sufficient for transferring parameters to a statement build up by a
stored function or stored procedure call.
HTH anyway,
Marc
Hi Marc
That is good for single return values, but not for returning multiple
parameters or for returning a cursor to a result set.
So, sure there is some access to the stored procedures with the
current implementation but it isn't yet ready to take full advantage
them - IMO.
Thanks,
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
