Query on an MySQL database

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
johnbarns
Posts: 4
Joined: Wed Jun 24, 2015 4:44 pm

Query on an MySQL database

Post by johnbarns »

I have created a query in design view and the sql is:

Code: Select all

SELECT
 `receipts`.`DATENEW`,
 `products`.`REFERENCE`,
 `products`.`NAME`,
 `ticketlines`.`UNITS`,
 `products`.`PRICESELL` FROM `epos`.`ticketlines` AS `ticketlines`,
 `epos`.`products` AS `products`, `epos`.`tickets` AS `tickets`,
 `epos`.`receipts` AS `receipts`, `epos`.`categories` AS `categories`,
 `Madison ID` WHERE `ticketlines`.`PRODUCT` = `products`.`ID` 
	AND `ticketlines`.`TICKET` = `tickets`.`ID` 
	AND `tickets`.`ID` = `receipts`.`ID` 
	AND `products`.`CATEGORY` = `categories`.`ID` 
	AND `categories`.`PARENTID` = `Madison ID`.`ID` 
	AND `receipts`.`DATENEW` BETWEEN {D '2015-06-15' } AND {D '2015-06-21' }
the above works fine but I want to be prompted for the two dates used in BETWEEN Have tried:

Code: Select all

`DATENEW` BETWEEN :enter_date_from AND :enter_date_to
Get Error:

SQL Status: S1009

Parameter index out of range (1 > number of parameters, which is 0).

Can anyone help?
Last edited by RoryOF on Wed Jun 24, 2015 6:05 pm, edited 1 time in total.
Reason: Added [code] tags. [RoryOF, Moderator]
OpenOffice 4.1.1 running on Windows 8.1

Cheers
John
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Query on an MySQL database

Post by Sliderule »

John:

Welcome to the OpenOffice / LibreOffice Base forum.

From what you have written . . . yes, it should work. Put another way, when I use MySQL, with a JDBC connection . . . in my data base when using a DATE column, with a BETWEEN clause, it does work.

But, just to explain something, to be sure it is clear ( well, at least as clear as mud :crazy: ) . . . what is happening behind the scenes is, Base . . . your database front-end - and MySQL is your database back-end . . . Base FIRST parse / read the Select statement and make some changes to it . . . including, adjusting dates and time to the correct format ( just as you wrote . . . {D '2015-06-15'} . . . that is . . . in YYYY-MM_DD format ).

IMPORTANT NOTE: Since, you want to prompt the user to enter the two values . . . the Base Parser must be enabled. You can turn on or off the Base Parser from either :
  1. The toolbar, pressing the SQL icon
  2. From the Menu: Edit -> Run SQL command directly
So, is it possible, you are passing the Query directly to the database back-end, rather than allowing the Base Parser first 'convert' the Query with the user defined values?

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
johnbarns
Posts: 4
Joined: Wed Jun 24, 2015 4:44 pm

Re: Query on an MySQL database

Post by johnbarns »

Sorry to take so long replying but had a bit of an emergency and could not get to the PC with database on.

I'm also sorry to be one of the dangerous ones, little knowledge big ambitions :D

Thanks for reply but when I toggle SQL Icon with sql below i get a new error.

The data content could not be loaded.

SQL Status: 42000
Error code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':enter_date_from AND :enter_date_to' at line 1

with the SQL button toggled back and copying dates directly from database table using mysql workbench and pasting in exact format I still get first error

Parameter index out of range (1 > number of parameters, which is 0).
OpenOffice 4.1.1 running on Windows 8.1

Cheers
John
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Query on an MySQL database

Post by Sliderule »

John:

Please, can you paste ( re-paste ) the enter SQL you are using, when an error occurs. That is, I would like to see EXACTLY what you have before press the run icon. The exact reason is to ensure, no unexpected single quotes are present. :crazy:

Also, please confirm for me ( and others reading this ) how you are connected to MySQL . . . that is . . . ¿ a JDBC connection, ODBC connection, other ?

Sliderule
johnbarns
Posts: 4
Joined: Wed Jun 24, 2015 4:44 pm

Re: Query on an MySQL database

Post by johnbarns »

Thanks for your replys sory to take sooo long I have had a bit of a rough time lately not been online for ages

Connection Type JDBC

Working with dates edited in Query:

SELECT `products`.`REFERENCE`, `products`.`NAME`, COUNT( `ticketlines`.`UNITS` ), `PRICESELL` + ( `PRICESELL` * .2 ) AS `Inc VAT` FROM `epos`.`ticketlines` AS `ticketlines`, `epos`.`products` AS `products`, `epos`.`tickets` AS `tickets`, `epos`.`receipts` AS `receipts`, `epos`.`categories` AS `categories`, `Sportline ID` WHERE `ticketlines`.`PRODUCT` = `products`.`ID` AND `ticketlines`.`TICKET` = `tickets`.`ID` AND `tickets`.`ID` = `receipts`.`ID` AND `products`.`CATEGORY` = `categories`.`ID` AND `categories`.`PARENTID` = `Sportline ID`.`ID` AND `receipts`.`DATENEW` BETWEEN {D '2015-07-06' } AND {D '2015-07-13' } GROUP BY `products`.`REFERENCE`

Not Working:

SELECT `products`.`REFERENCE`, `products`.`NAME`, COUNT( `ticketlines`.`UNITS` ), `PRICESELL` + ( `PRICESELL` * .2 ) AS `Inc VAT` FROM `epos`.`ticketlines` AS `ticketlines`, `epos`.`products` AS `products`, `epos`.`tickets` AS `tickets`, `epos`.`receipts` AS `receipts`, `epos`.`categories` AS `categories`, `Sportline ID` WHERE `ticketlines`.`PRODUCT` = `products`.`ID` AND `ticketlines`.`TICKET` = `tickets`.`ID` AND `tickets`.`ID` = `receipts`.`ID` AND `products`.`CATEGORY` = `categories`.`ID` AND `categories`.`PARENTID` = `Sportline ID`.`ID` AND `receipts`.`DATENEW` BETWEEN :enter_date_from AND :enter_date_to GROUP BY `products`.`REFERENCE`


Been trying to resolve again today no luck
OpenOffice 4.1.1 running on Windows 8.1

Cheers
John
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Query on an MySQL database

Post by MTP »

It sounds like if you turn on the Base parser (necessary to prompt for user input) you get a syntax error (due to the parser expecting syntax for HSQLDB) and if you turn off the parser to send the MySQL syntax directly to the MySQL database, then the user input prompts won't work. Is that correct?

It may be better to have a form where the user enters dates into a filter table (make the form source [SELECT * FROM "Filter" WHERE "FilterID" = 0] to force the table to always stay just one row) and have the query check the filter table for the appropriate dates.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
johnbarns
Posts: 4
Joined: Wed Jun 24, 2015 4:44 pm

Re: Query on an MySQL database

Post by johnbarns »

Thanks MTP finaly managed to get sorted using your method but its not exactly as I would like, as still cant get prompt for dates have to open form first.
OpenOffice 4.1.1 running on Windows 8.1

Cheers
John
Post Reply