Sybase version 10

Exporting Query Result (Bulk Data) and saving as .DAT file using a Stored Procedure

i am trying to export the select query results(bulk data) and saving as .DAT file using a Stored Procedure.

OutPut to (ASA), Unload to (ASE) commands are working in Interactive SQL only, not able to use in Stored procedure.

I have tried xp_write_file function that is saving a single record only.

Kindly let me know any alternative commands/in build system functions in Sybase sql anywhere 10 version to export the bulk data from a select query using a Stored procedure.

asked 02 Aug '20, 07:37

Sankar's gravatar image

Sankar
905510
accept rate: 0%


Have a look at the UNLOAD statement, which operates similar to a SELECT/OUTPUT command in dbisql but can be used within stored procedures and code blocks.

permanent link

answered 02 Aug '20, 08:04

Volker%20Barth's gravatar image

Volker Barth
39.5k355539810
accept rate: 34%

Maybe it is also worth to mention, that the -gl option should also be considered while starting the DBSRV10.EXE.

By default only DBA User is permitted to execute UNLOAD Statement.

(14 Aug '20, 15:47) Baron
Replies hidden
1

Or you put the UNLOAD within a procedure owned by DBA and grant regular users the execution so they need not to be allowed to do UNLOAD generally...

(15 Aug '20, 04:13) Volker Barth

The OUTPUT statement is processed by ISQL itself, so it can't be embedded inside a stored procedure.

The SQL Anywhere 10 UNLOAD SELECT statement is documented here.

UNLOAD 
SELECT *
  FROM ttt
 ORDER BY ccc
TO 'c:/temp/ttt.txt';
-- Unload stored procedure code to a file

UNLOAD 
SELECT STRING ( 
          '---------------------------------  ',
          proc_name,
          '  ---------------------------------\x0d\x0a\x0d\x0a',
          COALESCE ( source, proc_defn ), -- if no pretty text, then ugly
          ';\x0d\x0a' )
  FROM SYSPROCEDURE
 WHERE creator NOT IN ( 0, 2, 3, 4, 5 ) -- exclude system procedures
 ORDER BY proc_name
TO 'c:/temp/procs.txt'
DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
permanent link

answered 02 Aug '20, 08:55

Breck%20Carter's gravatar image

Breck Carter
32.5k5397241050
accept rate: 20%

edited 02 Aug '20, 09:00

Thanks a lot sir, i will try this

permanent link

answered 03 Aug '20, 04:17

Sankar's gravatar image

Sankar
905510
accept rate: 0%

Hi Sir, i have tried with the below code and try to create SP but SQL Code -131 error am getting due to UNLOAD command.

Please find the below code for your reference.

CREATE PROCEDURE sp_test( ) BEGIN UNLOAD SELECT * employee TO 'C:\test.txt'; END;

permanent link

answered 03 Aug '20, 05:25

Sankar's gravatar image

Sankar
905510
accept rate: 0%

Your statement needs a proper FROM clause to be processed correctly:

CREATE PROCEDURE sp_test( ) BEGIN UNLOAD SELECT * FROM employee TO 'C:\test.txt'; END;


(03 Aug '20, 07:30) Reimer Pods
Replies hidden

Then there's SQLCODE -602 Cannot access file...

Could not execute statement.
Cannot access file 'C:\test.txt' -- Invalid argument
SQLCODE=-602, ODBC 3 State="HY000"
Line 1, column 1

UNLOAD SELECT * FROM dummy TO 'C:\test.txt'

...because writing data to C:\ is considered [cough] inappropriate :)

(03 Aug '20, 08:56) Breck Carter

One should also mask the backslash so '\t' is not treated as a tab character...

 UNLOAD SELECT * FROM dummy TO 'C:\\test.txt'

(*): Well, according to the docs '\t' will be treated as a backslash and a 't' - but I would suggest to still double backslashes in string literals just to prevent that you forget to add a backslash if you modify the file name to 'newtest.txt'... - in which case '\n' would be a new line.

(03 Aug '20, 09:32) Volker Barth
Comment Text Removed

...or use /n which works if the filespec is being handled by SQL Anywhere.

But c:/newtest.txt is still gonna crap out on the -602...

Could not execute statement.
Cannot access file 'C:/newtest.txt' -- Invalid argument
SQLCODE=-602, ODBC 3 State="HY000"
Line 1, column 1

UNLOAD SELECT * FROM dummy TO 'C:/newtest.txt'
(03 Aug '20, 11:14) Breck Carter

Hi Sir,

sorry, i have wrongly executed the previous one, Unload is working fine with SP

Thanks a lot Sankar A R

(04 Aug '20, 07:54) Sankar
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:

×18

question asked: 02 Aug '20, 07:37

question was seen: 1,579 times

last updated: 17 Aug '20, 03:04