dbisql - OUTPUT USING 'DSN/driver' not working (sybase 11)

[Former Member]

Hi,

im looking for a way to move a sybase-table to mysql, so i checked the docs and found the dbisql-tool with the output-statement.

At the end of the page it says:

The following example copies the Customers table into a fictitious MySQL database called mydatabase, using the DRIVER option.

SELECT * FROM Customers;
OUTPUT USING "DRIVER=MySQL ODBC 5.1 Driver;DATABASE=mydatabase;SERVER=mySQLHost;UID=me;PWD=secret"
INTO "Customers"; 

I tried that with dbisql 11 on a lexware2012 installation (sybase 11), but i always get an syntax error on the 'using' parameter.

I can login to the sybase-db without a problem, mysql-odbc driver is installed and i can connect to the mysql-db (USING-rights)

Did i something wrong or miss something or is it a bug?

Would be nice if some1 could point me in the right direction.

Thx

[jack_schueler]

Have you tried using apostrophes (') rather than double quotes (").

OUTPUT USING 'DRIVER=MySQL ODBC 5.1 Driver;DATABASE=mydatabase;SERVER=mySQLHost;UID=me;PWD=secret'

[Breck_Carter]

Excellent catch!

The "double quotes" used in the Help example do indeed NOT work 🙂

[Former Member]

Yes i tried that too, and also: OUTPUT USING 'DSN=mydsn'; | OUTPUT USING DSN='mydsn';

But always the same result, thx anyway

[jack_schueler]

If you forget to include the INTO table name clause, then you do get a syntax error. I suspect that you are showing us the entire statement and I see OUTPUT USING connection-string but not OUTPUT USING connection-string INTO destination-table-name. (See comment below for an example).

[Former Member]

Thx for the examples with the proxy-tables. Thats it, and it's much more flexible than using the dbisql-tool.

However would be nice to know how the command-line has to be to export a table into an odbc-connected server using the dbisql-tool.

[jack_schueler]

I don't have access to a MySQL server but here is an example that works with MS Excel.

SELECT * FROM SalesOrders; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\\temp\\sales.xls;READONLY=0' INTO "newSalesData";

Since the Excel driver is 32-bit only, I made sure to use a 32-bit SQL Anywhere server.

[Breck_Carter]

Caveat Emptor: The following code examples use Microsoft SQL Server, not MySQL.

---

The examples in the Help only show SQL Anywhere databases.

Your problem may be that the connection string syntax used in the Help works for a SQL Anywhere database but not a MySQL database; in particular, the "dbf=" thing.

If you have an ODBC DSN for your MySQL database, that might make coding the connection string easier;

here is an example that works using SQL Anywhere 12.0.1.3298 and Microsoft SQL Server 2008 (SP2):

( Update: As @JBSchueler notes, using 'single quotes' instead of the "double quotes" in the Help example will probably have a larger effect than a DSN 🙂

----------------------------------------------------------------
-- Test 1a: SELECT and OUTPUT USING on SQL Anywhere

CREATE TABLE local_customer  
   ( pkey INTEGER NOT NULL PRIMARY KEY,
     data INTEGER NOT NULL )

INSERT local_customer VALUES ( 1, 1 );
INSERT local_customer VALUES ( 2, 2 );
COMMIT;

SELECT * FROM local_customer;

OUTPUT USING 'DSN=MSSTEST;uid=sa;pwd=j68Fje9#fyu489'
INTO "mss_customer2";

----------------------------------------------------------------
-- Test 1b: SELECT on SQL Server

SELECT * FROM mss_customer2
go

1> SELECT * FROM mss_customer2
2> go
 pkey        data
 ----------- -----------
           1           1
           2           2

(2 rows affected)

I would have bet real $$money$$ that OUTPUT USING only worked for SQL Anywhere databases... which is a good reason for me not to go to Vegas.

( Update: Scrolling down just a few more lines in the Help topic would reveal a MySQL example, albeit with "incorrect double quoting around the connection string", thus reinforcing the "no ir a Las Vegas" rule 🙂

Anyway, for the record, here is the more common approach using a proxy table (a.k.a. "remote data access"); clearly, if you can use the ISQL-only OUTPUT statement, that does seem easier than this:

----------------------------------------------------------------
-- Test 2a: CREATE TABLE ... AT without EXISTING on SQL Anywhere

CREATE SERVER mss 
   CLASS 'MSSODBC' 
   USING 'DSN=MSSTEST';

CREATE EXTERNLOGIN DBA  
   TO mss  
   REMOTE LOGIN "sa" 
   IDENTIFIED BY 'j68Fje9#fyu489';

CREATE TABLE proxy_customer  
   ( pkey INTEGER NOT NULL PRIMARY KEY,
     data INTEGER NOT NULL )
   AT 'mss.test.dbo.mss_customer';

INSERT proxy_customer SELECT * FROM local_customer;
COMMIT;

----------------------------------------------------------------
-- Test 2b: SELECT on SQL Server

SELECT * FROM mss_customer
go

1> SELECT * FROM mss_customer
2> go
 pkey        data
 ----------- -----------
           1           1
           2           2

(2 rows affected)