I recently updated my Alpha Anywhere Developer and app server version to build 4770-5018.
After upgrade I am getting an error message for an a5w page which runs a script which says:
Load Data - Database API specific error Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information. 1148 - 'The used command is not allowed with this MySQL version'
I have never had this error before and I have been using the a5w page for many years without problems.
The issue is that the script fails on the line which uses the MySQL statement “LOAD LOCAL INFILE”.
If I run this sql statement from the MySQL workbench, there are no errors.
The issue is that the Named Alpha DAO connection string in the project settings is not working as it used to in previous versions.
If I change my connection string in Alpha Anywhere to MySQLv4 the script works properly. I do not want to use the MySQLv4 connection method because this method does not allow a secure connection.
Here is how to repeat the error:
Step 1 – create a table in a MySQL db called test:
CREATE TABLE `test`.`new_table` (
`col1` VARCHAR(45) NULL ,
`new_tablecol` VARCHAR(45) NULL ,
`new_tablecol2` VARCHAR(45) NULL );
Step 2 – create a text file with tab separated values with the following content and save with file name “uploadfile.txt” which will be used for upload later. I saved this to the root folder of the D: drive for this test:
1 hello this is a test
2 bye goodbye
3 1234 6554
4 abc123 xyz345
Step 3 – Test the following statement in MySQL to ensure it works (you may have to change your system variables “local_infile” to “ON”:
load data local infile 'D:/uploadfile.txt' into table `test`.`new_table` character set latin1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\r\n' STARTING BY '';
Step 4 – Create an a5w page with the following content and publish it to your application and ensure you select the right drive path (this script has path for D:, but you could change it to C: to match where you saved the txt file in Step 2):
<!DOCTYPE html>
<html>
<head>
<meta name="generator" content="Alpha Anywhere HTML Editor Version 12 Build 4770-5018">
<!-- must use in order to make XP Themes render -->
<meta HTTP-EQUIV="MSThemeCompatible" content="Yes" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
<meta name="apple-mobile-web-app-capable" content="yes" />
<title></title>
</head>
<body>
<%a5
dim conn as SQL::Connection
conn.open("::Name::test")
dim q2 as c
q2 = "load data local infile 'D:/uploadfile.txt' into table `test`.`new_table` character set latin1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\r\n' STARTING BY '';"
conn.execute(q2)
if conn.callresult.text <> "Success" then
? "Load Data - "+conn.callresult.text
conn.close()
stop
else
? "Load Data - "+conn.callresult.text
end if
%>
</body></html>
Step 5 – Create a connection string to your test database and put the connection string parameters in your project settings. Use the MySQL connection type when you build it instead of the MySQLv4 connection type.
Step 6 – Publish the page and navigate to it in your browser… you will see the error message.
Please let me know if there is a setting I can change somewhere in the app server to allow the “LOCAL INFILE” statement to work. Of course, in the MySQL database I have enabled this, however, the load data local infile statement only works in the AlphaDAO connection type MySQLv4.
After upgrade I am getting an error message for an a5w page which runs a script which says:
Load Data - Database API specific error Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information. 1148 - 'The used command is not allowed with this MySQL version'
I have never had this error before and I have been using the a5w page for many years without problems.
The issue is that the script fails on the line which uses the MySQL statement “LOAD LOCAL INFILE”.
If I run this sql statement from the MySQL workbench, there are no errors.
The issue is that the Named Alpha DAO connection string in the project settings is not working as it used to in previous versions.
If I change my connection string in Alpha Anywhere to MySQLv4 the script works properly. I do not want to use the MySQLv4 connection method because this method does not allow a secure connection.
Here is how to repeat the error:
Step 1 – create a table in a MySQL db called test:
CREATE TABLE `test`.`new_table` (
`col1` VARCHAR(45) NULL ,
`new_tablecol` VARCHAR(45) NULL ,
`new_tablecol2` VARCHAR(45) NULL );
Step 2 – create a text file with tab separated values with the following content and save with file name “uploadfile.txt” which will be used for upload later. I saved this to the root folder of the D: drive for this test:
1 hello this is a test
2 bye goodbye
3 1234 6554
4 abc123 xyz345
Step 3 – Test the following statement in MySQL to ensure it works (you may have to change your system variables “local_infile” to “ON”:
load data local infile 'D:/uploadfile.txt' into table `test`.`new_table` character set latin1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\r\n' STARTING BY '';
Step 4 – Create an a5w page with the following content and publish it to your application and ensure you select the right drive path (this script has path for D:, but you could change it to C: to match where you saved the txt file in Step 2):
<!DOCTYPE html>
<html>
<head>
<meta name="generator" content="Alpha Anywhere HTML Editor Version 12 Build 4770-5018">
<!-- must use in order to make XP Themes render -->
<meta HTTP-EQUIV="MSThemeCompatible" content="Yes" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
<meta name="apple-mobile-web-app-capable" content="yes" />
<title></title>
</head>
<body>
<%a5
dim conn as SQL::Connection
conn.open("::Name::test")
dim q2 as c
q2 = "load data local infile 'D:/uploadfile.txt' into table `test`.`new_table` character set latin1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\r\n' STARTING BY '';"
conn.execute(q2)
if conn.callresult.text <> "Success" then
? "Load Data - "+conn.callresult.text
conn.close()
stop
else
? "Load Data - "+conn.callresult.text
end if
%>
</body></html>
Step 5 – Create a connection string to your test database and put the connection string parameters in your project settings. Use the MySQL connection type when you build it instead of the MySQLv4 connection type.
Step 6 – Publish the page and navigate to it in your browser… you will see the error message.
Please let me know if there is a setting I can change somewhere in the app server to allow the “LOCAL INFILE” statement to work. Of course, in the MySQL database I have enabled this, however, the load data local infile statement only works in the AlphaDAO connection type MySQLv4.