[Solved] Base SQL not working with MySQL backend

[stever92]

Hi,

I'm trying to build a database of student assessment records over a number of years so we can analyse growth over time. I've set up four tables; one for the teachers, one for the students, one to identify a data collection period (i.e. Feb 2013, May 2013 etc) and then the main data storage table which is the data we collect. I've populated each of the tables with some real data by importing from my spreadsheets used in previous years.

I setup MySQL on my Mac laptop and then installed OO 4.01 to use as a frontend, on the basis that I once created some relational databases in MS Office Access and thought it would improve my learning curve - but it hasn't really helped much.

I want to create a main form based on the student data table where I can bring up the data associated with a specific child for a given teacher and datapoint. The PK for the main data table is a composite key based on the student's ID code, teacher's ID code and the appropriate datapoint.

I've spent literally days studying this forum and a heap of other resources on-line to get to the point. Looking at several example databases showed me that I should be able to use a couple of ListBoxes to select the kid, teacher and datapoint I want using actual names from the simple tables rather than the meaningless id codes. So far so good.

I hit my first really big problem trying to concatenate fields to display in the list box. I am able to use a simple select query and insert, say, the teacher's last name into the list box but as soon as I try to use double pipes to join two fields together with an alias, the query just produces a 0 instead of the first_name, last_name combination I want. I've tried simplifying the query by just using the double pipes to add a comma after the first_name but it still produces a 0.

I eventually thought that it could be an incompatibility between the oobase SQL and the MySQL version when I discovered that MySQL uses the syntax CONCATENATE(field1,field2) etc. so I tried using the MySQL code but that just threw an instant error (I think you people refer to that as a parsing error?)

I've searched the forums and the wider web but I can't find any clues so I joined up to the forum and posted this.

Can anyone help?

Incidentally I've also been confused by the way my control properties and SQL strings are different from the ones in the example dbs. Some of my properties seem to have many more boxes, and the SQL strings have additional identifiers in the field names etc. I think it may just be because the examples all have embedded dbs and I'm using an external data source. Can anyone confirm that?

[stever92]

Oops - a small error I just noticed. The PK is a composite only of the student ID and the datapoint ID - but I do want to be able select a subset of data for all the children for whom they are responsible i.e. their class. The teacher ID is a FK in the student data table.

[Sliderule]

I would like to help . . . and . . . I do have a version of a HSQL database, MySQL database, H2 database and SQLite database running on my computer, using OpenOffice Base ( *.odb ) as the database front-end.

You said:

I eventually thought that it could be an incompatibility between the oobase SQL and the MySQL version when I discovered that MySQL uses the syntax CONCATENATE(field1,filed2) etc. so I tried using the MySQL code but that just threw an instant error (I think you people refer to that as a parsing error?)

Important note The function syntax for both MySQL and HSQL is NOT

Code: Select all

 CONCATENATE(field1,filed2)

but rather

Code: Select all

 CONCAT(field1,filed2)

NOTE the function name is CONCAT and I will assume you meant field2

For example, using a table by the name of `MyTable`, and, fields `FirstName` `LastName` and `PK_Field`:

Code: Select all

SELECT 
   `MyTable`.*, 
   CONCAT( `MyTable`.`FirstName`, ' ', `MyTable`.`LastName`, ' ', `MyTable`.`PK_Field` ) AS `Concat`
FROM `MyTable` AS `MyTable` 
WHERE `MyTable`.`TEST_DATE` BETWEEN {D '1999-12-28' } AND {D '2000-01-05' }

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has been resolved.

[stever92]

Thanks Sliderule, I just tested your amendment and you are quite correct that I had used CONCATENATE, not CONCAT. I just edited my test control and it worked as expected.

I'll mark the question as solved but I would like to know if this will always be necessary, i.e. that I will need to use MySQL syntax rather than the HSQL version. All the references I've found so far list ONLY the double pipes (||) method of combining fields. Is there a document you can recommend that lists the variations amongst the various 'flavours' of SQL, or which will guide me in choosing the correct form for my situation (MySQL backend, OOBase fronted)?

I have found http://www.w3schools.com/SQl/default.asp to be very helpful so far.

[stever92]

I've marked this solved because my main issue (well, this one anyway) has been solved but I would still appreciate an answer to the question in the final paragraph:

Incidentally I've also been confused by the way my control properties and SQL strings are different from the ones in the example dbs. Some of my properties seem to have many more boxes, and the SQL strings have additional identifiers in the field names etc. I think it may just be because the examples all have embedded dbs and I'm using an external data source. Can anyone confirm that?

TIA, Steve

[Sliderule]

Steve:

Just to quickly explain, OpenOffice / LibreOffice Base . . . is NOT a database.

OpenOffice / LibreOffice Base and the file ( *.odb ) is the FRONT-END to the database backend you elect to use. Most here, use, HSQL as the database back-end. Others elect to use, a newer and much more robust version of HSQL ( as of the date I am writing this, HSQL Version 2.3.2 rather than the HSQL included in the OpenOffice / LibreOffice download of HSQL Version 1.8.0.10 ).

Still others, like yourself, can elect to use database back-ends, such as, MySQL, or, Microsoft Access, or, H2, or SQLite, or, PostGRE, or, Firebird, etc . . . and . . . the choice is yours. Since OpenOffice / LibreOffice Base will SEND the Structured Query Language ( SQL ) statements to the data-base back-end of your choice . . . YES . . . the syntax and functions available is dependent on which database back-end you use.

As an additional note . . . per MySQL documentations found at http://dev.mysql.com/doc/refman/5.1/en/ ... -ansi.html , yes, I added the colour only blue and red for emphasis, but, NOT the words :

