Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
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
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Am Dienstag, den 16.02.2010, 11:52 +0100 schrieb Fernand Vanrie:
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.
Very well done, Fernand!
Hope this is usefull information for who trye to use stored procedures
As soon as I need it I will do some tests.
Marc
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
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: CREATE FUNCTION an_hour_before (t TIMESTAMP) RETURNS TIMESTAMP RETURN t - 1 HOUR 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 - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Am 12.02.10 08:29, schrieb Frank Schoenheit, Sun Microsystems Germany: Hi Marc, Maybe a third mode could help here: Semi-Native SQL, where base would only scan for parameter definitions using the :name scheme. Hmm, don't like this idea too much. Basically, it would be completely intransparent to the user. This is not per se a bad thing :), but if things are prone to failure (and parsing queries which the user said don't even attempt to do *are*), then we should not do them silently, and without a chance for the user to intervene. So what can we do about the issue at hand? Most stored procedures take parameters (else we could easily make them view, right?) Can the internal SQL parser be changed to accept statements like SELECT * FROM function(:parameter) ? Should be fairly straightforward to distinguish a table name from a function name. [0=1] What a hack ;) Well, yes, kind of :). On the other hand, it worked well for a decade or so, and as said, I assume the original authors had reasons for doing it. Still, I'd say we should think about removing it. Yes. Kinda weird construct ;) Ciao Frank - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Marc , you told : That makes, in consequence, stored procedures unusable with OpenOffice base. 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 If we talking about General users it could been a good start to make StoredProcedures visible in the GUI before we adding bells like parameters etc I would not call parameters to function calls bells, I would call it a necessity. Other users (who have acces to there own Databases) do not need other than Native Mode because they written already the Stored Procedures in the very same Native SQL Stored procedures usually need parameters, which you can not enter in native mode. That makes, in consequence, stored procedures unusable with OpenOffice base. - Marc Greetz Fernand Hi Marc, Maybe a third mode could help here: Semi-Native SQL, where base would only scan for parameter definitions using the :name scheme. Hmm, don't like this idea too much. Basically, it would be completely intransparent to the user. This is not per se a bad thing :), but if things are prone to failure (and parsing queries which the user said don't even attempt to do *are*), then we should not do them silently, and without a chance for the user to intervene. [0=1] What a hack ;) Well, yes, kind of :). On the other hand, it worked well for a decade or so, and as said, I assume the original authors had reasons for doing it. Still, I'd say we should think about removing it. Ciao Frank - 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
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
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 If we talking about General users it could been a good start to make StoredProcedures visible in the GUI before we adding bells like parameters etc I would not call parameters to function calls bells, I would call it a necessity. Other users (who have acces to there own Databases) do not need other than Native Mode because they written already the Stored Procedures in the very same Native SQL Stored procedures usually need parameters, which you can not enter in native mode. That makes, in consequence, stored procedures unusable with OpenOffice base. - Marc Greetz Fernand Hi Marc, Maybe a third mode could help here: Semi-Native SQL, where base would only scan for parameter definitions using the :name scheme. Hmm, don't like this idea too much. Basically, it would be completely intransparent to the user. This is not per se a bad thing :), but if things are prone to failure (and parsing queries which the user said don't even attempt to do *are*), then we should not do them silently, and without a chance for the user to intervene. [0=1] What a hack ;) Well, yes, kind of :). On the other hand, it worked well for a decade or so, and as said, I assume the original authors had reasons for doing it. Still, I'd say we should think about removing it. Ciao Frank - 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 - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Marc, 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 :-) 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 If we talking about General users it could been a good start to make StoredProcedures visible in the GUI before we adding bells like parameters etc I would not call parameters to function calls bells, I would call it a necessity. Other users (who have acces to there own Databases) do not need other than Native Mode because they written already the Stored Procedures in the very same Native SQL Stored procedures usually need parameters, which you can not enter in native mode. That makes, in consequence, stored procedures unusable with OpenOffice base. - Marc Greetz Fernand Hi Marc, Maybe a third mode could help here: Semi-Native SQL, where base would only scan for parameter definitions using the :name scheme. Hmm, don't like this idea too much. Basically, it would be completely intransparent to the user. This is not per se a bad thing :), but if things are prone to failure (and parsing queries which the user said don't even attempt to do *are*), then we should not do them silently, and without a chance for the user to intervene. [0=1] What a hack ;) Well, yes, kind of :). On the other hand, it worked well for a decade or so, and as said, I assume the original authors had reasons for doing it. Still, I'd say we should think about removing it. Ciao Frank - 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 - 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
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Hi Marc, Can the internal SQL parser be changed to accept statements like SELECT * FROM function(:parameter) ? Probably, yes (but Ocke has a saying in this, he has deeper insights in the parser implementation). Feel free to submit an issue. Ciao Frank -- - Frank Schönheit, Software Engineer frank.schoenh...@sun.com - - Sun Microsystems http://www.sun.com/staroffice - - OpenOffice.org Base http://dba.openoffice.org - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
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
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Hi Marc, I can, however, set the query editor to native SQL mode, then the SQL code is passed as is to the database backend and no error is output. But with native SQL mode, I loose the possibility to have OpenOffice ask the user for parameters. I can only use calls without parameters. But most stored procedurs actually require paramates. Indeed, native queries do not allow for parameters. This is caused by the fact that for recognizing the parameters, Base would need to parse the statement, which contradicts the do-not-parse restricting imposed by the native flag. Some of the underlying APIs (JDBC, for instance) support retrieving parameter information from the database/driver, but that's not used in Base currently. Issue 66559 would probably fix this, at least for all JDBC based connections (the issue talks about HSQL only, but that wouldn't matter much). Another option - which I am not sure an issue exists for - would be to let the creator of the native query define which parameters to ask the user for - would be the responsibility of the creator then to ensure consistency. There is a second (and maybe unrelated) problem: OpenOffice Base seems for each query to send a generated query to the database server to check if that table in the query exists. It does something like SELECT COUNT(*) FROM tablename WHERE 0 = 1 This will of course never return any data, but it will raise an error if tablename does not exist, so I assume this is done to check for the presence of a table. But again, this does not work when a query instead of table specifies a stored procedure in a SELECT. For historic reasons, the WHERE 0 = 1 thing is done for retrieving column information, with a fallback to ask the meta data of the prepared statement. I suppose the latter didn't work properly formerly, for a certain set of database/drivers, so WHERE 0 = 1 had been invented (well, that's not really an invention of Base), since the meta data of a result set (which is forced to be empty) were more reliable than the meta data of the prepared statement. If you're saying this happens even for native queries, then please submit an issue for it - it should be done for non-native queries only, IMO. Ciao Frank -- - Frank Schönheit, Software Engineer frank.schoenh...@sun.com - - Sun Microsystems http://www.sun.com/staroffice - - OpenOffice.org Base http://dba.openoffice.org - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Hello Marc Balmer,
It would be great if you could submit an issue and perhaps could add a
sample db dump (only with dummy data and one sample procedure) for a
test case. And assign it to me o...@openoffice.org.
Best regards,
Ocke
Marc Balmer wrote:
Good day
We develop point of sales (POS) applications using PostgreSQL as the
database backend. We thought it could be interesting for our customers
to access the database directly from OpenOffice Base to create their own
reports etc. But it turns out that OpenOffice Base and PostgreSQL are
not playing together nicely:
Our database consists of tables (of course..), views, and a substantial
number of stored procedures, especially for the more complex reports and
such.
E.g, to get the balance of all accounts, you would send the following
SQL code to the database:
SELECT * FROM acf_balance_all()
To get the balance from only on account, you would use
SELECT * FROM acf_balance('1000')
('1000' is the account number).
When I enter the SQL code in the query editor in non-native SQL mode, it
outputs a syntax error, i.e. it seems to be unable to handle the
PostgreSQL stored procedure calls. I can, however, set the query editor
to native SQL mode, then the SQL code is passed as is to the database
backend and no error is output. But with native SQL mode, I loose the
possibility to have OpenOffice ask the user for parameters. I can only
use calls without parameters. But most stored procedurs actually
require paramates.
There is a second (and maybe unrelated) problem: OpenOffice Base seems
for each query to send a generated query to the database server to check
if that table in the query exists. It does something like
SELECT COUNT(*) FROM tablename WHERE 0 = 1
This will of course never return any data, but it will raise an error if
tablename does not exist, so I assume this is done to check for the
presence of a table. But again, this does not work when a query instead
of table specifies a stored procedure in a SELECT.
I am using OpenOffice Base on Mac OS X Snow Leopard using the
jdbc.postgresql JDBC driver and PostgreSQL 8.4.2 as the database
backend. I did not try any native client, since this is a bug that I
want to report (and, if possible, see fixed) regardless if a native
driver would work.
If there are any details I can further provide, or any test I can run,
please me know.
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
--
Ocke Janssen Tel: +49 40 23646 661, x1
Dipl. Inf(FH) Fax: +49 40 23646 550
Sun Microsystems Inc.
Nagelsweg 55 mailto:ocke.jans...@sun.com
D-20097 Hamburg http://www.sun.com/staroffice
Sitz der Gesellschaft:
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Amtsgericht Muenchen: HRB 161028
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels
Vorsitzender des Aufsichtsrates: Martin Haering
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Marc ,
We uses also Stored Procedures on a Msql server To make all things
work , we handles all user input (parameters calculatutions,
translations etc...) with OO-dialogs and some macro's to create the SQL
statements writen to please the ODBC driver and the Msql server :-)
Good day
We develop point of sales (POS) applications using PostgreSQL as the
database backend. We thought it could be interesting for our customers
to access the database directly from OpenOffice Base to create their own
reports etc. But it turns out that OpenOffice Base and PostgreSQL are
not playing together nicely:
Our database consists of tables (of course..), views, and a substantial
number of stored procedures, especially for the more complex reports and
such.
E.g, to get the balance of all accounts, you would send the following
SQL code to the database:
SELECT * FROM acf_balance_all()
To get the balance from only on account, you would use
SELECT * FROM acf_balance('1000')
('1000' is the account number).
When I enter the SQL code in the query editor in non-native SQL mode, it
outputs a syntax error, i.e. it seems to be unable to handle the
PostgreSQL stored procedure calls. I can, however, set the query editor
to native SQL mode, then the SQL code is passed as is to the database
backend and no error is output. But with native SQL mode, I loose the
possibility to have OpenOffice ask the user for parameters. I can only
use calls without parameters. But most stored procedurs actually
require paramates.
There is a second (and maybe unrelated) problem: OpenOffice Base seems
for each query to send a generated query to the database server to check
if that table in the query exists. It does something like
SELECT COUNT(*) FROM tablename WHERE 0 = 1
This will of course never return any data, but it will raise an error if
tablename does not exist, so I assume this is done to check for the
presence of a table. But again, this does not work when a query instead
of table specifies a stored procedure in a SELECT.
I am using OpenOffice Base on Mac OS X Snow Leopard using the
jdbc.postgresql JDBC driver and PostgreSQL 8.4.2 as the database
backend. I did not try any native client, since this is a bug that I
want to report (and, if possible, see fixed) regardless if a native
driver would work.
If there are any details I can further provide, or any test I can run,
please me know.
-
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
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
SELECT * FROM acf_balance_all()
To get the balance from only on account, you would use
SELECT * FROM acf_balance('1000')
('1000' is the account number).
I'd guess the problem is the ability of DBA to bind columns for display.
That is, while postgres understands the columns which are going to come
back from an SP, DBA does not, since they are different metadata from
table columns.
For stored procedures which do not require a parameter, then you could
wrap them in views and I'd expect those to work. But for an example
like the above, that's no help.
--Josh Berkus
-
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures
Am 08.02.10 13:35, schrieb Fernand Vanrie:
We uses also Stored Procedures on a Msql server To make all things
work , we handles all user input (parameters calculatutions,
translations etc...) with OO-dialogs and some macro's to create the SQL
statements writen to please the ODBC driver and the Msql server :-)
While I am aware of this possibility, it does not fix the problems in
OpenOffice Base. There has to be a better solution.
Good day
We develop point of sales (POS) applications using PostgreSQL as the
database backend. We thought it could be interesting for our customers
to access the database directly from OpenOffice Base to create their own
reports etc. But it turns out that OpenOffice Base and PostgreSQL are
not playing together nicely:
Our database consists of tables (of course..), views, and a substantial
number of stored procedures, especially for the more complex reports and
such.
E.g, to get the balance of all accounts, you would send the following
SQL code to the database:
SELECT * FROM acf_balance_all()
To get the balance from only on account, you would use
SELECT * FROM acf_balance('1000')
('1000' is the account number).
When I enter the SQL code in the query editor in non-native SQL mode, it
outputs a syntax error, i.e. it seems to be unable to handle the
PostgreSQL stored procedure calls. I can, however, set the query editor
to native SQL mode, then the SQL code is passed as is to the database
backend and no error is output. But with native SQL mode, I loose the
possibility to have OpenOffice ask the user for parameters. I can only
use calls without parameters. But most stored procedurs actually
require paramates.
There is a second (and maybe unrelated) problem: OpenOffice Base seems
for each query to send a generated query to the database server to check
if that table in the query exists. It does something like
SELECT COUNT(*) FROM tablename WHERE 0 = 1
This will of course never return any data, but it will raise an error if
tablename does not exist, so I assume this is done to check for the
presence of a table. But again, this does not work when a query instead
of table specifies a stored procedure in a SELECT.
I am using OpenOffice Base on Mac OS X Snow Leopard using the
jdbc.postgresql JDBC driver and PostgreSQL 8.4.2 as the database
backend. I did not try any native client, since this is a bug that I
want to report (and, if possible, see fixed) regardless if a native
driver would work.
If there are any details I can further provide, or any test I can run,
please me know.
-
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
