MySQL Forums
Forum List  »  General

Re: permissions on LOAD DATA?
Posted by: Jan Theodore Galkowski
Date: October 17, 2004 03:47PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: permissions on LOAD DATA?
October 17, 2004 03:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.