Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

hboid
46115
accept rate: 0%

edited 30 Jul '12, 05:43

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050


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'

permanent link

answered 30 Jul '12, 10:13

JBSchueler's gravatar image

JBSchueler
3.3k41564
accept rate: 19%

edited 30 Jul '12, 10:14

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.


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)

permanent link

answered 30 Jul '12, 06:56

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 30 Jul '12, 10:37

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.

permanent link

answered 01 Aug '12, 07:09

hboid's gravatar image

hboid
46115
accept rate: 0%

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

×145
×105
×16

question asked: 30 Jul '12, 04:18

question was seen: 4,411 times

last updated: 01 Aug '12, 08:50