[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2481
  • Last Modified:

Sybase Output to variable filename

Hi There,

I am trying to use the Sybase Output to command to output a query to a text file.  I would like to be able to replace the filename with a variable.

In the end I would like to have filenames of Export20081128.csv, Export 20081129.csv, ect.

Thanks,
0
dneelin
Asked:
dneelin
  • 6
  • 4
  • 3
1 Solution
 
grant300Commented:
The OUTPUT clause is an ISQL feature, not part of the database server itself.  As such, you cannot use it from a stored procedure or in a SQL block where you declare a variable because ISQL will have no way of resolving the variable to a value in it's own context.

If you want files on the client side of things, you have a couple of choices.  You can script the ISQL commands and execute them or you can use the BCP utility to bulk copy the data out.

If you want the files on the sever, you can use the UNLOAD TABLE command.  This is, unfortunately, kind of heavy handed as it will unload the entire table.  You might create a view and then do the UNLOAD TABLE against the view.  I have not tried it in ASA but it works for BCP in ASE so I suspect it may well work in ASA for UNLOAD TABLE.  Give it a try and let us know if it works.

Regards,
Bill


0
 
dneelinAuthor Commented:
Hi Grant,

I am using an ISQL script file that is why I opted for the the Output To...  It seems much simpler than the UNLOAD TABLE.  This is also a Third Party database, so I don't want to go messing it up with new views and such.

Thanks for the ideas!
0
 
IncisiveOneCommented:
1  Write your SQL query and put in in example.sql
2  Write a shell script to invoke isql, Use a variable for the filename:
OUTFILE=example.out
isql -U -S -P -iexample.sql -o$OUTFILE
3  Your report (Sybase query output) is in example.out
4  Play with the date function, etc, to modify the variable $OUTFILE

It will not be csv format, it is straight output text as per running the query under isql.

Probably the more important question is, what are you going to use the output file for.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
grant300Commented:
Adding views is not a problem.  As long as you make the name(s) unique enough that there won't be a collision next time you do an update to the software.

With SQL Anywhere, I am assuming you are on Windblows so you have very limited scripting capabilities.  There are several solutions, all involve adding a better scripting language.  You can look at WinBash and PowerScript, either of which will give you the tools you need to build decent scripts.

Do NOT use the -o$OUTFILE technique as you will get nothing useful.  It just does a stream capture on the output and will not give you a CSV fiile.  A view and BCP or UNLOAD TABLE is a much stronger way to go I think.

I am with IncisiveOne though and am curious what you are going to do with the files after you get them.

Regards,
Bill
0
 
dneelinAuthor Commented:
Thanks Bill,

The -o$OUTPUT is not going to work because I need the CSV formatting.

The files are an export from one database into SAP.  My client does not want to give the other system direct access to the database so we are passing CSV files around.  The SAP programmers have specific formats for the filenames and how the data is layed out in the file.

I have created a solution where I output the file to a standard name, and then rename it via a batch file.  This works okay, but adds another layer of support and is kind of kludgy.  I will revisit the UNLOAD TABLE solution.

Thanks,


Don
0
 
IncisiveOneCommented:
SAP is disgusting, but I have done this scores of times.

bcp-out with -c option.  That will give you commas as field delimiters and cr/lf as row delimiters, but you can change that via command line parms.  Use a view to construct/filter the content, and bcp-out the view.  Read the Utility Guide.

For something more elaborate, read the instructions on creating a Format File in bcp.  You do this once, and re-use it.
0
 
grant300Commented:
bcp is really the right way to go.  Your problem using it has more to do with the next-to-non-existent Windows scripting capabilities.

As I said before, you will have much better luck and an easier time getting the job done if you use something like WINbash or PowerScript.

Regards,
Bill
0
 
IncisiveOneCommented:
I know nothing re WINbash, little exp on PowerScript.

There is also a GNU-licensed suite which allows full unix capability on Windoze.  I normally do not touch it but I did once write a small subsystem to pull data out of a Sybase database (the target, working storage, scripts had to reside on Windaze), and I used sybperl and bcp without event.  I cannot for the life of me remember the name of the package.  I understand there are a few out now.
0
 
grant300Commented:
Cygwin.  It is pretty much a complete linux/unix environment running under Window$ for free.  All the tools and utilities you would expect of a full distro are there.  There is even an X-Windows environment so you don't have to pay for Huminbird anymore.  Very nice stuff.

Regards,
Bill
0
 
dneelinAuthor Commented:
Hi Bill,

I have got it working.  It was easier than I thought.  I didn't have to make a View.  You can unload Select statements.  So I just put UNLOAD in front of Select and then took out the semi-colon and OUTPUT and presto everything worked!

I just have to tweak the filename now.

Thanks for your help!

Don
0
 
grant300Commented:
Cool.  That's a neat trick that is not documented, at least not in the UNLOAD syntax in the reference manual.  I will have to remember that one.

Regards,
Bill
0
 
dneelinAuthor Commented:
It is documented on the SyBase online docs for v11. I was surprised that it worked on 9.0.2 becasue the QUOTE doesn't.
0
 
grant300Commented:
I was looking at the 9.0.2 doc.  Seems the features are out in front of the docs or it was untested and intentionally left out of the docs in v9.

Oh well.  Another win for the good guys.

Bill
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now