MySQL Server understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL Server, || and OR are synonyms, as are && and AND [1235]. Because of this nice syntax, MySQL Server doesn't support the standard SQL || operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it is easy to convert use of the || operator to MySQL Server.

I hope that is clear . . . well . . . at least as clear as mud.

Welcome to the forum.

Sliderule

[DACM]

Forgive me here, but this thread highlights some of the criticisms surrounding SQL and perhaps MySQL in particular, among the options in a typical Base/AOO/LibO environment. There's no getting around the fact that ANSI/ISO SQL is the standard and the defacto language of relational databases. The viability of SQL, itself, is not in question here. The problem is that "SQL implementations are incompatible between vendors." The reasons (excuses) vary but SQL standards change over time and vendors are reluctant to forgo backwards compatibility with their existing customer base, while market forces provide a strong incentive to trap "customer loyalty" with propriety. Database professionals would further argue that proprietary extensions beyond the SQL standards are necessary for their daily operations, although Base users will rarely (if ever) benefit from such extensions.

I mention this incompatibility because MySQL is particularly proprietary among popular Open Source and Commercial relational-database solutions. In contrast, HSQLDB, H2 and PostgreSQL not only offer the best (free) Open Source licensing, but they perhaps uniquely strive to adhere to the SQL standards -- rendering them easier to learn, more modern, and more portable should migration ever become necessary. Many web sites provide SQL tutorials while serving as references for standard SQL. I find myself particularly attracted to the interactive "SQL Grammar" for H2, even though H2's SQL dialect is not always 100% compatible with HSLQDB. And we now have modern tutorial-sites, such as SQLZOO, that allow us to select the reference engine for interactive use (typically select PostgreSQL, or perhaps Oracle for relatively standard SQL references on these sites). So in light of interactive SQL reference sites such as SQLZOO, variations among SQL dialects shouldn't pose a problem for Base users, even when using MySQL, perhaps particularly when utilizing "direct SQL" to bypass the Base Query Parser/Analyzer.

While it's entirely up to you, I always question the use of anything beyond HSQLDB 2.x for new database projects with Base. H2 is wonderful, but HSQLDB 2.x begins to distance itself from H2 with support for SQL stored procedures and triggers. HSQLDB also enjoys the greatest expertise and wide-spread experience among the Base user-community. And with the advent of portable 'split HSQL database' templates utilizing the latest HSQLDB 2.x engine, the user-community has effectively eliminated the need for a default database engine and associated "embedded databases" in Base. Leveraging only the JDBC support in Base, our templates provide one of the fastest and most flexible desktop database solutions available today, while scaling with relative ease in support of multiple users (~50+). HSQL databases are cross-platform, cloud/USB portable, tiny footprint (single 2MB file), zero-admin, and no-installation required for single-user file-mode operation with Base (JRE required). You can even run the entire database application, including LibreOffice Portable and Java Portable from a Dropbox or other cloud-sync'd folder. So when creating a new database in a typical Base/AOO/LibO user environment, there's rarely a valid reason to look beyond the flexibility and power of HSQLDB. Obviously, if you're simply connecting to an existing database, then you really don't have a choice. But in my five years on this forum, I've only encountered one user that actually required another engine (for ancient date analysis), and he wisely chose PostgreSQL. I understand that initial table development is easier with more advanced database management tools such as phpMyAdmin for MySQL. But Java databases also enjoy a plethora of advanced database managers, beyond Base, and even without typing SQL commands. In any case, once the tables are setup, you'll be leaving these database managers behind as Base becomes the primary front-end tool for data-input, query and reporting purposes.

Of course, I'm concentrating on Open Source database engines here for use in typical Base environments. If my sources are correct, big-budget enterprises look to Oracle, MS SQL Server and DB2, while bypassing equivalent Open Source solutions largely because they require someone to sue (litigate) when things go wrong.

See also:
Which Would Make for a Better Back-End, MySQL or HSQLDB?
Choose your own Back-End Database for Base
...

[Steve R.]

I ran into this (Base/MySQL) issue today and was able to solve it.
But, for me - as I researched the issue, there proved to be an additional tweek; modification of the my.cnf file.

I needed to add the following line to my my.cnf file to get the Base code below to work.

Code: Select all

# Set SQL-Mode for various options - Added 5/7/2014 to allow pipes for BasE
# http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_ansi
sql-mode        = "ANSI"

Below is the code in my Base program. See Revision below.

Code: Select all

SELECT "ArtistIDnum", CONCAT( "tblCoverArtistList"."ArtistFirst", '  ', "tblCoverArtistList"."ArtistLast" ) AS "ArtistName", "ArtistLast", "ArtistFirst" FROM "TEST_sfmags"."tblCoverArtistList" AS "tblCoverArtistList" ORDER BY "ArtistLast" ASC, "ArtistFirst" ASC

ADDENDUM: While the code above tested correctly and appeared correct when executed directly as an SQL query, it did not appear correct in the field located on the Base form. Surprise. The following solution came from page 115 of Mariano Casanova's Base Tutorial From Newbie to Advocate in a One, Two ... Three!. The Base form field now correctly has a space between the first and last names.

Code: Select all

SELECT "ArtistIDnum", CONCAT( CONCAT("tblCoverArtistList"."ArtistFirst", '  '), "tblCoverArtistList"."ArtistLast" ) AS "ArtistName", "ArtistLast", "ArtistFirst" FROM "TEST_sfmags"."tblCoverArtistList" AS "tblCoverArtistList" ORDER BY "ArtistLast" ASC, "ArtistFirst" ASC

Setting the SQL Mode

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

A list of the sql-modes is located further down the page on the MySQL documentation.

PS: Testing pipes did not work, as previously mentioned.