RE: [Info-ingres] not possible to return values from stored procedure using ingresdbi for python

Hi,

It is possible if you a looking at a procedure that returns rows.

Take a look at the following example -
http://ingres.ca.com/pipermail/users...er/000022.html

Regards,

grant


> -----Original Message-----
> From: info-ingres-admin-at-cariboulake.com [mailto:info-ingres-
> admin-at-cariboulake.com] On Behalf Of morgan brickley
> Sent: Tuesday, August 02, 2005 4:11 PM
> To: info-ingres-at-cariboulake.com
> Subject: [Info-ingres] not possible to return values from stored
procedure
> using ingresdbi for python
>
> Hey all,
>
> Am I right in stating that it is not possible to retrieve results from
a
> stored procedure in ingres to a python script using thhe ingresdbi
driver?
>
> The obvious work-around is to follow up the stored proc with a direct
> SELECT statement, but this seems inefficient.
>
> So you CAN do this :
>
> exeStr = "execute procedure team_create(in_userId=" + in_userId
> +",in_teamName='" + in_teamName + "')";
>
> c.execute( exeStr )
>
> exeStr = "SELECT teamID FROM Teams WHERE (teamname='" +
> in_teamName +
> "')";
>
> c.execute( exeStr )
> rows = c.fetchall()
> newTeamId = rows[0][0]
>
> but you CAN'T do this :
>
> exeStr = "execute procedure team_create_with_return(in_userId=" +
> in_userId +",in_teamName='" + in_teamName + "')";
>
> c.execute( exeStr )
> rows = c.fetchall()
> newTeamId = rows[0][0]
>
> nor can you use fetchone() here, and nope it doesn't matter whether
the
> stored proc itself actually returns a single value using 'return' or
> uses the iterator style 'result row' and the 'for ... end for' method
of
> returning values..
>
> Should we be able to do this ?
>
> Morgan
>
>
> _______________________________________________
> Info-ingres mailing list
> Info-ingres-at-cariboulake.com
> http://mailman.cariboulake.com/mailm...py/info-ingres
>

Thanks Grant, the link shows the syntax I was looking for...Just for
others searching for this the syntax for accessing a single variable and
multiple variable stroed proc is as follows:

def usr_logon_read_test( self, in_userName ):
exeStr = "{call usr_logon_read_test(?)}"
self.c.execute(exeStr, {"in_username": in_userName})
newid = self.c.fetchone()
print newid

def usr_logon_read_test2( self, in_userName, in_password ):
exeStr = "{call usr_logon_read_test2(?,?)}"
self.c.execute(exeStr, {"in_username": in_userName, "in_password":
in_password})
rows = self.c.fetchall()
newid = rows[0][0]
print newid

the stored proc's should use the for...result row(..) ... enfor syntax
to return values


> Hi,
>
> It is possible if you a looking at a procedure that returns rows.
>
> Take a look at the following example -
> http://ingres.ca.com/pipermail/users...er/000022.html
>
> Regards,
>
> grant
>
>
>
>>-----Original Message-----
>>From: info-ingres-admin-at-cariboulake.com [mailto:info-ingres-
>>admin-at-cariboulake.com] On Behalf Of morgan brickley
>>Sent: Tuesday, August 02, 2005 4:11 PM
>>To: info-ingres-at-cariboulake.com
>>Subject: [Info-ingres] not possible to return values from stored
>
> procedure
>
>>using ingresdbi for python
>>
>>Hey all,
>>
>>Am I right in stating that it is not possible to retrieve results from
>
> a
>
>>stored procedure in ingres to a python script using thhe ingresdbi
>
> driver?
>
>>The obvious work-around is to follow up the stored proc with a direct
>>SELECT statement, but this seems inefficient.
>>
>>So you CAN do this :
>>
>>exeStr = "execute procedure team_create(in_userId=" + in_userId
>>+",in_teamName='" + in_teamName + "')";
>>
>> c.execute( exeStr )
>>
>> exeStr = "SELECT teamID FROM Teams WHERE (teamname='" +
>>in_teamName +
>>"')";
>>
>> c.execute( exeStr )
>> rows = c.fetchall()
>> newTeamId = rows[0][0]
>>
>>but you CAN'T do this :
>>
>>exeStr = "execute procedure team_create_with_return(in_userId=" +
>>in_userId +",in_teamName='" + in_teamName + "')";
>>
>> c.execute( exeStr )
>> rows = c.fetchall()
>> newTeamId = rows[0][0]
>>
>>nor can you use fetchone() here, and nope it doesn't matter whether
>
> the
>
>>stored proc itself actually returns a single value using 'return' or
>>uses the iterator style 'result row' and the 'for ... end for' method
>
> of
>
>>returning values..
>>
>>Should we be able to do this ?
>>
>>Morgan
>>
>>
>>_______________________________________________
>>Info-ingres mailing list
>>Info-ingres-at-cariboulake.com
>>http://mailman.cariboulake.com/mailm...py/info-ingres
>>
>
>
>
>