Problem: OpenOffice Base and PostgreSQL stored procedures

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Ocke Janssen
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 [hidden email].

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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>  


--
Ocke Janssen                      Tel: +49 40 23646 661, x66661
Dipl. Inf(FH)                     Fax: +49 40 23646 550
Sun Microsystems Inc.
Nagelsweg 55                      mailto:[hidden email]
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Fernand Vanrie
In reply to this post by Marc Balmer-2
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: [hidden email]
> For additional commands, e-mail: [hidden email]
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Josh Berkus
In reply to this post by Marc Balmer-2

> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
In reply to this post by Fernand Vanrie
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: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>  
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

frank.schoenheit
In reply to this post by Marc Balmer-2
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         [hidden email] -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
Frank,

>> 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.

Maybe a third mode could help here:  Semi-Native SQL, where base would
only scan for parameter definitions using the :<name> scheme.

> 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.

What a hack ;)

>
> 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.

No, it only happens for parsed queries.

- Marc

>
> Ciao
> Frank


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

frank.schoenheit
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

--
- Frank Schönheit, Software Engineer         [hidden email] -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Fernand Vanrie
Frank , Marc

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....
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

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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
In reply to this post by frank.schoenheit
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
In reply to this post by Fernand Vanrie

> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Fernand Vanrie
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: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>    
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
> 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: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>>    
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>  
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Fernand Vanrie
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: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>>
>>>>    
>>>>        
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>  
>>>      
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>    
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

frank.schoenheit
In reply to this post by Marc Balmer-2
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         [hidden email] -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Balmer-2
Am 12.02.10 16:35, schrieb Frank Schoenheit, Sun Microsystems Germany:

> 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.

I already did that, I opened an issue (besides posting to list).  The
issue is assigned to Ocke, btw.

- Marc

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Santhoff
In reply to this post by Fernand Vanrie
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

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 ?

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: [hidden email]
> For additional commands, e-mail: [hidden email]
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Santhoff
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: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >  
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem: OpenOffice Base and PostgreSQL stored procedures

Marc Santhoff
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: [hidden email]
For additional commands, e-mail: [hidden email]

12