Problem using custom query with LOAD DATA LOCAL INFILE

Wappler General
1

I’m trying to create a server action to import records quickly, using LOAD DATA LOCAL INFILE. I created a custom query but it won’t run. This is the error message:

image

I can use a separate PHP file to do this, including this line:
PDO::MYSQL_ATTR_LOCAL_INFILE => true

I don’t know if this is all that’s needed, but I imagine there isn’t a way to add options like this. It would be useful if there were.

(This is not exactly a feature request, because it may be that I’m doing something wrong, and in any case, I can use PHP to do this - though of course it would be much more convenient if it could be done within Wappler.)

3

Do you have other PHP scripts that are also accessing the database?

4

Yes, lots. It occurred to me that modifying the Wappler files might have undesirable implications.

Edit: by PHP scripts, I meant other server action files, not custom PHP scripts created separately.

5

Server Connect always uses fetchAll, so it should not give this error. If you want to try the PDO option, you can add it in dmxConnectLib/lib/db/Connection.php around line 60.

6

Thanks Patrick. I added this line but the error message is the same.

7

Do you have other queries in the same action file as the custom query?

Did you also try setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY as in the error mentioned?

8

Great - it’s working now. Many thanks.

This is the code as it is now:

	if (preg_match('/^mysql:/', $dsn)) {
		$pdo_options = array(
			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
			PDO::ATTR_EMULATE_PREPARES => $preps,
			PDO::MYSQL_ATTR_LOCAL_INFILE => true,
			PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true,
			PDO::ATTR_STRINGIFY_FETCHES => FALSE
		);

I first added => after ‘… BUFFERED QUERY’ but that didn’t work.

Could this change have any implications for other queries or cause any problems? If not, will it be added to the code now?

9

What was the error you got? Correct syntax should be:

$pdo_options = array(
	PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
	PDO::ATTR_EMULATE_PREPARES => $preps,
	PDO::MYSQL_ATTR_LOCAL_INFILE => true,
	PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
	PDO::ATTR_STRINGIFY_FETCHES => FALSE
);

I don't know the effect of MYSQL_ATTR_LOCAL_INFILE, the MYSQL_ATTR_USE_BUFFERED_QUERY should bot affect other code.

10

With the first syntax I used (as you show above), I got the same error message as before. Only when I replaced the => with a comma did it work. I’ve just tried again, using both versions - same result. It seems odd. I commented out the line to check it was needed (I think buffered queries are the default) and I got the same error.