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
