Thread: 7.3 Cursors/ResultSets/CallableStatements
I am interested in calling a stored procedure through a CallableStatement and fetching a ResultSet a few rows at a time. I have seen a few messages suggesting all the pieces may be in place for doing this kind of thing nicely in 7.3. Any idea when it will become clear what will actually go into 7.3? Larry
Larry, The jdbc driver will not support returning result sets from callableStatements in 7.3. You will need to use a regular Statement/Prepared Statement object and execute a 'select * from func()' sql statement. Off the top of my head, I'm not sure how the jdbc callableStatement syntax is supposed to handle resultsets. Dave, correct me if I am wrong here. thanks, --Barry Larry Riedel wrote: > I am interested in calling a stored procedure through > a CallableStatement and fetching a ResultSet a > few rows at a time. I have seen a few messages > suggesting all the pieces may be in place for doing > this kind of thing nicely in 7.3. Any idea when it > will become clear what will actually go into 7.3? > > > Larry > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > > >
Barry, I think notionally the idea would be to call a stored proc which returned a cursor, and when result set's supported cursors this would be feasible. I'm still not sure how we tell if the stored proc returned a cursor? Dave On Mon, 2002-08-26 at 07:34, Barry Lind wrote: > Larry, > > The jdbc driver will not support returning result sets from > callableStatements in 7.3. You will need to use a regular > Statement/Prepared Statement object and execute a 'select * from func()' > sql statement. Off the top of my head, I'm not sure how the jdbc > callableStatement syntax is supposed to handle resultsets. > > Dave, correct me if I am wrong here. > > thanks, > --Barry > > > Larry Riedel wrote: > > > I am interested in calling a stored procedure through > > a CallableStatement and fetching a ResultSet a > > few rows at a time. I have seen a few messages > > suggesting all the pieces may be in place for doing > > this kind of thing nicely in 7.3. Any idea when it > > will become clear what will actually go into 7.3? > > > > > > Larry > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
Dave, We need to use a different syntax for how we build the query in 7.3 than in 7.2 if we want to support functions returning resultsets. Currently the code generates a query that looks like the following: select func(); in 7.3 that becomes: select * from func(); If the function is a regular function the results of the two queries are the same. However in 7.3 if the function returns a resultset you will get back the resultset of the function instead of a result set that contains one row and one column with the return value of the function. thanks, --Barry Dave Cramer wrote: >Barry, > >I think notionally the idea would be to call a stored proc which >returned a cursor, and when result set's supported cursors this would be >feasible. I'm still not sure how we tell if the stored proc returned a >cursor? > >Dave >On Mon, 2002-08-26 at 07:34, Barry Lind wrote: > > >>Larry, >> >>The jdbc driver will not support returning result sets from >>callableStatements in 7.3. You will need to use a regular >>Statement/Prepared Statement object and execute a 'select * from func()' >>sql statement. Off the top of my head, I'm not sure how the jdbc >>callableStatement syntax is supposed to handle resultsets. >> >>Dave, correct me if I am wrong here. >> >>thanks, >>--Barry >> >> >>Larry Riedel wrote: >> >> >> >>>I am interested in calling a stored procedure through >>>a CallableStatement and fetching a ResultSet a >>>few rows at a time. I have seen a few messages >>>suggesting all the pieces may be in place for doing >>>this kind of thing nicely in 7.3. Any idea when it >>>will become clear what will actually go into 7.3? >>> >>> >>>Larry >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >>> >>> >>> >>> >>> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> >> >> >> > > > > > >
Dave Cramer wrote: > Barry, > > I think notionally the idea would be to call a stored proc which > returned a cursor, and when result set's supported cursors this would be > feasible. I'm still not sure how we tell if the stored proc returned a > cursor? The function is defined as returning a REFCURSOR, right? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073