[Solved] Base sql dump add semi-colons ;

[chenier]

I am in process of migrating my accounting database from hsqldb embedded to a MySQL backend. (decided to do this based on recent data loss with embedded)

I have created a .sql dump file from Base, but am unable to insert multiple data lines at once because base did not put semi-colons at the end of each INSERT INTO statement.
Are there ways to modify the .sql dump file coming out of Base so it places a semi-colon after each statement?

I could manually place semi-colons after each statement, but for my primary table, which has over 10,000 records, not easily done and prone to data loss due to typing errors.
I have not yet discovered a flag in MySQL which would allow it to accept multiple statements without semi-colons, but am still looking

I have already:
Created new Base .odb file with native MySQL connection to my new MySQL database.
Changed MySQL to accept ANSI text, so double quotes around table and field values are accpted.
Successfully imported each table structure into MySQL by copying CREATE TABLE statements from the .sql dump file
Learned the changes needed in the CREATE TABLE statements for them to work. (remove CACHED, translate unsupported Data types to SQL supported)
Imported single lines of data into MySQL successfully... also multiple lines if I manually put a semi-colon after each INSERT INTO statement
Learned alternate ways of importing data, which I may need to revert to.... Namely Copy old database table into a spreadsheet (f4 from spreadsheet), then copy and paste, followed by copying, and selecting paste onto new MySQL table in Base table browser area.
Learned that dragging tables from old database to new MySQL connected one does not work (current reported bug) (-- it appears to meit tries to paste table outside of any schema rather than within a MySQL database.)

[Villeroy]

Get some decent text editor and append a semicolon to each line.

In Writer you do it like this:
Edit>Find&Replace
[More Options]
[X] Regular expressions

Search: $
Replace: ;\n
[Replace All]

[DACM]

Get some decent text editor and append a semicolon to each line.

Wow. I just had visions of multi-colored keyboard overlays associated with a bygone era of word processing (aka Word Perfect for DOS). I see the 'List of Regular Expressions' in the Help section, but I'm surprised that OOo/AOO/LO doesn't offer an equivalent 'Special' list for quick selection of these cryptic "expressions" such as MS Word provides in its own Search&Replace dialog. But that's not really my point here...

This use of 'Regular Expressions' works fine in OOo 3.3 and LO 3.5, but AOO 3.4 trashes the results in my experiments.

Anyone else seeing this bug?

[DACM]

I am in process of migrating my accounting database from hsqldb embedded to a MySQL backend. (decided to do this based on recent data loss with embedded)

By the way, you may be concluding that data migration is necessary based on our imprecise use of terms. The problem is not with HSQLDB embedded-mode but with the Base "embedded database" file format. I understand the confusion as we throw around the term "embedded" but don't misconstrue the issue. MySQL is a great database now that it uses the InnoDB engine (transactional! ) by default. But when you compare HSQLDB to MySQL side-by-side in Base applications, there are some distinct advantages to HSQLDB. If you have other reasons to use MySQL then by all means adopt it. But you're tedious data-migration process and now daily MySQL management (start/stop) tasks are not necessary to solve the issue. You could simply port your current database (without data migration) to a proper/stable configuration with Base in order to stop Base from corrupting your data. And you wouldn't lose the seamless nature (automatic database engine management) of your application. If you haven't read the "avoid corruption" link in my signature below closely, it might shed some light on your options.

[RoryOF]

The Regular Expression syntax was changed for AOO 3.4; hence the trashing you experienced. I think they decided to use a different Regular Expression engine, for copyright compliance reasons

[Villeroy]

