+ Reply to Thread
Results 1 to 5 of 5

call stored procedure from iReport

  1. call stored procedure from iReport

    Hi,

    How to create result set to hold data produced from stored
    procedure call in iReport which is part of jasper soft
    report designing tool? I got a very good example on the
    website for Oracle db; but I can't find any example in
    sybase db. iReport required the data be stored in the
    result set after the procedure is called; so iReport can get
    the data from the result set in order to
    produce a report.

    I am using ASE 12.5 Can this version support the syntax:
    select * from my_stored_procedure(). I give you a link to
    the example that iReport call sp in oracle.
    >The link is:
    >http://www.brianburridge.com/2006/06...asper-reports/

    I don't know a way in sybase how to perform the tasks in
    the example from step 4 and on.


    Thanks,

    Annie

  2. Re: call stored procedure from iReport

    You can do this in ASE by creatign a proxy table that is mapped to a stored
    procedure. Then you can select from that proxy table and insert the rows
    into a 'real' table.
    In a nutshell:

    sp_addserver loopback, null, @@servername -- you need to set this up just
    once
    go


    use my_db
    go
    create procedure p as select name, type, crdate from sysobjects
    go
    create existing table t (name varchar(30), type char(2), crdate datetime)
    external procedure at 'loopback.my_db..p'
    go

    Now, by select from table 't', you execute procedure 'p':

    1> select * from t
    2> go
    name type crdate
    ------------------------------ ---- -------------------
    sysobjects S Apr 30 2007 6:54PM
    sysindexes S Apr 30 2007 6:54PM
    syscolumns S Apr 30 2007 6:54PM
    systypes S Apr 30 2007 6:54PM
    syslogs S Apr 30 2007 6:54PM

    [...]


    There's a lot more to say about this but this is the basic idea. See the
    docs for details.

    HTH,

    Rob V.
    -------------------------------------------------------------
    Rob Verschoor

    Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
    and Replication Server 12.5 / TeamSybase

    Author of Sybase books (order online at www.sypron.nl/shop):
    "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
    "The Complete Sybase ASE Quick Reference Guide"
    "The Complete Sybase Replication Server Quick Reference Guide"

    mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
    http://www.sypron.nl
    Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
    -------------------------------------------------------------

    wrote in message news:47545426.5828.1681692777@sybase.com...
    > Hi,
    >
    > How to create result set to hold data produced from stored
    > procedure call in iReport which is part of jasper soft
    > report designing tool? I got a very good example on the
    > website for Oracle db; but I can't find any example in
    > sybase db. iReport required the data be stored in the
    > result set after the procedure is called; so iReport can get
    > the data from the result set in order to
    > produce a report.
    >
    > I am using ASE 12.5 Can this version support the syntax:
    > select * from my_stored_procedure(). I give you a link to
    > the example that iReport call sp in oracle.
    >>The link is:
    >>http://www.brianburridge.com/2006/06...asper-reports/

    > I don't know a way in sybase how to perform the tasks in
    > the example from step 4 and on.
    >
    >
    > Thanks,
    >
    > Annie




  3. Re: call stored procedure from iReport

    any idea!

    create proc p_t as
    print "Hello what is this"

    create existing table et_t (c1 char(10)) external procedure
    at "loopback.tempdb..p_t"

    select * from p_t gives me
    Hello what is this
    c1
    ----------

    (0 rows affected)

    How can i make the output of "print" statement part of the
    resultset so I can insert it into a new table? I am planning
    to replace the print cmds by simiar other cmds like dbcc's




    > You can do this in ASE by creatign a proxy table that is
    > mapped to a stored procedure. Then you can select from
    > that proxy table and insert the rows into a 'real' table.
    > In a nutshell:
    >
    > sp_addserver loopback, null, @@servername -- you need to
    > set this up just once
    > go
    >
    >
    > use my_db
    > go
    > create procedure p as select name, type, crdate from
    > sysobjects go
    > create existing table t (name varchar(30), type char(2),
    > crdate datetime) external procedure at
    > 'loopback.my_db..p' go
    >
    > Now, by select from table 't', you execute procedure 'p':
    >
    > 1> select * from t
    > 2> go
    > name type crdate
    > ------------------------------ ---- -------------------
    > sysobjects S Apr 30 2007 6:54PM
    > sysindexes S Apr 30 2007 6:54PM
    > syscolumns S Apr 30 2007 6:54PM
    > systypes S Apr 30 2007 6:54PM
    > syslogs S Apr 30 2007 6:54PM
    >
    > [...]
    >
    >
    > There's a lot more to say about this but this is the basic
    > idea. See the docs for details.
    >
    > HTH,
    >
    > Rob V.
    > ----------------------------------------------------------
    > --- Rob Verschoor
    >
    > Certified Sybase Professional DBA for ASE
    > 12.5/12.0/11.5/11.0 and Replication Server 12.5 /
    > TeamSybase
    >
    > Author of Sybase books (order online at
    > www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
    > ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
    > Reference Guide" "The Complete Sybase Replication Server
    > Quick Reference Guide"
    >
    > mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
    > http://www.sypron.nl
    > Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The
    > Netherlands
    > ----------------------------------------------------------
    > ---
    >
    > wrote in message
    > > news:47545426.5828.1681692777@sybase.com... Hi,
    > >
    > > How to create result set to hold data produced from
    > > stored procedure call in iReport which is part of jasper
    > > soft report designing tool? I got a very good example
    > > on the website for Oracle db; but I can't find any
    > > example in sybase db. iReport required the data be
    > > stored in the result set after the procedure is called;
    > > so iReport can get the data from the result set in order
    > > to produce a report.
    > >
    > > I am using ASE 12.5 Can this version support the
    > > syntax: select * from my_stored_procedure(). I give you
    > > a link to the example that iReport call sp in oracle.
    > >>The link is:
    > >>http://www.brianburridge.com/2006/06...w-to-call-stor

    > > ed-procedures-from-jasper-reports/ I don't know a way
    > > in sybase how to perform the tasks in the example from

    > step 4 and on. >
    > >
    > > Thanks,
    > >
    > > Annie

    >
    >


  4. Re: call stored procedure from iReport

    The proxy table trick works on the basis of result sets, i.e. data returned
    by 'select'. The stirng returned by 'print' is not a result set, but
    technically a 'message'. You won't see those through the proxy table.
    Replace 'print' by 'select' and try again....

    HTH,

    Rob V.
    -------------------------------------------------------------
    Rob Verschoor

    Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
    and Replication Server 12.5 / TeamSybase

    Author of Sybase books (order online at www.sypron.nl/shop):
    "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
    "The Complete Sybase ASE Quick Reference Guide"
    "The Complete Sybase Replication Server Quick Reference Guide"

    mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
    http://www.sypron.nl
    Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
    -------------------------------------------------------------

    wrote in message news:47545e38.597b.1681692777@sybase.com...
    > any idea!
    >
    > create proc p_t as
    > print "Hello what is this"
    >
    > create existing table et_t (c1 char(10)) external procedure
    > at "loopback.tempdb..p_t"
    >
    > select * from p_t gives me
    > Hello what is this
    > c1
    > ----------
    >
    > (0 rows affected)
    >
    > How can i make the output of "print" statement part of the
    > resultset so I can insert it into a new table? I am planning
    > to replace the print cmds by simiar other cmds like dbcc's
    >
    >
    >
    >
    >> You can do this in ASE by creatign a proxy table that is
    >> mapped to a stored procedure. Then you can select from
    >> that proxy table and insert the rows into a 'real' table.
    >> In a nutshell:
    >>
    >> sp_addserver loopback, null, @@servername -- you need to
    >> set this up just once
    >> go
    >>
    >>
    >> use my_db
    >> go
    >> create procedure p as select name, type, crdate from
    >> sysobjects go
    >> create existing table t (name varchar(30), type char(2),
    >> crdate datetime) external procedure at
    >> 'loopback.my_db..p' go
    >>
    >> Now, by select from table 't', you execute procedure 'p':
    >>
    >> 1> select * from t
    >> 2> go
    >> name type crdate
    >> ------------------------------ ---- -------------------
    >> sysobjects S Apr 30 2007 6:54PM
    >> sysindexes S Apr 30 2007 6:54PM
    >> syscolumns S Apr 30 2007 6:54PM
    >> systypes S Apr 30 2007 6:54PM
    >> syslogs S Apr 30 2007 6:54PM
    >>
    >> [...]
    >>
    >>
    >> There's a lot more to say about this but this is the basic
    >> idea. See the docs for details.
    >>
    >> HTH,
    >>
    >> Rob V.
    >> ----------------------------------------------------------
    >> --- Rob Verschoor
    >>
    >> Certified Sybase Professional DBA for ASE
    >> 12.5/12.0/11.5/11.0 and Replication Server 12.5 /
    >> TeamSybase
    >>
    >> Author of Sybase books (order online at
    >> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
    >> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
    >> Reference Guide" "The Complete Sybase Replication Server
    >> Quick Reference Guide"
    >>
    >> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
    >> http://www.sypron.nl
    >> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The
    >> Netherlands
    >> ----------------------------------------------------------
    >> ---
    >>
    >> wrote in message
    >> > news:47545426.5828.1681692777@sybase.com... Hi,
    >> >
    >> > How to create result set to hold data produced from
    >> > stored procedure call in iReport which is part of jasper
    >> > soft report designing tool? I got a very good example
    >> > on the website for Oracle db; but I can't find any
    >> > example in sybase db. iReport required the data be
    >> > stored in the result set after the procedure is called;
    >> > so iReport can get the data from the result set in order
    >> > to produce a report.
    >> >
    >> > I am using ASE 12.5 Can this version support the
    >> > syntax: select * from my_stored_procedure(). I give you
    >> > a link to the example that iReport call sp in oracle.
    >> >>The link is:
    >> >>http://www.brianburridge.com/2006/06...w-to-call-stor
    >> > ed-procedures-from-jasper-reports/ I don't know a way
    >> > in sybase how to perform the tasks in the example from

    >> step 4 and on. >
    >> >
    >> > Thanks,
    >> >
    >> > Annie

    >>
    >>




  5. Re: call stored procedure from iReport

    Annie He wrote:
    >
    > How to create result set to hold data produced from stored
    > procedure call


    Er ... a stored procedure can return a result set.

    > in iReport which is part of jasper soft
    > report designing tool? I got a very good example on the
    > website for Oracle db; but I can't find any example in
    > sybase db. iReport required the data be stored in the
    > result set after the procedure is called; so iReport can get
    > the data from the result set in order to
    > produce a report.
    >
    > I am using ASE 12.5 Can this version support the syntax:
    > select * from my_stored_procedure(). I give you a link to
    > the example that iReport call sp in oracle.


    Forget you ever heard of Orable when discussing ASE.
    To get a result set from a stored procedure in ASE,
    you just call the stored procedure. I.e. -

    my_stored procedure
    or
    exec my_stored_procedure

    If the iReport product can't handle this (I don't
    see why it wouldn't), then try Rob's proxy table
    suggestion.

    -am 2007

+ Reply to Thread