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!! asked 12 Jul '13, 10:07 raphael |
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. answered 12 Jul '13, 12:03 Justin Willey 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. :)
(15 Jul '13, 08:27)
raphael
Replies hidden
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...
(15 Jul '13, 09:34)
Volker Barth
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.
(16 Jul '13, 07:29)
raphael
Replies hidden
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...
(16 Jul '13, 09:01)
Volker Barth
|