RE: Stored Procedures, Visual Basic 6 and LIKE clause

Sorry,

Actually it needs to be like this:

        BEGIN 
        declare tempvar varchar(50);
        DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS RETURNED FROM
                CLSDFILES . CLOSEDFILES WHERE BOX LIKE tempvar;
        set tempvar = '%' + criteria + '%';
        OPEN C1 ;
        END  ; 


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Adam Lang
> Sent: Tuesday, June 07, 2005 11:10 AM
> To: Midrange Systems Technical Discussion
> Subject: Stored Procedures, Visual Basic 6 and LIKE clause
> 
> 
> Hello,
> 
> I have a Visual Basic 6 program using ADO and the iSeries ODBC driver
> for getting data off the iSeries (V5R2.
> 
> I use a variety of stored procedures currently with no problem, until
> I tried doing a LIKE clause.
> 
> The stored procedure runs, but it returns a NULL value.  When I run
> the SQL in the "run scripts" window of NAvigator, it returns data,
> like it should.  I went over code a lot and I can see nothign I am
> doing different from the other stored procedures I am calling.  I am
> curious if anyone has any ideas.
> 
> Here is the definituion of the stored procedure I am calling:
> 
> CREATE PROCEDURE CLSDFILES.LAST_BOX ( 
>       IN CRITERIA VARCHAR(50) ) 
>       DYNAMIC RESULT SETS 1 
>       LANGUAGE SQL 
>       SPECIFIC CLSDFILES.LAST_BOX 
>       NOT DETERMINISTIC 
>       READS SQL DATA 
>       CALLED ON NULL INPUT 
>       BEGIN DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS
> RETURNED FROM CLSDFILES . CLOSEDFILES WHERE BOX LIKE '%CRITERIA%' ;
> OPEN C1 ; END  ;
> 
> And here is the Visual Basic code I am calling it with:
> 
> Set MyCommand = New ADODB.Command
> 
> With MyCommand
>     .ActiveConnection = MyConnection
>     .CommandText = "CLSDFILES.LAST_BOX"
>     .CommandType = adCmdStoredProc
>     .Parameters("criteria") = criteria
> End With
> 
> Set rcdset = New ADODB.Recordset
> With rcdset
>     .CursorType = adOpenStatic
>     .LockType = adLockReadOnly
>     Set .Source = MyCommand
>     .Open
> End With
> 
> If IsNull(rcdset.Fields.Item(0).Value) Then
>     MsgBox "No boxes with that search criteria."
> Else
>     MsgBox rcdset.Fields.Item(0).Value
> End If
> 
> Also of note.  When I call the SQL directly from VB, it also works
> that it is supposed to:
> 'sSQL = "SELECT max(BOX) FROM CLSDFILES . CLOSEDFILES WHERE BOX LIKE
> '%" & criteria & "%'"
> '
> 'Set rcdset = MyConnection.Execute(sSQL)
> '
> 'If IsNull(rcdset.Fields.Item(0).Value) Then
> '    MsgBox "No boxes with that search criteria."
> 'Else
> '    MsgBox rcdset.Fields.Item(0).Value
> 'End If
> -- 
> "Your Majesty has slain the mother; how can one kill the 
> child as well?"
> - Ts'ao Jui, Romance of the Three Kingdoms
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>