|
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. |
|
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. 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;
|
|
Thanks a lot sir, i will try this |
|
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; 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
|