Question Re: PHP, PDO, ODBC, SQL Anywhere 9, and output to problem in Technology Q&A

PHP, PDO, ODBC, SQL Anywhere 9, and output to problem

Former Member — Fri, 12 Jul 2013 10:07:32 GMT

As the subject suggests, I'm using PHP to connect to some SQL Anywhere (9.0.2.3586) databases using ODBC. Here is the code:

<?php
$dsn = 'odbc:DSN=myserver;uid=test;pwd=test';
$user = 'test';
$pwd = 'test';

$dbh = new PDO($dsn, $user, $pwd);

$stmt = $dbh->prepare($sql);
?>

The SQL statement is really long, so let's say it's something like this:

select
col1,
col2
from
mytable;

output to 'd:/out/myfile.csv'
format ascii
quote ''
delimited by ','
encoding 'utf8';

I get this error: Error: SQLSTATE[42000]: Syntax error or access violation: 0 [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'OUTPUT' on line 7 (SQLPrepare[0] at extpdo_odbcodbc_driver.c:206)

To get file output to work with MySQL, I had to add this attribute:

$attributes = array(PDO::MYSQL_ATTR_LOCAL_INFILE => true);

 $dbh = new PDO($dsn, $user, $pwd, $attributes);

Do I need to do something like that for SQL Anywhere?

Thanks!!

Re: PHP, PDO, ODBC, SQL Anywhere 9, and output to problem

justin_willey — Fri, 12 Jul 2013 12:03:41 GMT

Hi OUTPUT TO is a facility of ISQLs (the client) not of the database engine.

If you want the engine to do the work then try:

UNLOAD 
 select col1, col2 from mytable
INTO CLIENT FILE
 'd://out//myfile.csv'
ENCODING 'UTF-8'
DELIMITED BY ','
QUOTE '';

To do a client side unload there are some permission issues, see UNLOAD syntax for details. Note the escaping of the slashes. (assumes v12.0.1)

UPDATE - However I've just checked v9 (which is many years EOLed now) - it doesn't allow client side unloads, so the unload location would have to be relative to the server. Also there is no support for specifying the encoding. - the syntax would be:

UNLOAD 
 select col1, col2 from mytable
INTO 
 'f://out//myfile.csv'
DELIMITED BY ','
QUOTES OFF;

where f: is a drive visible to the SERVER.

Re: PHP, PDO, ODBC, SQL Anywhere 9, and output to problem

Former Member — Mon, 15 Jul 2013 08:27:33 GMT

Thanks for your reply. I can use "output to" with dbisql when executed from the command line on the server. It executes the sql on the remote computer and then creates the csv on the server. I was just hoping I could use PHP's built-in PDO functions to run the SQL instead of having to use exec() to run dbisql.

BTW, I know SQL Anywher v9 is way out-dated, but that's what we're stuck with for now. 🙂

Re: PHP, PDO, ODBC, SQL Anywhere 9, and output to problem

VolkerBarth — Mon, 15 Jul 2013 09:34:09 GMT

It executes the sql on the remote computer and then creates the csv on the server.

What machine is the SQL Anywhere server running on?

Using OUTPUT with DBISQL is a perfect choice to export data to clients (where "client" is seen from a SA database server point-of-view).

However, as Justin has explained, when using your own database client instead of DBISQL, then you will have to use UNLOAD to export data. - If the database server can not access the client directory, you may still use a command shell (or whatever PHP offers here) to move the files to the client-side...

Re: PHP, PDO, ODBC, SQL Anywhere 9, and output to problem

Former Member — Tue, 16 Jul 2013 07:29:59 GMT

Our setup:

server in our office with a MySQL database (Computer A)

remote server in one of our stores that has a Sybase SQL Anywhere database (Computer B)

Computer A is trying to contact Computer B to extract data from SQL Anywhere and store it in the MySQL database on Computer A.

Computer A has an ODBC connection to Computer B.

Re: PHP, PDO, ODBC, SQL Anywhere 9, and output to problem

VolkerBarth — Tue, 16 Jul 2013 09:01:23 GMT

Well, if it were the other way around (remote SA database can access the MySQL database), then you might use SA's proxy tables feature (aka "Remote Data Access") to insert/modify data directly from SA tables to MySQL tables - without the need to extract data files. That would require an ODBC connection from B to A - and could be packaged in an SA event or stored procedure to automate that (and protect permissions).

Apparently, I do not know whether that would be acceptable for you w.r.t. security/responsibility and the like...