How to Convert informix to MySQL?

I've got some data that was exported from Informix, how do I convert the data to MySQL?

[Free WordPress Themes]

I've got some data that was exported from Informix, how do I convert the data to MySQL?

can you post some example data?

The data is |-seperated so it looks something like this:

234234|AAAA|abcd|2006-03-04

The SQL looks like this:

Code:

{ TABLE "itis".change_comments row size = 263 number of columns = 4 index size =
              30 }
{ unload file name = chang00100.unl number of rows = 0 }

create table "itis".change_comments
  (
    change_track_id integer not null ,
    chg_cmt_id serial not null ,
    change_detail varchar(250,1) not null ,
    update_date date not null
  );
revoke all on "itis".change_comments from "public";

and then after all the table definitions there's some key and constraints defenitions:

Code:

create unique index "itis".change_comments_ky on "itis".change_comments
    (change_track_id,chg_cmt_id) using btree ;
alter table "itis".change_comments add constraint primary key
    (change_track_id,chg_cmt_id) constraint "itis".u111_1  ;

and

Code:

update statistics medium for table comments (
     comment_detail, comment_id, comment_time_stamp, commentator, update_date)
     resolution   2.50000   0.95000 ;

[Free WordPress Themes]

do you have phpmyadmin?

yes

[Free WordPress Themes]

first you have to create a table (is easy, read the beginners tutorials) and then locate this option "Insert data from a textfile into table" (tab sql) 
that's all

yes, I know how to create a table in phpMyAdmin. The problem is that the exported SQL doesn't work. The posted data is just a sample, there are a lot of tables in the database with a lot of constraints and indexes. It would be nice if there would be a tool to translate the Informix SQL to MySQL SQL. I've see some commercial tools for this, but I'm looking for the cheap way...

[Join us @ facebook]

Try to create your tables by hand, and then import the data.

I don't think there is an open source translator for informix to mysql

can I use the

Code:

update statistics medium for table comments (
     comment_detail, comment_id, comment_time_stamp, commentator, update_date)
     resolution   2.50000   0.95000 ;

command in MySQL? I know alot about SQL on iSeries (IBM DB2) but never seen 'update statistics medium'

[Join us @ facebook]

can I use the

Code:

update statistics medium for table comments (
     comment_detail, comment_id, comment_time_stamp, commentator, update_date)
     resolution   2.50000   0.95000 ;

command in MySQL? I know alot about SQL on iSeries (IBM DB2) but never seen 'update statistics medium'

It is the first time I see this. Propably you can't do that with mySQL

[Free WordPress Themes]

yes, I know how to create a table in phpMyAdmin. The problem is that the exported SQL doesn't work. The posted data is just a sample, there are a lot of tables in the database with a lot of constraints and indexes. It would be nice if there would be a tool to translate the Informix SQL to MySQL SQL. I've see some commercial tools for this, but I'm looking for the cheap way...

how many tables do you have 100 ? just create a few tables manually and if you want to translate all sql statements (update, insert, select...), be carefull you are running on thin ice...

Oke, I've created the tables manually, now to upload the data...
The data I have looks like this:

1|aaa|03/26/2005|
2|bbb|02/14/2006|

How can I upload this data in phpMyAdmin?
I've tried about every option but I'm getting errors or not the data I expect...

[Join us @ facebook]

Why don't you do it manually?

For the example I see you will need something like :

Code:

<?php
$records = explode("\n", $input); //$input = the data you have
foreach ( $records as $l )
{
    $rec = explode('|', substr($l,0,-1));
    //Do something with the $rec
}

Hope that helps

yeah that sounds good! but:
1. how do I get the data in $input (do I load the whole file at once?)
2. I get the $rec = explode('|' but where do I find the 3 values after the explode (so what do I need to use on the INSERT?
Thanks!

[Join us @ facebook]

1. If the file ia too big, it would be better to use fgets, but you wont have any problem with files bigger than 500 mb.

2. After the explode, $rec[0] is the first value, $rec[1] is the second, etc. If you are asking wich field goes with each value, then this is not a problem as in mysql you can say INSERT INTO table (value1,value2,value3,.....)

The biggest file is 50 MB, so I guess it can be done in one go?
How do I get the file content into $input?

[Join us @ facebook]

The biggest file is 50 MB, so I guess it can be done in one go?
How do I get the file content into $input?

Yes it will be ok.

Grab the contents with this :

Code:

<?php
 $input = file_get_contents( 'filename_with_path.sql' );

cool, thanks man!

I'm filling up the database, the first couple of file went oke, but I'm getting errors now. I think the error is because of commas in the field value. What would be the best way to fix this?
The data looks like this:

123|aaa,2|bbb

[Join us @ facebook]

I'm filling up the database, the first couple of file went oke, but I'm getting errors now. I think the error is because of commas in the field value. What would be the best way to fix this?
The data looks like this:

123|aaa,2|bbb

The problem is not the comma. Do you use ' for every value that you insert?

What is the error that you get?

[Guest]

[44.235]

Msnbot, Yahoo crawler, Googlebot, Baidu Spider