Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    5

    Unanswered: record set as parameter

    Hi All,

    First of all I am very new to the Sybase arena. During our development of Stored procedures I came across the issue mentioned below.
    For one of our requirement , we need to pass set of records from one procedure to another.
    As of now I have though of the following options..

    i.Create a Temporary table in the Sybase Temp database and put the data from SourceDB into it, Later the TargetDB procedure will fetch the records from this table to do further processing.

    ii. Create some object types and store the data into it in the SourceDB. Create same object type in the TargetDB and get the data from the Objects.

    iii.Create a String variable from SourceDB and pass the records in such a fashion (like”,” for Column separator and “|” for Row Separator), which will be taken care by the TargetDB by converting it into a session specific #temp-table.

    I am preferring option ii, but not getting any proper guidance on how to achieve that.

    What is the best possible solution for the same? Any help is much appreciated.

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    Let me see if I got it right:

    SourceDB -> No procedure is changing data ( no busines, you just need the table has it is) ?

    TargetDB -> Run a proc, using as base the SourceDB..table(s) ( no pro is changing data physically)

    If this is the case and the DBs are on the same server, which it seems to be the true by your description.

    Why don't have 1 procedure that does all the work?
    Why have data duplicated in 2 Dbs, unless you will enrich the data (updates or deletes or inserts) otherwise no point, just run 1 proc that gives the output desired.

    You can be on DB TEST1 and select,insert,update,etc a table in DB TEST2, as long the user exists and has the permissions in both DBs.


    If you will enrich data, then yes create an intermediate table to hold data. Here you just have to be carefull choosing tempdb or physical table, if you want data to be persisten or tempdb is small for the volume you will be handling choose to create a physical table, if not go with tempdb.

  3. #3
    Join Date
    Jan 2014
    Posts
    5

    RecordSet as Parameter

    Hi Catarrunas,
    Thanks for your reply. I feel I am not clear enough to make you understand the requirement. Here it goes...

    We have two database and a middle tier application in between. From SourceDB we have to pass the record sets to the middle tier and from Middle tier the same record sets will be passed to the TargetDB. The targetDb then will do some manipulation and do the implementation of the business logic. Hence I am looking a way to pass a complete recordset which can hold 1.. n number of rows as a out parameter from the SourceDB and get the same sets as a IN Parameter in to the targetDB.

    Could you please let me know if this is possible in Sybase? If yes, what are the steps to be followed?

  4. #4
    Join Date
    Jan 2014
    Posts
    5

    recordsets as parameters

    Hi Catarrunas,
    Thanks for your response. I feel there is a misunderstanding in the requirement part of the Query because I have missed some key points.
    The Sybase database SOURCE and TARGET are connected by each other through a middle tier layer created in Java. So in my work , what I am supposed to do is to fetch some recordsets from the Source DB and the Middle Tier will accept that and they are going to pass the same recordsets to the TARGET DB for the next steps of processing.
    I am not aware, how the middle tier will handle the recordsets and pass it to the TARGET db, what my concern is, in the Sybase Database if we can pass the recordsets as parameters. We can define the recordset structure in both the databases as objects and create a variable of the same to use the same in both the places (procedures). Moreover as of now, we can’t use any common databases, because the SOURCE and TARGET might be in the different servers. So we need to do something programmatically if possible.
    Please guide me on the same.
    Thanks

  5. #5
    Join Date
    Jan 2014
    Posts
    5
    Hi Catarrunas,
    Thanks for your response. I feel there is a misunderstanding in the requirement part of the Query because I have missed some key points.
    The Sybase database SOURCE and TARGET are connected by each other through a middle tier layer created in Java. So in my work , what I am supposed to do is to fetch some recordsets from the Source DB and the Middle Tier will accept that and they are going to pass the same recordsets to the TARGET DB for the next steps of processing.
    I am not aware, how the middle tier will handle the recordsets and pass it to the TARGET db, what my concern is, in the Sybase Database if we can pass the recordsets as parameters. We can define the recordset structure in both the databases as objects and create a variable of the same to use the same in both the places (procedures). Moreover as of now, we can’t use any common databases, because the SOURCE and TARGET might be in the different servers. So we need to do something programmatically if possible.
    Please guide me on the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •