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 asked 30 Jul '12, 04:18 hboid Breck Carter |
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' answered 30 Jul '12, 10:13 JBSchueler Excellent catch! The "double quotes" used in the Help example do indeed NOT work :)
(30 Jul '12, 10:28)
Breck Carter
Yes i tried that too, and also: OUTPUT USING 'DSN=mydsn'; | OUTPUT USING DSN='mydsn'; But always the same result, thx anyway
(01 Aug '12, 07:04)
hboid
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).
(01 Aug '12, 08:50)
JBSchueler
|
Caveat Emptor: The following code examples use Microsoft SQL Server, not MySQL.
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) answered 30 Jul '12, 06:56 Breck Carter |
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. answered 01 Aug '12, 07:09 hboid 3
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.
(01 Aug '12, 08:42)
JBSchueler
|