Techniques?

I am somewhat new to Informix, but I have a good amount of experience working with Sybase. In my Sybase environment, I frequently used unix shell scripts and stored procedures to handle routine tasks such as moving data from one db to another, nightly data loads, transactional data loads, etc. I would do things such as load data into a temp table with the bcp command, and then execute a stored procedure via isql that would process the data in some fashion (distribute it to various tables, calculate reports etc.). A typical shell script ------------------------------------- #!/bin/csh # load data in orders.txt into the orders table bcp tempdb..orders in orders.txt ....etc. isql << EOF EXEC process_orders go EOF ------------------------------------- This works very well for almost every situation. The stored procedure has variables, logic, cursors and whatever intelligence is necessary to process the newly loaded data. While I understand and have attempted to use Informix Stored Procedures and Stored Functions, I keep running accross some severely limiting problems. The fact that I cannot simply execute a "select xxx from yyy" and have it return a list of records (to my terminal) within a stored function seems to be crippling me. In addition to that, I cannot understand why Informix would not allow me to create a cursor in SPL. I would be ok without the stored functions/procedures if I could use a .sql batch file that was capable of containing variables, logic and/or cursors, but I seem to be unable to use any of these things in batches. My questions are: Can anyone offer up some sort of techniques to do what I am trying to do? Is informix really only suited to a C/C++ and ODBC environments? Am I just not thinking clearly here? Please respond via email since my news server seems to purge posts very quickly. Thanks For Any Help!! Bob Damato Cox Target Media

Bob Damato wrote: > > I am somewhat new to Informix, but I have a good amount of experience Welcome to the fold Bob. > working with Sybase. In my Sybase environment, I frequently used unix > shell scripts and stored procedures to handle routine tasks such as > moving data from one db to another, nightly data loads, transactional > data loads, etc. I would do things such as load data into a temp table > with the bcp command, and then execute a stored procedure via isql that > would process the data in some fashion (distribute it to various tables, > calculate reports etc.). > > A typical shell script > ------------------------------------- > #!/bin/csh > > # load data in orders.txt into the orders table > bcp tempdb..orders in orders.txt ....etc. > > isql << EOF > > EXEC process_orders > go > > EOF > ------------------------------------- > > This works very well for almost every situation. The stored procedure > has variables, logic, cursors and whatever intelligence is necessary to > process the newly loaded data. > > While I understand and have attempted to use Informix Stored Procedures > and Stored Functions, I keep running accross some severely limiting > problems. The fact that I cannot simply execute a "select xxx from yyy" > and have it return a list of records (to my terminal) within a stored > function seems to be crippling me. In addition to that, I cannot Sure you can with a FOREACH or WHILE loop and a "RETURN .... WITH RESUME"! > understand why Informix would not allow me to create a cursor in SPL. I You CAN create a cursor it is just an implied cursor not the explicit kind you would create in ESQL/C or 4GL. It is created by using a FOREACH statement. > would be ok without the stored functions/procedures if I could use a > .sql batch file that was capable of containing variables, logic and/or > cursors, but I seem to be unable to use any of these things in batches. > My questions are: Can anyone offer up some sort of techniques to do what > I am trying to do? Is informix really only suited to a C/C++ and ODBC > environments? Am I just not thinking clearly here? Please respond via > email since my news server seems to purge posts very quickly. So what else is the problem? SPL is an admittedly sparse language but it is more powerful than you have given it credit for. One reason Informix never developed SPL into a full programming language as Oracle and Sybase have is that neither of the other servers had a powerful and easy to use development environment like Informix 4GL which allowed us DBAs and developers to develop even more powerful utilities even more quickly, especially with RDS and ID, than even one could with Sybase's SPL or Oracle SQL+. The Informix designer of SPL envisioned it as a way to encapsulate SQL in the server NOT write entire utilities. Before you get down on Informix -vs- Sybase remember that Sybase did not even HAVE cursor support AT ALL until 3 years ago while Informix always has. Art S. Kagel