Well, it was easier and faster to work around the problem than wait for hosting to fix it. I'm providing the script I use instead in PHP form. This is hardly a drop-in replacement for LOAD DATA since it isn't as complete and is much slower. But it works. It uses a wrapper "do_sql(.,.)" common routine in place of mysql_query so there's a single point to trap and log. I'm sure you can figure it out.
Hope it helps next time someone was stuck like me.
function loadData( $sql_link, $fromFileName, $tableName, $fieldsList ) {
$recordDelimiter = 0x02 ;
$fieldsDelimiter = "\t" ;
$howManyAtOnce = 1024 ;
$fieldNames = array() ;
$fieldNames = explode( ', ', $fieldsList ) ;
$queryFields = 'SELECT * FROM ' . $tableName . ' ; ' ;
if ( $cursorFields = do_sql( $sql_link, $queryFields ) ) {
$fieldTypes = array() ;
$n = mysql_num_fields( $cursorFields ) ;
$m = $n - 1 ;
for ( $j = 0 ; $j < $n ; ++$j ) {
$name = mysql_field_name ( $cursorFields, $j ) ;
$type = mysql_field_type ( $cursorFields, $j ) ;
$fieldTypes[ $name ] = $type ;
}
mysql_free_result( $cursorFields ) ;
$h = fopen( $fromFileName, "r" ) ;
$buffer = '' ;
$add = fread( $h, $howManyAtOnce ) ;
while( (0 < strlen( $add )) || (0 < strlen( $buffer )) ) {
$buffer .= $add ;
if ( $eorPositionWithinAdd = strpos( $add, $recordDelimiter ) ) {
while( $eorPosition = strpos( $buffer, $recordDelimiter ) ) {
// rem: strpos(.,.) returns a zero-indexed position, so
// using such as a length is using it decremented
$record = substr( $buffer, 0, $eorPosition ) ;
$buffer = substr( $buffer, (1 + $eorPosition) ) ;
$fieldValues = array() ;
$fieldValues = explode( $fieldsDelimiter, $record ) ;
$insertStatement = 'INSERT INTO ' . $tableName . ' ( ' . $fieldsList . ' ) VALUES ( ' ;
foreach( $fieldNames as $fieldIndex => $fieldName ) {
$fieldValue = $fieldValues[ $fieldIndex ] ;
if ( !isset( $fieldTypes[ $fieldName ] ) ) {
logMessage( 'cannot find type for field \'' . $fieldName . '\'' ) ;
return( FALSE ) ;
} else {
switch ( $fieldTypes[ $fieldName ] ) {
case 'blob' :
case 'date' :
case 'datetime' :
case 'string' :
case 'time' :
case 'timestamp' :
case 'year' :
$insertStatement .= '\'' . addslashes($fieldValue) . '\'' ;
break ;
case 'unknown' :
// this will fail when the insert statment is
// attempted and this is deliberate
$insertStatement .= 'UNKNOWN' ;
break ;
case 'int' :
case 'real' :
if ( ereg( '^[[:space:]]*$', $fieldValue ) ) {
$insertStatement .= '0' ;
} else {
$insertStatement .= $fieldValue ;
}
break ;
case 'null' :
// $insertStatement .= 'NULL' ;
$insertStatement .= '\'\'' ;
break ;
default :
break ;
}
$insertStatement .= ', ' ;
}
}
$insertStatement = substr( $insertStatement, 0, (strlen( $insertStatement ) - 2) ) . ' ) ; ' ;
if ( !do_sql( $sql_link, $insertStatement ) ) {
logMessage( 'loadData insert failed' ) ;
return( FALSE ) ;
}
}
}
$add = fread( $h, $howManyAtOnce ) ;
}
fclose( $h ) ;
return( TRUE ) ;
} else {
logMessage( 'cannot access fields of table ' . $tableName . ' in loadData function' ) ;
return( FALSE ) ;
}
}
Jan Theodore Galkowski (o°)
jtgalkowski@alum.mit.edu
http://www.thesmalltalkidiom.net/
The Smalltalk Idiom
laboratory workflow software
PHP,ANSI SQL,relational designs
Internet engineering