Generate DDL from 4D database

I have inherited a 4D database that I need to extract all the data from to import to another relational database. The 4D database ODBC driver seems to have quite a few quirks that prevents it from being used as a SQL Server linked server. I can give the gory details if anyone wants them but suffice to say; it's not looking like a possibility.

Another possibility I tried was using the MS SQL Server Import Data wizard. This is, of course, SSIS under the covers and it requires the 32 bit ODBC driver. This gets part of the way but it fails trying to create the target tables because it doesn't understand what a CLOB datatype is.

So my reasoning is that if I can build the DDL from the existing table structure in the 4D database I might be able to just import the data using the Data Import wizard if I create the tables first.

Any thoughts on what tools I could use to do this?

Thanks.

Solution

Alas, the 4D ODBC drivers are a (ahem) vessel filled with a fertiliser so powerful that none may endure its odour...

There is no simple answer but if you have made it here, you are already in a bad place so I will share some things that will help.

You can use the freeware ODBC Query Tool that can connect to the ODBC through a user or system DSN with the 64 bit driver. Then you run this query:

SELECT table_id, table_name,column_name, data_type, data_length, nullable, column_id FROM _user_columns ORDER BY table_id, column_id limit ALL

Note: ODBC Query Tool fetches the first 200 row pages by default. You need to scroll to the bottom of the result set.

I also tried DataGrip from JetBrains and RazorSQL. Neither would work against the 4D ODBC DSN.

Now that you have this result set, export it to Excel and save the spreadsheet. I found the text file outputs to be not be useful. They are exported as readable text, not CSV or tab delimited.

I then used the Microsoft SQL Server Import Data Wizard (which is SSIS) to import that data into a table that I could then manipulate. I am targeting SQL Server so it makes sense for me to make this step but if you importing to another destination database, you may create the table definitions from the data you now have whatever tool you think is best.

Once I had this in a table, I used this T-SQL script to generate the DDL:

    use scratch;

-- Reference for data types: https://github.com/PhenX/4d-dumper/blob/master/dump.php

declare @TableName varchar(255) = '';

declare C1 CURSOR for 
    select distinct table_name
    from 
        [dbo].[4DMetadata]
    order by 1;

open C1;
fetch next from C1 into @TableName;

declare @SQL nvarchar(max) = '';
declare @ColumnDefinition nvarchar(max) = '';

declare @Results table(columnDefinition nvarchar(max));

while @@FETCH_STATUS = 0
begin
    set @SQL = 'CREATE TABLE [' + @TableName + '] (';
    declare C2 CURSOR for
        select
            '[' + 
            column_name +
            '] ' +
            case data_type
                when 1 then 'BIT'
                when 3 then 'INT'
                when 4 then 'BIGINT'
                when 5 then 'BIGINT'
                when 6 then 'REAL'
                when 7 then 'FLOAT'
                when 8 then 'DATE'
                when 9 then 'DATETIME'
                when 10 then 
                    case 
                        when data_length > 0 then 'NVARCHAR(' + cast(data_length / 2 as nvarchar(5)) + ')'
                        else 'NVARCHAR(MAX)'
                    end
                when 12 then 'VARBINARY(MAX)'
                when 13 then 'NVARCHAR(50)'
                when 14 then 'VARBINARY(MAX)'
                when 18 then 'VARBINARY(MAX)'
                else 'BLURFL' -- Put some garbage to prevent this from creating a table!
            end +
            case nullable
                when 0 then ' NOT NULL'
                when 1 then ' NULL'
            end +
           ', '
        from
            [dbo].[4DMetadata]
        where 
            table_name = @TableName
        order by column_id;
    open C2;
    fetch next from C2 into @ColumnDefinition;

    while @@FETCH_STATUS = 0
    begin
        set @SQL = @SQL + @ColumnDefinition;

        fetch next from C2 into @ColumnDefinition;
    end
    -- Set the last comma to be a closing parenthesis and statement terminating semi-colon
    set @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ');';

    close C2;
    deallocate C2;

    -- Add the result
    insert into @Results (columnDefinition) values (@SQL);
    fetch next from C1 into @TableName;
end
close C1;
deallocate C1;

select * from @Results;

I used the generated DDL to create the database table definitions.

Unfortunately, SSIS will not work with the 4D database ODBC driver. It keeps throwing authentication errors. But you may be able to load this database with your own bespoke tool that works with the ODBC weirdness of 4D.

I have my own tool (unfortunately I cannot share it) that will load the XML exported data directly to the database. So I am finished.

Good luck.