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's gravatar image

raphael
1515614
accept rate: 33%

edited 12 Jul '13, 10:40


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.

permanent link

answered 12 Jul '13, 12:03

Justin%20Willey's gravatar image

Justin Willey
6.7k108137208
accept rate: 20%

edited 12 Jul '13, 12:31

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

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...

(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×46
×24

question asked: 12 Jul '13, 10:07

question was seen: 2,650 times

last updated: 16 Jul '13, 09:03