Using MySQL "LOAD DATA LOCAL INFILE" with the Joomla database connection object

Question

In a Joomla 3 component I use the MySQL "LOAD DATA LOCAL INFILE" command to import large CSV files into a Joomla database table.

When moving to a new server, it is no longer possible to configure MySQL via php.ini like this:

mysqli.allow_local_infile = On

The web host suggests to use this PHP command:

mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);

What I would like to achieve is to be able to do this with the Joomla database connection object.

A) Adding a $options parameter to the JConfig object in Joomla's configuration.php did not work:

public $options = array('local_infile' => true); // <-- ??

B) Alternatively, I tried creating another database connection like this:

$option['driver']   = 'mysqli'; // Database driver name
$option['host']     = 'host';   // Database host name
$option['user']     = 'user';   // User for database authentication
$option['password'] = 'pass';   // Password for database authentication
$option['database'] = 'name';   // Database name
$option['prefix']   = 'xxx_';   // Database prefix (may be empty)

$option['option']   = array('local_infile' => true); // <-- ???

$db = JDatabaseDriver::getInstance($option);

Experimentation with replacing local_infile with allow_local_infile, mysqli.local_infile, or MYSQLI_OPT_LOCAL_INFILE does not seem to work either.

Will this even be possible with Joomla? Is there any other best practice for importing large CSV files into a Joomla database?

Answer 1

Updated to include the more detailed example based on the 1st comment.

I have been able to get the follow statement to work, or at least not fail, in a simple Joomla 3 Model;

$db = $this->getDbo();
if ($db->getconnection() instanceof mysqli)
{
    mysqli_options($db->getConnection(), MYSQLI_OPT_LOCAL_INFILE, true);
}

However I haven't look too hard into how to confirm that it has made the required change in the MySQL connection so you will need to do that with your function.