Problem w/VB 5, 7.23.UC6, stored procedures

Environment: HP-UX 10.20 Informix IDS 7.23.UC6 OpenLink Request Broker Version 2.7A (Release 3.0) NT WS 4.0 SP5 Visual Basic 5 OpenLink Generic 32 Bit Driver 3.01.0312 Situation: A VB program running on the NT workstation runs the code segment below. If we uncomment the code that sets lvSql to be the hardcoded DELETE statement, then the program works just fine with the sqlBeginTran and sqlCommit calls. If we try to use the EXECUTE PROCEDURE form instead, the program appears to work correctly when stepping through a line at a time, but at the end, the database table still has the row(s) that should have been deleted. If we then comment out the sqlBeginTran and sqlCommit, it works. So, to restate the problem, we can either have sqlBeginTran, sqlExec(DELETE FROM ... ), sqlCommit, or we can have sqlExec(EXECUTE PROCEDURE ... ), but we can not have sqlBeginTran, sqlExec(EXECUTE PROCEDURE ... ), sqlCommit. Any ideas why? The procedure is also included below, as well as the trace file. Thanks in advance. Mark Collins mcollins@us.dhl.com =============================== Visual Basic code snippet: > Sub DeleteFromRecongTBL() > > Dim lvDeleDate As String > Dim lvSql As String > Dim lvhStmt As Integer > > lvDeleDate$ = Format( _ > DateAdd("d", ((-1 * (mvDeleteFromReconAfterDays%))), Now), _ > "mm/dd/yyyy") > ' "yyyy-mm-dd hh:mm:ss") > > > 'lvSql = "Delete from reconcile " & _ > ' "where match_dttm is Not null and " & _ > ' "match_dttm < '" & lvDeleDate$ & "'" > > lvSql = "execute procedure reconcile_delete ('" & lvDeleDate$ & "')" > > > > 'sqlBeginTran gvHDBCDB > > lvhStmt% = sqlExec(gvHDBCDB, lvSql$) > > If (lvhStmt% > 0) Then > sqlEnd lvhStmt% > ' sqlCommit gvHDBCDB > Else > ' sqlRollBack lvhStmt% > End If > > End Sub ============================= Stored procedure definition: create procedure "informix".reconcile_delete (i_match_date date) -- returning integer; define p_sql_err integer; define p_isam_err integer; define p_nrows integer; -- -- default error handler -- for any non-trapped error, set SQL error and ISAM error -- on exception set p_sql_err, p_isam_err end exception set debug file to '/tmp/sp.trace' with append; trace on; trace i_match_date; trace extend(i_match_date, year to second); -- -- test for how many rows should be deleted -- select * from reconcile where match_dttm is not null and match_dttm < extend(date(i_match_date), year to second) into temp screw_you; trace (dbinfo('sqlca.sqlerrd1')); trace (dbinfo('sqlca.sqlerrd2')); -- -- update row with match file date/time -- delete from reconcile where match_dttm is not null and match_dttm < extend(date(i_match_date), year to second); trace (dbinfo('sqlca.sqlerrd1')); trace (dbinfo('sqlca.sqlerrd2')); -- -- check to see how many (if any) rows were deleted -- let p_nrows = dbinfo('sqlca.sqlerrd2'); if p_nrows = 0 then raise exception -746, 0, 'Unable to delete from reconcile within reconcile_delete '; end if; -- return p_nrows; end procedure; ============================== Contents of trace file /tmp/sp.trace: trace on trace expression :07/21/1999 expression:(extend i_match_date year to second) evaluates to 1999-07-21 00:00:00 trace expression :1999-07-21 00:00:00 select * from reconcile where (and (not-null match_dttm), (< match_dttm, (extend (date i_match_date) y ear to second))) into temp screw_you;expression:(dbinfo-sqlca.sqlerrd1 ) evaluates to 0 trace expression :0 expression:(dbinfo-sqlca.sqlerrd2 ) evaluates to 1 trace expression :1 delete from reconcile where (and (not-null match_dttm), (< match_dttm, (extend (date i_match_date) year to second))); expression:(dbinfo-sqlca.sqlerrd1 ) evaluates to 0 trace expression :0 expression:(dbinfo-sqlca.sqlerrd2 ) evaluates to 1 trace expression :1 expression:(dbinfo-sqlca.sqlerrd2 ) evaluates to 1 let p_nrows = 1 expression:(= p_nrows, 0) evaluates to 0 procedure reconcile_delete returned no data

