0

In order to import large CSV files into a Joomla database table, I use the MySQL "LOAD DATA LOCAL INFILE" command as follows:

$db = $this->getDbo();

$sql = "LOAD DATA LOCAL INFILE '" . $path . "' INTO TABLE " . $db->quoteName('#__mytable') . " ";
$sql .= "CHARACTER SET LATIN1 ";
$sql .= "FIELDS TERMINATED BY ';' ";
$sql .= "LINES TERMINATED BY '\n' ";
$sql .= "IGNORE 1 LINES ";
$sql .= "(id, name, ...);";

try {
   if ($db->getConnection() instanceof mysqli) {
      mysqli_options($db->getConnection(), MYSQLI_OPT_LOCAL_INFILE, true);
   }
} catch (Exception $e) {
   $msg = $e->getMessage(); 
   $code = $e->getCode();
}

try {
   $db->setQuery($sql);
   $result = $db->execute();
} catch (Exception $e) {
   $msg = $e->getMessage(); 
   $code = $e->getCode();
}

This works fine in Joomla 3 - as described in this thread:

In Joomla 4, the 2nd try/catch throws the following error:

error 1295 This command is not supported in the prepared statement protocol yet

Is there a way to get this code to work in Joomla 4 as well?

6
  • After the setQuery() and before the execute() insert these two lines to dump the actual SQL that is about to be executed. $fred = $db->query->dump(); echo '<pre>'. $fred . '</pre>'; This might show you if there is some other statement or syntax that is not valid with MySQL being added as part of Joomla 4
    – Irata
    Jul 29, 2023 at 11:45
  • If you haven't already found it, this question has similarities to your statements, less the obvious PREPARE statement, stackoverflow.com/questions/44360756/… but it might be a useful reference.
    – Irata
    Jul 29, 2023 at 11:49
  • Thanks for the hints: I do get a 500 error on $db->query->dump() which stops execution of the PHP script. No feedback from the Webhosting company, yet. Cannot make changes on Server or MySQL. Still looking for a way to fix this on PHP / Joomla side.
    – cappu
    Jul 30, 2023 at 8:41
  • Are you using an IDE or XDebug with this code that you can step through what is going on? I can't think why the dump() would be causing a 500 error.
    – Irata
    Jul 30, 2023 at 8:54
  • Well, I tried a lot. The code breaks on $db->setQuery($sql). Switched to local development environment. Still get the same error message: 1295 This command is not supported in the prepared statement protocol yet
    – cappu
    Jul 30, 2023 at 18:59

1 Answer 1

2

Since 4.0, Joomla database layer always uses prepared statements. And as the error states, the query you're trying to run is not supported when used in prepared statements. This is true at least for the combination of the MySQLi library and the MySQL database used. To get things working, you might have to not use prepared statements. You can do that by bypassing Joomla database layer completely and manually creating a mysqli instance. You can also use Joomla\Database\DatabaseInterface::getConnection() to get the mysqli instance, as you are already doing, and run the query directly on it:

$connection = $db->getConnection();
mysqli_options($connection, MYSQLI_OPT_LOCAL_INFILE, true);

// Replace #__ table prefix.
$sql = $db->replacePrefix($sql);

// Run the query.
$connection->query($sql);

PDO, on the other hand, appears to work correctly (YMMV). However, PDO::MYSQL_ATTR_LOCAL_INFILE attribute must to be passed to the constructor of PDO class and cannot be set later. Therefore, you need to create a new driver instance with the attribute already in:

$options = [
    'host' => 'host',
    'user' => 'user',
    'password' => 'password',
    'database' => 'database',
    'prefix' => 'xxx_',
    // Set connection attributes.
    'driverOptions' => [PDO::MYSQL_ATTR_LOCAL_INFILE => 1],
];

// Create PDO MySQL driver.
$db = (new Joomla\Database\DatabaseFactory)->getDriver('mysql', $options);

// Execute the query.
$db->setQuery($sql);
$db->execute();
8
  • With the first variant I get 1295 This command is not supported in the prepared statement protocol yet.
    – cappu
    Jul 31, 2023 at 9:43
  • In the second I added 'driver' => 'mysqli', to the options array. Using 'driverOptions' => [PDO::MYSQL_ATTR_LOCAL_INFILE => 1] I get 1295 This command is not supported in the prepared statement protocol yet as well. Change it to 'options' => [PDO::MYSQL_ATTR_LOCAL_INFILE => 1] the error message is [0] HY000, 2068, LOAD DATA LOCAL INFILE is forbidden, check related settings like mysqli.allow_local_infile|mysqli.local_infile_directory or PDO::MYSQL_ATTR_LOCAL_INFILE|PDO::MYSQL_ATTR_LOCAL_INFILE_DIRECTORY.
    – cappu
    Jul 31, 2023 at 9:43
  • It sounds like you haven't removed the old query execution code. Can't say without seeing complete code. I don't know why you're trying to change the provided options.
    – Sharky
    Jul 31, 2023 at 12:19
  • I tried the provides options, too. But it didn't work and the error message stays the same.
    – cappu
    Jul 31, 2023 at 14:08
  • I need to see the complete code. Calling query() method on mysqli object should not be producing the error since it does not use prepared statements.
    – Sharky
    Aug 1, 2023 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.