In AOO they changed the whole regex engine for the better. No, my AOO does not crash. It inserts an additional new line which should not hurt in SQL context.
Replacing regex $ with ; works as expected.
The ^ and $ match the position at the beginning or the end of a line respectively. $ is not supposed to match the control character which constitutes the end of a line (at least that's what I recall from my old camel book).

[acknak]

I ... am unable to insert multiple data lines at once because base did not put semi-colons at the end of each INSERT INTO statement. ...

Sorry, but does this really matter?

Surely MySQL has some way to take its commands from a file, or redirected from a file, in which case you don't need semicolons--each separate statement is complete in itself.

[Villeroy]

I ... am unable to insert multiple data lines at once because base did not put semi-colons at the end of each INSERT INTO statement. ...

Sorry, but does this really matter?

http://dev.mysql.com/doc/refman/5.1/en/ ... eries.html

A command normally consists of an SQL statement followed by a semicolon. (There are some exceptions where a semicolon may be omitted. QUIT, mentioned earlier, is one of them. We'll get to others later.)

[DACM]

I think there's some issues with the AOO implementation. Note '5' replacements in AOO versus '8' replacements in LO/OOo.

[Villeroy]

I think, the additional line break is correct regex processing. $ matches the position at the end of the line (which is not the line feed character) and the replacement inserts a semicolon and a line feed at that position. Replace with a semicolon only and you get the same result as with the old regex implementation which is still used in LibreOffice.

[DACM]

I don't know. Maybe I'll try a re-install but it looks buggy...now only '4' replacements:

[acknak]

In OOo, there is no character that marks the end of a paragraph, so you can neither match it with anything, nor replace it with anything.

There is a character that produces a line break (what you get with Insert > Manual Break > Line Break, or Shift+Enter). You can match the line break character with \n in the pattern (or \x0a), you can only insert it if your system allows you to enter a literal line feed character in the Replace With box.

That is all the same between OOo 3.3 and 3.4. What has changed is that OOo used to handle a couple of search patterns specially, but AOO 3.4 no longer does this. There are some tasks that you could accomplish by find/replace in 3.3 that are no longer possible in AOO 3.4.

[chenier]

You guys are fast!
Great solution... all the INSERT INTO satements always end with ")" end parentheses, of course, and it so happens that I have no parenthesis in my data. So I can just find and replace ")" with ");" This worked very well for me in a test run.
If I wanted to be safe I could to more specific finds statements. My largest table has a field at the end that is either NULL or text, so if I actually had memo fields with ), I could do two more specific find and replace runs. Either way, thanks for the great idea! Not sure why I didn't think of it. Guess you are all smarter than I

p.s. I'm on LO 3.5.3.2 Which translates to OO? Maybe I'm using the wrong dev branch as the best Database people seem to be here ... feel free to weigh in on that one.

Search and replace $ for ;\n
As Vileroy suggested, I did try to search for $ with More options/regular expressions selected then replace with ;\n -- unfortunately in my case, this mysteriously skipped every other line. (but otherwise worked just fine)

Importing into MySQL without semi-colons
I did search for importing into MySQL without semi-colons and did not find anything useful other than people suggesting to simply put semi-colons at the end of each statement.

HSQLDB 2.x back end instead
This was naturally my first thought and attempt. I did spend 4+ hours trying to convert to hsqldb 2.x back end configuration, got all my data over successfully, then ran up short on java heap space errors when accessing the data in large tables and queries. I gave up after trying to pass larger memory parameters to Java without a change in results. I read some unresolved posts on the same error and I believe some mention of java runtime bug with hsqldb. My humble notes (with references back to this forum): http://techtips-tom.blogspot.com/2012/0 ... ffice.html. I wouldn't mind changing the ending of that story if I could just get hsqldb working properly. I run Linux Mint 9 Gnome. I suppose it all works better on Windows, but I would loose so much if I had to use Windows just to make Base work, lol. In any case, I thought a reasonable approach was to wait until hsqldb 2.x support was fully integrated... or just move to another backend (and MySQL does look pretty nice. DACM, I certainly was not intending to malign hsqldb. Actually seems like a great product. I just couldn't make it work for me. I did read through quite carefully the "avoid corruption" post and many others. Maybe I missed something, but after spending several partial days on it, I though I'd try something else. .... I really love the Front end I built in OpenOffice, works great for my business, so I certainly don't want to ditch that... and never looked back on my migration over from another very popular personal database solution. After learning some of the differences of OO base, I thought it worked better for me.

Thanks again, you guys are great! ... and try to convince me I'm going down the wrong path if you like
Again, thanks much for all your help! Feel free to try and convince me to go another route, but for now will keep moving forward with MySQL, though hsqldb back end would be my first choice if I could get it working properly.

[chenier]

OK, so I just stumbled across this: http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html

Which gives option of loading data from a file with no semi-colon at end of line:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

So looks like acknack was right... but too late to test tonight.

[DACM]

HSQLDB 2.x back end instead
...ran up short on java heap space errors when accessing the data in large tables and queries. I gave up after trying to pass larger memory parameters to Java without a change in results. I read some unresolved posts on the same error and I believe some mention of java runtime bug with hsqldb. My humble notes (with references back to this forum): http://techtips-tom.blogspot.com/2012/0 ... ffice.html.

Wow! Very nice blog documentation of your efforts!!! Sorry it's not working as advertised. To my knowledge there's no incompatibilities with HSQLDB+Java 1.6 on any platform. The temporary incompatibility when adding Base to the mix is solved by LO 3.5.x -or- by using Java 1.6.0.22.
See: Linux+Java+Base issue: Java 1.6.0.22 downloads for Linux; instructions

That said, I don't know what's going on with the Java heap errors. Your blog suggests you're creating 'cached tables' by default with HSQLDB 2.2.8. With cached tables and only 10,000 or 20,000 records (perhaps 2MB-4MB table size?), I can't think of many scenarios that would cause a query result-set in excess of the default Java heap (64MB), much less your 2GB setting. And with HSQLDB 2.2.x you're not bound to the Java heap memory for result-set size, but that requires a couple of non-default settings. I've never stress-tested HSQLDB 2.2.x with respect to Java heap, but I have stress-tested HSQLDB 1.8.0.10 (built-in Base engine) in this respect. I've done large batch inserts without embedded 'checkpoint' commands just to test this exact 'Java heap error' issue. Since Base has a rather limited buffer for direct-SQL INSERTs, I used SQL Workbench to INSERT the records. I found that around 100,000 batch record (128 byte records) inserts would exceed the default Java heap (64MB). This might equate to a query of 10,0000 records with several OUTER-joins or UNION commands (fairly unrealistic). Increasing the Java heap scaled better than expected. As I recall, I did a successful batch insert of 1 million (128 byte) records with 256MB Java heap memory.

...DACM, I certainly was not intending to malign hsqldb. Actually seems like a great product. I just couldn't make it work for me...but for now will keep moving forward with MySQL, though hsqldb back end would be my first choice if I could get it working properly.

No problem. I'm not here to promote HSQLDB or Java. I simply recognize the typical advantages of modern Java engines in embedded mode with Base. I think you're the first user I've encountered that bumped the default Java heap with under 100,000 records. And your willingness to adjust the heap to 2GB should render result-sets in memory up to 16 million records (128 byte record average) or so. It may be an issue with the HSQLDB 2.x automatic upgrade from HSQLDB 1.8 tables/schema, as we've encountered a few of these in these forums with respect to unspecified VARCHAR size (VARCHAR versus VARCHAR(255)), etc.

Technicalities aside: You spent the time to give HSQLDB 2.2.x a try. You then found a working solution with MySQL on your particular flavor of Linux. That's reason enough to stick with MySQL. Very nice job!

[RoryOF]

Just for information: here is a post which arrived at a workaround for errors when importing a very large number of records into Base.
http://user.services.openoffice.org/en/ ... 20&t=53599