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.

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
906611
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
40.2k361550822
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.5k5417261050
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
906611
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
906611
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: 2,220 times

last updated: 17 Aug '20, 03:04