Apache OpenOffice Community Forum

(Well, at least it's resolved.)

Attempting to convert an existing Microsoft Access database to OpenOffice Base and also learn some SQL in the process, I have encountered a problem that I cannot solve with my very limited understanding, nor do I find the problem addressed under topics I can think of in this Forum. Condensed to its simplest form, here it is.

I have a table, Persons:

ID Person
1 Bill
2 Sue
3 Joe
4 Mary

In Access, the following query, qry_Backup_Persons, creates a copy of Persons, which it is supposed to.

But in Base, with the same table, the same query, has a syntax error: "Syntax error in SQL expression". No more detailed information is given. I have tried it with and without quotes and brackets around the table names and with and without the ';', all with the same result. What I am trying to do may be bad form, but I'm looking for a quick-and-dirty solution and the SELECT INTO approach would seem to provide it, if I could just get past the syntax error.

The problem occurs in both Ubuntu 8.04 and Windows XP versions of Base, using the HSQL database engine.

Is there a solution or a work-around or an alternate method to create a table from a query?

Does the status bar indicate HSQLDB?
http://hsqldb.org/doc/guide/ch09.html#insert-section If fields do not match exactly one to one:

Thanks for the quick reply!

Villeroy wrote:Does the status bar indicate HSQLDB?

Almost. It says "HSQL database engine". I assume that means the same thing.

...

Code: Select all

INSERT INTO "Persons_Backup" SELECT * FROM "Persons";

That produces the same "Syntax error in SQL expression." message. Ah, but now I see the [More] button! And that reveals more information on the error: I'm not much better off, though, for I see nothing about those "expected" keywords in the SELECT or INSERT command syntax in the Hsqldb User Guide, http://hsqldb.org/doc/guide/ch09.html.

What can I say? The following query finished succesfully after I copied the definition from "Dates" to a new table "Dates2": You can copy tables in the GUI. Just copy and paste the objects in the tables container or drag a table aside. A wizard pops up ...

Well, thanks for trying.

Summary: The syntax looks OK and it works in another's Base, but mine declares a syntax error.

Does anyone know what's going on here? Is this some kind of Base configuration problem? As I recall, I did a standard installation using Applications > Add/Remove ... Is it worth a try to remove Base, then re-install?

Ken33:

Your original question . . . where you said:

Ken33 wrote:SELECT *
INTO Persons_Backup
FROM Persons;

has a syntax error: "Syntax error in SQL expression".

The above, would be accurate ( error ), as you have entered it.

What I mean is, it WILL return an error, BUT, yes, it is 'easy' to correct.

Just for others reading this . . . one can 'easily' create a 'backup' table . . . or . . . part of a table . . . by using the SELECT FIELD_NAME INTO NEW_TABLE FROM OLD_TABLE syntax . . . when . . . the above is 'executed' from either, a macro, OR, the menu, Tools -> SQL...

The above is 'documented' in HSQL documentation at: http://www.hsqldb.org/doc/guide/ch09.ht ... ct-section .

BUT, I suspect, your 'problem' / 'solution' is this little 'footnote' in the documentation:

A unquoted identifier (name) starts with a letter and is followed by any number of ASCII letters or digits. When an SQL statement is issued, any lowercase characters in unquoted identifiers are converted to uppercase. Because of this, unquoted names are in fact ALL UPPERCASE when used in SQL statements.

What this means is . . . because your Table Name is defined as: Persons . . . ( mixed case, using both UPPER CASE and lower case letters ) . . . you MUST wrap your column and table names in double quotes.

So, by entering the command as: should work. You should see the message . . . Command successfully executed.

To view the 'new' table in the Table section . . . you will also have to issue the command . . . from the Menu: View -> Refresh Tables

I hope that is clear', and, it helps. Please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.

Thanks, Sliderule. works for me in Tools -> SQL..., but not in a query. If that's a hard limitation, then it would appear that Base (using the HSQL database engine, anyway) does not have the equivalent of a MS Access Make Table query. So to create a table under program control requires a macro. Is that right? Or is there another way to use a query to create a table?

Ken33:

I am not sure I understand your question.

Just to be clear ( or at least, as clear as possible ) . . . creating a new table . . . with the

SELECT FIELD_NAMES INTO NEW_TABLE FROM OLD_TABLE

does NOT produce a result set ( return of records to be displayed ) . . . ergo . . . it can ONLY be 'run' from either:

1. Tools -> SQL...
2. Macro

. . . but . . . NOT from the Query Window . . . since . . . the above is NOT a Query . . . that is . . . just READING a database. Rather, what you are doing is BOTH, reading, and, writing to the database ( changing the database -- adding a new table ) . . . NOT JUST a 'query'.

You said:

Ken33 wrote:So to create a table under program control requires a macro.

I do NOT understand what you are asking ( under program control ). This is OpenOffice Base, NOT MS Access. You can 'pass' an SQL command ( or multiple SQL commands ) 'directly' to the database engine . . . by issuing the SQL command(s) 'directly' via the Tools -> SQL... menu, OR, via a macro(s).

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

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.

Sliderule wrote:... what you are doing is BOTH, reading, and, writing to the database ( changing the database -- adding a new table ) . . . NOT JUST a 'query'.

So the problem lies not in Base, but in my misunderstanding of SQL and databases. I was trying to use a query to do something which a query should not be expected to do. OK, thanks for helping me learn that.

Sliderule wrote:

Ken53 wrote:So to create a table under program control requires a macro.

I do NOT understand what you are asking ( under program control ). ...

I would like to perform a sequence of actions on the database (e.g., query, create a new table, query, ...) automatically, i.e, without the user having to manually perform a sequence of menu selections or enter a sequence of commands. I believe that can be done with a macro. Now I have to learn more about macros ...

Thanks for your help!

Ken53

Running SQL by macro is not difficult. For casual use I would store the string somewhere and paste it into the "command line" (Tools>SQL...). A macro to run SQL that has been stored in a table would be nice.

Thanks, Villeroy and Sliderule. I think that I can make one of the alternatives you suggested work for me. This is just a prototype application and I will turn it over to someone who knows what they're doing for the operational version.

Ken