[dba-dev] Re: i108377 and SQLite ( Re: Anyone willing to give this function a try and let me know)

[Drew Jensen]

And attached is the simplest most generic procedure I could come up with.

So this routine just creates an SQL INSERT STATEMENT

No prepared statement.
No support for IMages or other varbinary data.
Everything is a string.
No use of a cataloge (schema) name

Ran it against a few forms I had for each
HSQLdb, MS Access, MySQL Native, ODBC/SQLite3

Note - I gave up trying to force a commit of the GUI controls to the 
bound data columns.

To SLOW...

Drew


'Copyright (C) 2010  Andrew 'Drew' Jensen
'atjen...@openoffice.org
'
'This program is free software: you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published by
'the Free Software Foundation, either version 3 of the License, or
'(at your option) any later version.
'
'This program is distributed in the hope that it will be useful,
'but WITHOUT ANY WARRANTY; without even the implied warranty of
'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
'GNU General Public License for more details.
'
'To review a full a copy of the GNU General Public License
'please see, .
'
'// 
'// The library module was intended to be used as a aid in 
'// working around an anomily in the 3.2 release of OpenOffice.org Base
'// 
'// For reference see:
'// http://www.openoffice.org/issues/show_bug.cgi?id=108377
'// 
'// The library includes 4 rooutines for use in these
'// Insert Data Only forms
'// 
'// Two are suitable for use from menu, toolbar
'// or dialog displayed by data entry form
'// 
'// insertdFistDataForm 
'// reloadFistDataForm
'//
'// Two when called from a button owned by the dataform
'// that owns the controls to write to the database
'//
'// insertThisDataForm
'// reloadThisDataForm  

option explicit 

'// GLOBALS
'// 
GLOBAL BoundFields as variant


'// 
'// reloadFistDataForm
'// 
'// can be called from menu or toolbar or HotKey
'// can be called from dialog displayed by data entry form
'// 
'// will clear all controls of the first dataform 
'// on the top most base form window
'// 
sub reloadFistDataForm()
'// 
'// only want to work with forms
'// 
'// a Query/dataview window is
'// ActiveFrame = NULL
'// 
if not isNull( 
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model ) then
'// 
'// Report Builder editor
'// 
if not 
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.supportsService("com.sun.star.sdb.ReportDesign")
 then
'// ASSUME - ASSUME - ASSUME
'// this is a form
'// 

thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0).reload

end if

end if

end sub 

'// 
'// insertFirstDataForm
'// 
'// can be called from menu or toolbar or Kotkey
'// can be called from dialog displayed by data entry form
'// 
'// will write the values from the bound data columns 
controls
'// of the first dataform on the top most base form window
'// 
sub insertFirstDataForm()
'// 
'// only want to work with forms
'// 
if not isNull( 
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model ) then
'// 
'// Report Builder editor
'// 
if not 
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.supportsService("com.sun.star.sdb.ReportDesign")
 then
'// 
'// ASSUME - ASSUME - ASSUME
'// this is a form
'// 
InsertDataForm( 
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0)
 )   
end if

end if

end sub 

'// 
'// reloadThisDataForm
'// 
'// can be called from a button t

Re: [dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures

[Marc Balmer]

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  WHERE 0 = 1
>>
>> This will of course never return any data, but it will raise an error if
>>  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

[dba-dev] i108377 and SQLite ( Re: Anyone willing to give this function a try and let me know)

[Drew Jensen]

Hwody,

Alright - don't say it - I tossed pretty much all of that.

*IMPORTANT *

Unlike the jdbc/access drivers the odbc(all?)/SQLite3 combination is the 
worse possible situation.


The user has a Data Inert Only form.
They try to commit it throws the error.
(Under JDBC/Access only the first try writes data to the table)
With SQLite each time the user commits a new record is inserted 
(assuming an Auto_increment PK)

then tells the user there was an error - leaving the GUI ready to save agin.
Do that 10 times you have 10 records of identical data, less the unique 
key in your table.

*lovely*
WE really need to let this, small group, of users know in advance of 
this bug.


So back to coding

I was hoping that I could, as a work around, just do something like this:

dataform.Cycle = com.sun.star.form.TabulatorCycle.PAGE
dataform.InsertRow
dataform.Cycle = com.sun.star.form.TabulatorCycle.CURRENT
dataform.Reload

- nope that doesn't work the error still triggers.

I've got the code rewitten, using prepStmt / Statement (for SQLite) 
without the case statements and a lot more smarts for passes across the 
controls
- the performance is a lot better but still not good enough for more 
then maybe 20 controls.


Latter today I'll put that up here...just once this time - sorry for the 
flurry the other day.


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

[Josh Berkus]

> 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

[Fernand Vanrie]

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  WHERE 0 = 1

This will of course never return any data, but it will raise an error if
 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

[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 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  WHERE 0 = 1

This will of course never return any data, but it will raise an error if
 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

[dba-dev] Problem: OpenOffice Base and PostgreSQL stored procedures

[Marc Balmer]

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  WHERE 0 = 1

This will of course never return any data, but it will raise an error if
 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