As the subject suggests, I'm using PHP to connect to some SQL Anywhere (126.96.36.19986) 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: Syntax error or access violation: 0 [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'OUTPUT' on line 7 (SQLPrepare 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?
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.