Re: Strange MySQL Problem

This may not be a perl problem. I am currently doing a similar thing trying to pull data out of foxpro dbf files and put them in other SQL databases. Some lines are not coming across because there are subtle differences in the data types between the data source (foxpro dbf) and the destination.

To figure out what was going haywire, in my case, I tried importing the data into a MS Access database using a system DSN. Some rows generated an error because the value in an integer feild in the row on the source was too large for integer feild on the destination table.

Maybe checking the differences between the values allowed for each datatype in dbf's Vs Mysql might shed light?

Comment on Re: Strange MySQL Problem

Replies are listed 'Best First'.

Re^2: Strange MySQL Problem
by tyndyll (Novice) on Aug 18, 2004 at 15:55 UTC

differences between the values allowed for each datatype in dbf's Vs Mysql might shed light?

When the script starts I go through each of the columns and create a base table of a char type, which in MySQL goes holds up to 255 characters. As I go through the data and dump it into a text file I check whether the column can be converted into a number type, and if not check to see if it has the longest string length (if it does update the length). At the end the tables are updated to optimise them for space etc.

I'm preety sure the problem isn't a type one - using the MySQL client it inserts fine. I thought it was maybe a timing issue - Looking at the last line of the shortened data shows that the last few columns are null.. is the script moving faster than it is writing? If so why did

parse_data();
sleep 10;
query('LOAD DATA INFILE "'.$temp.'" INTO TABLE '.$table.'
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""
        LINES TERMINATED BY "\r\n"');
unlink($temp);
[download]

[reply]
[d/l]


Just another Perl shrine
	PerlMonks