This is a bug in OpenLink. It has problems with procedures that do not return values in a transaction. I assume it fails to initialise something. The solution is to have a dummy procedure that returns a dummy value, and call it every time: BeginTran Execute dummy procedure that returns a value Read dummy value Execute real procedure that does not return a value CommitTran. -- Bashar Chalabi CTL, London mcollins@us.dhl.com wrote in message <7n0697$n13$1@news.xmission.com>... > >Environment: > > HP-UX 10.20 > Informix IDS 7.23.UC6 > OpenLink Request Broker Version 2.7A (Release 3.0) > > NT WS 4.0 SP5 > Visual Basic 5 > OpenLink Generic 32 Bit Driver 3.01.0312 > > >Situation: > >A VB program running on the NT workstation runs the code segment below. If we >uncomment the code that sets lvSql to be the hardcoded DELETE statement, then the >program works just fine with the sqlBeginTran and sqlCommit calls. If we try to >use the EXECUTE PROCEDURE form instead, the program appears to work correctly when >stepping through a line at a time, but at the end, the database table still has the >row(s) that should have been deleted. If we then comment out the sqlBeginTran and >sqlCommit, it works. > >So, to restate the problem, we can either have sqlBeginTran, sqlExec(DELETE FROM >... ), sqlCommit, or we can have sqlExec(EXECUTE PROCEDURE ... ), but we can not >have sqlBeginTran, sqlExec(EXECUTE PROCEDURE ... ), sqlCommit. > >Any ideas why? The procedure is also included below, as well as the trace file. > >Thanks in advance. > > > >Mark Collins >mcollins@us.dhl.com > > >=============================== >Visual Basic code snippet: > >> Sub DeleteFromRecongTBL() >> >> Dim lvDeleDate As String >> Dim lvSql As String >> Dim lvhStmt As Integer >> >> lvDeleDate$ = Format( _ >> DateAdd("d", ((-1 * (mvDeleteFromReconAfterDays%))), >Now), _ >> "mm/dd/yyyy") >> ' "yyyy-mm-dd hh:mm:ss") >> >> >> 'lvSql = "Delete from reconcile " & _ >> ' "where match_dttm is Not null and " & _ >> ' "match_dttm < '" & lvDeleDate$ & "'" >> >> lvSql = "execute procedure reconcile_delete ('" & lvDeleDate$ & "')" >> >> >> >> 'sqlBeginTran gvHDBCDB >> >> lvhStmt% = sqlExec(gvHDBCDB, lvSql$) >> >> If (lvhStmt% > 0) Then >> sqlEnd lvhStmt% >> ' sqlCommit gvHDBCDB >> Else >> ' sqlRollBack lvhStmt% >> End If >> >> End Sub > > >============================= >Stored procedure definition: >create procedure "informix".reconcile_delete (i_match_date date) > -- returning integer; > > define p_sql_err integer; > define p_isam_err integer; > define p_nrows integer; > > -- > -- default error handler > -- for any non-trapped error, set SQL error and ISAM error > -- > on exception > set p_sql_err, p_isam_err > end exception > > set debug file to '/tmp/sp.trace' with append; > trace on; > trace i_match_date; > trace extend(i_match_date, year to second); > > -- > -- test for how many rows should be deleted > -- > select * from reconcile > where match_dttm is not null > and match_dttm < extend(date(i_match_date), year to second) > into temp screw_you;> > trace (dbinfo('sqlca.sqlerrd1')); > trace (dbinfo('sqlca.sqlerrd2')); > > -- > -- update row with match file date/time > -- > delete from reconcile > where match_dttm is not null > and match_dttm < extend(date(i_match_date), year tosecond); > > trace (dbinfo('sqlca.sqlerrd1')); > trace (dbinfo('sqlca.sqlerrd2')); > > -- > -- check to see how many (if any) rows were deleted > -- > let p_nrows = dbinfo('sqlca.sqlerrd2'); > > if p_nrows = 0 > then > raise exception -746, > 0, > 'Unable to delete from reconcile within reconcile_delete >'; > end if; > > -- return p_nrows; > >end procedure; > > > >============================== >Contents of trace file /tmp/sp.trace: >trace on > >trace expression :07/21/1999 > >expression:(extend i_match_date year to second) >evaluates to 1999-07-21 00:00:00 >trace expression :1999-07-21 00:00:00 > > >select * > from reconcile > where (and (not-null match_dttm), (< match_dttm, (extend (datei_match_date) y >ear to second))) > into temp screw_you; >expression:(dbinfo-sqlca.sqlerrd1 ) >evaluates to 0 >trace expression :0 > >expression:(dbinfo-sqlca.sqlerrd2 ) >evaluates to 1 >trace expression :1 > > >delete from reconcile > where (and (not-null match_dttm), (< match_dttm, (extend (datei_match_date) y >ear to second))); >expression:(dbinfo-sqlca.sqlerrd1 ) >evaluates to 0 >trace expression :0 > >expression:(dbinfo-sqlca.sqlerrd2 ) >evaluates to 1 >trace expression :1 > >expression:(dbinfo-sqlca.sqlerrd2 ) >evaluates to 1 >let p_nrows = 1 >expression:(= p_nrows, 0) >evaluates to 0 >procedure reconcile_delete returned no data > >