Am Samstag, den 13.02.2010, 13:41 +0100 schrieb Fernand Vanrie: > 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 ?
Dunno, my assumption was that it is not strictly necessary ... I tink someone else has to answer this question. > I googled a lot about this "prepareCall()", but nobody gets it works . Has somebody tried? If so, where can I look at it? > 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" ? They sure are. Stored procedures can be really fast, because the database server has the most direct access to the underlying data and they can do complex evaluations without sending masses of data through the line to the client. If you do complex things on the client side each access to the tables has to be done via the connection, which mostly is a network. The downside of stored procs is (or was some years ago) that each database system has it's own language and syntax. If the database is to be changed, maybe the customer wants it or you have several customers with existing databases, all stored procs have to be written twice. I would love to know if there has been sort of a standardization process of languages used for stored procedures in the mainly used databases (PostgreSQL, MySQL, HSQL, Firebird/Interbase, Oracle, ...). > > 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: dev-unsubscr...@dba.openoffice.org > > For additional commands, e-mail: dev-h...@dba.openoffice.org > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org > For additional commands, e-mail: dev-h...@dba.openoffice.org > --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
