Sybase automated export

Classic Servoy
1

Can the following be run from a bat file or using the rawSQL plugin?

SELECT *
FROM contacts;
OUTPUT TO contacts.txt
FORMAT ASCII

If anyone can provide sample code for doing so with Sybase it would be greatly appeciated.

2

Westy:
Can the following be run from a bat file or using the rawSQL plugin?

SELECT *

FROM contacts;
OUTPUT TO contacts.txt
FORMAT ASCII



If anyone can provide sample code for doing so with Sybase it would be greatly appeciated.

You can use the rawSQL plugin, just double check if this SQL syntax is right for Sybase and keep in mind that the file will be created on the server and not on the client.

3

ngervasi:
You can use the rawSQL plugin…

I am new to the rawSQL plugin. Is the following the correct syntax for putting the above multi-line statement on one line?

"SELECT * FROM contacts; OUTPUT TO contacts.txt FORMAT ASCII"

In other words, is it just a matter of inserting the “;” whenever a line break is required?

4

SQL ignores line-breaks, it treats them as spaces. And a semicolon is used when you want to execute your sql.
So the following SQL are in fact 2 request send to the database:

SELECT * FROM contacts; OUTPUT TO contacts.txt FORMAT ASCII;

Hope this helps.

5

I am not sure what the rawsql plugin is, but based on your commands, it looks like you are using the dbisql utility. The OUTPUT statement is a DBISQL only statement.
You can run dbisql in batch mode to do what you want:
eg. dbisql -c “connectstring” -nogui myfile.sql
The file would contain the SQL statements you want to execute.

If you are on the same machine as the server, you could use the “UNLOAD SELECT” statement instead. This would probably work better from the “rawsql” tool.
eg. UNLOAD SELECT * FROM contacts to ‘filename’ format ASCII;

6

jhinsperger:
You can run dbisql in batch mode to do what you want:
eg. dbisql -c “connectstring” -nogui myfile.sql

In the “C:\Program Files\Sybase\SQL Anywhere 9\win32” folder I created a executeSQL.bat file containing:

dbisql -c "uid=DBA;pwd=SQL" -nogui myfile.sql

In the same folder I created a myfile.sql file containing:

UNLOAD SELECT * 
FROM contacts
TO 'c:\temp\test.csv'
FORMAT ASCII

When I run the executeSQL.bat file I get the following error:

Table ‘contacts’ not found

What should I be doing to help it find the ‘contacts’ table?

Dean Westover
Choices Software, Inc.

7

From the top of my head: try to prepend the name of the database like this:

SELECT * 
FROM myDBname.contacts; 
OUTPUT TO contacts.txt 
FORMAT ASCII
8

Here are a few things to check:

  1. Is the DBA user the owner of the contacts table? If not, you need to prepend the table name with the owner name. (eg. owner.contacts). To find the owner, log in via dbisql and execute:
    SELECT table_name, user_name FROM SYSTABLE KEY JOIN SYSUSER ON SYSTABLE.creator = SYSUSER.user_id WHERE SYSTABLE.table_name=‘contacts’

  2. Are you connecting to the right database? Your dbisql commandline only lists a UID/PWD and not a server name. This means you will connect to the first database server that was started on your local machine. To ensure you get connected to the right server, you should include the “eng” parameter. eg. uid=dba;pwd=sql;eng=myeng

  3. As an extension to point 2), you can have multiple databases running on a single server. If you do, specifying uid=dba;pwd=sql will attempt to connect only to the first database that was started on the server. To ensure you are connecting to the right one, specify the dbn parameter: eg. uid=dba;pwd=sql;eng=myengine;dbn=mydatabase

If you have only one server running with one database, points 2 and 3 will make no difference.