Antworten auf Ihre Fragen • Datenbank-Pflege • 4D Coaching • OpenDDDD • Termine • 4D Expert V11/V12-Service • Konzepte • Alternativen • Meine Apps
GebrauchtesMac + iPhone: zu verkaufen
Geo-Themen + ProjekteGoogleMap-Integration • GeoDDDD • Database Publishing • Database Photography
26. Mai 2009
Those friendly guys over there send me a MySQL-dumpfile. That is a 107 MB text-document.
BBEdit displays the MySQL-dumpfile like this:
All table-definitions and records are contained in this single document. Everything is SQL-Syntax. Devided by commentaries first the table-definition then the records. This repeats for 15 tables. For a total of 1.170.508 lines. The first 20-something lines are shown as BBEdit displays them.
How to get those 107 MB of data into 4D and generate the PDFs, which is why I got the data in first place? I used this opportunity, to test the real SQL-power of V11.
First I attempted, in case it were that simple:
Receive packet line for line and react depending on content:
From my earlier attempts I knew that 4D V11 is not very elastic concerning key-words. It doesn't accept char(15) but exactly varchar(15) *, text is varchar without length. Obviously MySQL and 4D V11 don't use an identical SQL-syntax (spring_SQL92 vs. autumn_SQL92): default NULL is nothing 4D V11 will handle, neither KEY nor TYPE=MyISAM.
Therefor the table-definitions had to be cleaned first, changed into something more 4D V11-compatible. Doing that I exchanged also CREATE TABLE with CREATE TABLE IF NOT EXISTS, so I could import into the same 4D structure a couple of times. I omitted KEY completely. Extra "," and ")" and double spaces " " generate 4D V11 SQL-errors, so I cleaned them out, too.
: ($what="Bereinigen") ` cleaning because 4D doens't like SQL of MySQL - sort of
Then it's easy. EXECUTE IMMEDIATE executes the SQL-statement immediately. The 4D-method looks like this:
The SQL-definition for every table is called like this:
The data is a single one-line INSERT INTO-statement. One example here:
As so often done, I first attempted to convert the characters above Ascii 127 myself. But that's much easier this way
USE CHARACTER SET
USE ASCII MAP renames to USE CHARACTER SET("iso-8859-1";$importFilter_L) in V11. Available is a subselection of IANA Names. In this case import used "iso-8859-1" and export USE CHARACTER SET("MacRoman";$exportFilter_L). Simple - effektiv - good.
Acceptable! The big tables had 700.000, 350.000 and 100.000 records respectively. The whole import took about 20 minutes. BBEdit needs already a couple of minutes to open the original-107 MB-textfile.
Everything in one method
I love that way of writing code. Everything which belongs to a modul
* I prefer pragmatism. My code would test char or varchar and react properly, same is true for int and int32. Probably to simple, to much 4D thinking. ↵
You want to have the source-code? No problem! Send a descend amount of $ or € to my PayPal-account: firstname.lastname@example.org. Finding out how much?