Importing data from MySQL

[pierrick]

Hi all,

Just installed OOO 2.4. Aim of the game: encouraging one of my users who wishes to go purchase that other well-known database application to forget about it and move to OOO. To convince him I need to be able do the following (at least):
1. Create tables using straightforward SQL statements,
2. Import data from MySQL,
3. Create queries,
4. Create reports and,
5. Automate the whole process with code/macros.

Why?
Item 1: for time and data integrity's sake of course. How might one create and run a query in OOO that starts with: CREATE TABLE...
Item 2: Similar problem. Exporting as cvs from MySQL isn't as reliable as one expects, so again best use a SQL statement. How might one create and run a query in OOO that starts with: INSERT INTO...
Otherwise I think I've pretty much sussed out the basics of items 3 and 4. No idea yet about item 5...

NOTE: MySQL is NOT installed on the machine where OOO is installed (the data resides on a web server), so I don't think linking is an option (or is it?)

Thanks for your help.

[Villeroy]

...encouraging one of my users who wishes to go purchase that other well-known database application to forget about it and move to OOO...

You encourage "your users" to use something that you don't know? What tells you that Base is "better" than a mature database server such as MySQL? Better for what? Base is just a frontend application to import several file based tables and database servers as well. As an extra feature there is a built-in database engine which can be used to create small databases in a single file (lousy performance, no privileges, no network, no driver for third-party applications).

[pierrick]

Your message does not answer my questions and assumes incorrectly that the "other well-known database application" I mention is MySQL.
Please re-read my message and if you have anything positive to contribute I'll be happy to read it.

[Sliderule]

pierrick:

Welcome to the world ( or is that galaxy ) of OpenOffice Base.

You have asked about "create table" . . . I will assume . . . your intention is to use the "embedded database" . . . HSQL in OpenOffice.

Documentation on HSQL can be found at:

http://www.hsqldb.org/doc/guide/ch09.html

Now, there are several ways to issue a SQL ( Structured Query Language ) commands to create a table. Including:

1. From within OpenOffice
   1. Create a database
   2. From the Menu: Tools -> SQL...
   3. In the Command to execute box, enter your SQL statements, each terminated by a semicolon . . . for example:
      

      Code: Select all

      CREATE CACHED TABLE "SUPPORT"("LAST_NAME" VARCHAR_IGNORECASE(15) NOT NULL,"FIRST_NAME" VARCHAR_IGNORECASE(10) NOT NULL,"ADDRESS" VARCHAR_IGNORECASE(25),"CITY" VARCHAR_IGNORECASE(13),"ST" VARCHAR(2),"ZIP" VARCHAR(5),"PHONE" VARCHAR(14),EMAIL VARCHAR_IGNORECASE(40),PRIMARY KEY("LAST_NAME","FIRST_NAME"));
      
      CREATE CACHED TABLE "TEST_30"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"MY_DATE" DATE DEFAULT CURRENT_DATE NOT NULL,"ID_REF" VARCHAR(50));
      

   4. Press the Execute button
2. Write one or more macros . . . to execute SQL statements.

Just a few suggestions . . . with your permission. According to HSQL documentation

name

The character set for quoted identifiers (names) in HSQLDB is Unicode.

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. An important implication of this is the for accessing columns names via JDBC DatabaseMetaData: the internal form, which is the ALL UPPERCASE must be used if the column name was not quoted in the CREATE TABLE statement.

Therefore, I prefer ( YMMV - Your Mileage/Database May Vary ) to 'define' my Tables /Fields ( Columns ) / Views with all UPPERCASE names.

Additionally, according to HSQL documentation:

VARCHAR_IGNORECASE is a special case-insensitive type of VARCHAR.

I prefer to use VARCHAR_IGNORECASE . . . so . . . in a Query . . . the text result will be found . . . regardless of case ( UPPER / lower ). For example: if a table contains a field "COUNTRY" that is populated with a value of: Canada . . . if . . . in the field is defined as . . . "COUNTRY" VARCHAR(50) . . . the following Query:

Code: Select all

Select * from "MY_TABLE" WHERE "COUNTRY" = 'CANADA'

would NOT find the record ( Canada not the same as CANADA ) . . . whereas, if in the field is defined as "COUNTRY" VARCHAR_IGNORECASE(50), the above Query would find the record.

Likewise, INSERT statements may be entered in the same location as above.

I hope this helps, and, gives you a place to start.

Sliderule

[pierrick]

Hi Sliderule,

Thanks heaps for your answers. I was away on a trip to Paris hence no answers for a while...

Welcome to the world ( or is that galaxy ) of OpenOffice Base.

Thanks.

You have asked about "create table" . . . I will assume . . . your intention is to use the "embedded database" . . . HSQL in OpenOffice.

You assume correctly.

Documentation on HSQL can be found at ...

Yes, good on you for reminding me. I should really have started there.

Tools -> SQL...

Yep, that's what I was looking for. In fact it worked well, especially after I found the View -> Refresh Tables.

Just a few suggestions . . . with your permission...

I will remember the uppercase column names.
And thanks muchly for the VARCHAR_IGNORECASE column type. This could have caused me problems later.

Now I'll go spend some time with the documentation.

[DrewJensen]

NOTE: MySQL is NOT installed on the machine where OOO is installed (the data resides on a web server), so I don't think linking is an option (or is it?)

Just because it is on a web server doesn't mean you can't connect to the MySQL server - that depends on your network setup. If you/they have admin rights on both machines ( the MySQL machine and the users machine ) then sure you can connect - not using the http protocol of course, but via TCP/IP. If the MySQL server is at a hosted site, chances are you will not be able to do this.

[pierrick]

Hi DrewJensen,

If the MySQL server is at a hosted site, chances are you will not be able to do this.

And it is. At least as far as this particular user is concerned.

However I do have MySQL running on my development machine so thanks for the tips on connecting OOO Base with MySQL.

[DrewJensen]

OK - you may be interested to know then that the MySQL folks are working on a native connector for Base - no need for ODBC or JDBC then...it has been released for an early beta ( alpha might be more accurate) testing. It is faster then the JDBC connector for sure. Not totally sure, but I believe the target time frame is to have it ready for the OO.o 3.0 release.

One other item - if your user is going to use CSV to transfer any data be sure to look at the support TEXT tables in HSQLdb - takes a little setup, but for recurring data transfers it works quite nicely.

[pierrick]

Hi DrewJensen,

Thanks for all this info.
I do not plan on using CSV transfers from MySQL. I prefer using a straight export from MySQL (data only.) It provides me with all the INSERT... statements, nice clean SQL which I can then run in the Tools -> SQL... menu option.
What I need now is automate the whole thing... Easy on the MySQL side. A whole new world on the Base side. I guess macros are the way to go.