I'm having a problem getting back a result set using a DBPROC. If I
process the SQL that comprises the DBPROC in SQLStudio, the resultset
has 3 rows of data (like I would expect), yet when I create the DBPROC
and execute it, I get back an empty resultset. My suspicion is that the
parameters is getting corrupted in being passed into the SQL engine. I
hope this is not too confusing...
*My code that calls the DBProc*
sp_stmt = 'call SP_RPT_OPTIMIZER_RM_BY_CUST(?)'
prepared = self.session.prepare(sp_stmt)
values = [cn]
print cn
result = prepared.execute(values)
#print result
#if result[0] == 'Unexpected Error':
# return -1
#else:
# should be a SapDB_ResultSet
print "\tresult: '%s', %s" % (result , type (result))
descriptions = result.getDescription ()
for name, typeName, typeCode, colLength, colPercision,
isNullable, inout in descriptions:
print name, typeName, typeCode, colLength, colPercision,
isNullable, inout
row = result.next()
#print row[0]
while row:
print row
# returns:
# symbol, name, entry_date, entry_price shares %
finalResults.append([row[0],row[1],row[2],row[3],row[4]])
row = result.next ()
return finalResults
*My results*
4690
result: '<SapDB_ResultSet object at 0x823cd58>', <type
'SapDB_ResultSet'>
symbol Char 1 10 0 None OUT
name Char 1 40 0 1 OUT
entry_date Date 91 10 0 1 OUT
entry_price Fixed 3 18 7 1 OUT
rm_shares Fixed 3 9 5 1 OUT
('-.1', 'double')
('.2', 'double')
('1', 'double')
('.5', 'double')
*Yet if I pass in the customernumber 4690,*
I don't assign it using the parameter of the DBProc, instead I hard
code it, the query works fine and returns data (3 rows).
the DBProc (hardcoded)
create dbproc SP_RPT_OPTIMIZER_RM_BY_CUST (IN CUSTOMERNUM INT)
RETURNS CURSOR AS
$CURSOR = 'RM_BY_CUST' ;
DECLARE :$CURSOR CURSOR FOR
select SI."symbol", SI."name", DATE(VCTH."entry_date") "entry_date",
VCTH."entry_price", RM."rm_shares" from TIMEWISE_DBA."stock_info"
SI, TIMEWISE_DBA."v_curr_tradepointHistory" VCTH,
TIMEWISE_DBA."RM_analysis" RM where SI."symbol" = VCTH."symbol" and
RM."symbol" = SI."symbol"
and DATE(VCTH."entry_date") in (select "issue_date" from
TIMEWISE_DBA."v_max_date")
and RM."customerNumber" = 4690;
Press CTRL+C to abort or let the system start in 2 seconds
4690
4690
result: '<SapDB_ResultSet object at 0x80d8688>', <type
'SapDB_ResultSet'>
symbol Char 1 10 0 None OUT
name Char 1 40 0 1 OUT
entry_date Date 91 10 0 1 OUT
entry_price Fixed 3 18 7 1 OUT
rm_shares Fixed 3 9 5 1 OUT
*Data is here:*
('RYCAX', 'Rydex Consumer Products Adv', '20021212',
7.3099999999999996, 1000.0)
('RYCIX', 'Rydex Consumer Products Inv', '20021212',
7.4599999999999991, 1000.0)
('RYGBX', 'Rydex U.S. Government Bond', '20021212',
10.809999999999999, 1000.0)
*Worst behavior,*
If I change the definition of the first parameter to be INOUT
instead of just IN, here is the output of the call:
sql.SQLError: sql.SQLError: [-4024] (at 1) Row not found
*Even More bizare behavior*
Needing to get this stuff done, I decided screw it and don't use a
DBProc and cursor, just use the SQL. I put the SQL into my code and
I get parsing errors. Mind you I don't get these errors if I
execute the query under SQLStudio.
*the SQL*
stmt = 'select SI."symbol", SI."name", DATE(VCTH."entry_date")
"entry_date", VCTH."entry_price", RM."rm_shares" from
TIMEWISE_DBA."stock_info" SI,
TIMEWISE_DBA."v_curr_tradepointHistory" VCTH,
TIMEWISE_DBA."RM_analysis" RM where SI."symbol" = VCTH."symbol"
and RM."symbol" = SI."symbol" and RM."customerNumber" = ? and
DATE(VCTH."entry_date") in (select "issue_date" from
TIMEWISE_DBA."v_max_date")'
*the Results*
sql.SQLError: sql.SQLError: [-8] (at 1) Execution failed, parse
again
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
