Sadly I lost a client last week as they decided it would be more expensive to pay for all the missed updates of 4D than to buy something completely new…
At least I got them to pay for me helping to export that data from 4D to something  the new supplier (who is using MSSQL server) could read…

Well I thought, that’s going to be quick money and fired up SQL EXPORT DATABASE…

Boy was I wrong…

As long as you stay within the 4D Universe all may be fine, but no other SQL database engine can read these Export.sql files 4D creates, simply due to the way they write the INSERT INTO line…

INSERT INTO [ATY_TITLES] ( [ATT_ID] , [ATT_ATY_ID] , [ATT_TITLE] , [ATT_HANDLE] , [ATT_WTS_ID] , [ATT_WTP_ID] )

This may make sense for 4D, as there are apparently still some people out there who use spaces in field and table names (allowing spaces, special characters etc. in object names was not one of the better ideas Laurent ever had…) but all other SQL systems will just reject this syntax as being utterly ridiculous..

I also found out, that contrary to other SQL dumps, for example by MySQL, the 4D dump does not contain the CREATE TABLE statements needed to recreate a compatible structure.

A quick search revealed there is no such command in 4D, all these nice structure exports to XML, HTML, etc. are absolutely useless in this context (has anyone noticed that you cannot print the structure anymore?).

So, only solution, roll my own.

Result is this little stand-alone component with only a few commands:

SchemaText:=ACSQLDEF_SchemaDump({TargetSystem{;SortTable{;SortFields}}})

It will fill a text variable with the complete schema definition of the host database, including Indexes, primary keys and the UNIQUE constraints set in the database.

You may pass a few optional parameters:

TargetSystem  : text    : The target database system for the Schema export, as different target systems require different syntax for some fields. Currently supported are MySQL4; MySQL5 (different handling of booleans); MSSQL and the default 4D

SortTable     : boolean : Sort tables alphabetically
SortFields    : boolean : well, take a guess

As for the target systems, there is a json file in the resources folder, look at it and you can add more target systems, if you do, please share them back.

Now for those unusable Export.sql files…

ACSQL_CleanDump

Will take care of these. Just point it to the SQLExport folder created by 4D and it will troll through all files transforming this

INSERT INTO [ATY_TITLES] ( [ATT_ID] , [ATT_ATY_ID] , [ATT_TITLE] , [ATT_HANDLE] , [ATT_WTS_ID] , [ATT_WTP_ID] )

into this

INSERT INTO ATY_TITLES ( ATT_ID, ATT_ATY_ID, ATT_TITLE, ATT_HANDLE, ATT_WTS_ID, ATT_WTP_ID )

With a 32 GB Export made up of roughly 18 900 files it took some 21 minutes to run.

Of course you must make sure that your table and field names are SQL compliant… I could have added a check in here, but where would be the fun in this?

If you start the component in compiled mode or as a compiled application, it will automatically run this command.

There are a few more tools in there:

TableDef:=ACSQLDEF_TableDefinition(TablePointer{;SortFields})

Create the table definition for a specific table

$p_FieldPointer:=ACSQL_PrimaryKeyofTable(TablePointer)

Will return a pointer to the PrimaryKey field of a table, if the PrimaryKey is made up of multiple fields, only the first field is returned.

Indexes:=ACSQLDEF_IndexesForTable(TablePointer)

will return the SQL Index creation statements for the the table passed, composite indexes are handled as well, you might get something like this in return:

CREATE INDEX IDX_11_21 ON ADRESSEN (ADR_IS_MAIN);
CREATE INDEX IDX_11_11 ON ADRESSEN (ADR_FIRMA,ADR_FIRMA2);
CREATE INDEX IDX_11_10 ON ADRESSEN (ADR_SORT);

PrimaryKeys:=ACSQLDEF_PKeyForTable(TablePointer)

will return a text with only the field name(s) of the fields that make up the primary key of the table

ADR_IS_MAIN
or
ADR_FIRMA,ADR_FIRMA2

Ok, that’s all folks, enjoy.

The component is V14 and can be downloaded here:

AC_SQLHelper.4dbase

You need the source? Make me an offer I can’t resist…

I’d love to get feedback on this.