To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=110119 Issue #|110119 Summary|prompted parameters in queries fails when dragging que |ry from explorer window onto spreadsheet Component|Database access Version|OOO320m12 Platform|All URL| OS/Version|Windows XP Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|none Assigned to|dbaneedsconfirm Reported by|ianst
------- Additional comments from ia...@openoffice.org Sun Mar 14 19:55:24 +0000 2010 ------- Hi Not sure if this is actually a spreadsheet problem or whether it is database access specific nor have I been able to find an issue similar to this. The database is connected to using JDBC When running the following query from within BASE in "Create Query in Design View" OOo prompts for the parameters as expected and returns the result as expected - all is well. SELECT "AP_AR_ID", "DATE", "CHEQUE", "BANK", "AMOUNT", "VAT", "PERIOD" FROM "CHEQUES" WHERE "BANK" = :D_BankCode AND "PERIOD" >= :AStartPeriod AND "PERIOD" <= :BEndPeriod ORDER BY "PERIOD" ASC The BANK and PERIOD columns are of type CHARACTER,10 and the prompts are presented in sequence "alphabetically" i.e AStartPeriod BEndPeriod and D_BankCode, as desired. Now, in Calc, I open the Explorer window (Press F4) and click the same query by name - OOo prompts for all parameters and returns the result to the explorer "view" window - so far all is still fine. However, when dragging the query name from the Explorer window directly onto the spreadsheet the operation and results differ: 1. only the column names are loaded into the first row of the spreadsheet - no data. 2. The other difference is that the prompt parameters do not appear in the same sequence - ie. are not "sorted" into AStartPeriod BEndPeriod and D_BankCode - which leads to the question: <Does Calc use a different query tool or does it not set the properties of the query to the same values to access the database when dragging a query onto a spreadsheet vs when running the query into the explorer view window or from within BASE query designer ?> Changing the data types in the table to INT for either PERIOD and BANK also works perfectly either from query designer or into Explorer. However when dragging the query onto the spreadsheet results in the following error "Parameter marker 2 does not match expected data type" I then edited the query and replaced the D_BankCode prompt with the value 2 (INT not CHAR/Text), and then drag the query onto the spreadsheet and the result is loaded correctly - i.e there are only two prompted parameters Checking the table properties in BASE shows the columns to be defined as INTEGER and all values in the database for BANK and PERIOD are INTEGER. All hard-coded values in any query for any columns whether INTEGER or CHAR etc result in a successful query, so it seems something goes wrong when dragging a query onto the spreadsheet that has prompt parameters. If more details/examples are needed please let me know what you need. Many thanks for an extremely powerful tool in BASE Rgds Ian Stuart --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
