Sorting out SQL syntax in Base

[CEN]

I am very new to using Base. I have done quite a few databases in Access and using Access as a front end to Mysql. With the hope to move future projects to open source environments I started trying to use Base. The intent is to use base to create some documents automatically by merging base fields in Open Office documents. After getting part way through the project I discovered that Base on it's own is not a multi-user environment, which confused me quite a lot as I couldn't see the point of a database that wasn't a multi-user system ! So now to recover the work done so far the I have migrated the Base data to Mysql I am now struggling with what sql command Synax Base wants to see in the sql view in Base. In Access the Mysql command structure is entered and passed to Mysql , in Base this does not seem to be the same.

INSERT INTO tbl_Associatetemp ( ID_Student, ID_Subject )
SELECT tbl_Associate.ID_Student, ID_Subject
FROM tbl_Associate;

I get the following errors below regardless of what I enter.
In http://openoffice.org/downloads/documen ... %20OOo.pdf Page 126 there is an indication that double quotes are required , so I have tried that and the single quotes as well. There is some color coding in the SQL window , Blue for SQL commands , is green or brown shown for correct table and field syntax ?

How do you sort out what parts of SQL commands Base does not like ?

SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE


Regards

CEN

[r4zoli]

To understand better the situation, I try to clarify it.
When you use OOo Base UI services you must follow the built-in hsqldb SQL syntax, or it is a general SQL syntax supported by hsqldb and OOo.
In your case you connected to MySQL database, lot of cases the general/hsqldb SQL not works, you needs to run your queries in SQL direct mode activated (green tick in query window).
The OOo Base/hsqldb require "double_quote" for field names, and other database object, the MySQL uses ` instead of it.
If you use double quotes in SQL direct mode, the MySQL will throw an error.
OOo Base UI support SELECT clauses only, the INSERT, UPDATE, etc. works only in Tools>SQL... SQL command window.
The command window show you only the errors or success of your commands.
The errors you get so general, that could happen when you use something wrong, no special problem.

[CEN]

Thanks for the quick reply r4zoli
I did discover the run sql query directly mode and I ran a INSERT VALUE Query .

Which I tested in Mysql and ran OK . I have tried it in run direct mode with and without
double quotes .

So how does Base insert new records or make temporary tables ?

HSQLDB supports INSERT so I guess there is still lots of work to do on Base yet ?

INSERT INTO `tbl_Associatetemp` VALUES (0,14,14),(1,15,14),(2,16,14),(3,17,14),(4,18,14),(5,19,14),(6,20,14),(7,21,14),(8,22,14),(9,23,14),(10,24,14),(11,25,14),(12,26,17),(13,27,17),(14,28,17),(15,29,17),(16,30,17),(17,31,17),(18,32,17),(19,38,17),(20,14,18),(21,15,18),(22,16,18),(23,17,18),(24,18,18),(25,19,18),(26,20,18),(27,21,18),(28,22,18),(29,23,18),(30,24,18),(31,25,18),(32,14,19),(33,15,19);

Regards

CEN

[r4zoli]

So how does Base insert new records or make temporary tables ?

Base not a database application, it is an office application interface for databases.
No temporary tables support in Base.

HSQLDB supports INSERT so I guess there is still lots of work to do on Base yet ?

Only two developers working on this part of OOo, mostly bugfixes they could do, fine tunes happens and some new features introduces occasionally.

[CEN]

Thanks for all the time you spend responding to our Posts. I was expecting a replacement for Access in the same way the other Open Office components have worked for Office. I need to take a different approach for the data processing part of the project. Are there any examples to execute stored mysql scripts with Macro's via the mysql client ?
Thanks

CEN

[rudolfo]

Are there any examples to execute stored mysql scripts with Macro's via the mysql client ?

The macro processing in Base uses UNO objects as all other OpenOffice applications. Various scripting languages can be used to drive this object processing: Basic, Python, Java, Javascript and with some limitations VBScript.
Look in the macro section of this forum for sample code, "createStatement" or "prepareStatement" are good search words.
In general there is also a callableStatement, which allows you to call stored procedures in the database engine. Haven't seen too many examples for it, so I guess it is not so often used, though the way to code with it is only slightly different from prepareStatement.
But maybe you don't mean stored procedures if you mention "stored sql scripts" ... By concept you can also call external processes in all of the above macro languages. But the problem with that is you can easily feed parameters on the input side, but the best you can get is a single return code from the executed process. In other words that's nothing for "select" statement, update and insert won't tell you how many rows have been affected.

Serious work in Base is meant to be done in Forms. Forms can display the relational data in several database tables in a way that is appropriate for the preferred workflow. A form is basically a Writer document with controls (Checkboxes, input fields, list boxes, ...) that are organised in forms and subform containers. The naming is unfortunate here, it is "form" in both cases but one refers to the document or the visual represenation and the other one is rather something abstract defining a interface to retrieve an write records to a database table. The form navigator is of great help here.
If this is all new for you check out the tutorial section for Base related tutorials. They contain a lot valuable data on how to use forms. Building your logic in forms is mostly a question how to organize it into the right combination of forms and subforms. Only in few cases you should fall back to macros. The answer on "I need this functionality" is not always starting to program a macro function for this feature but often just to find the right queries.