RE: Stored Procedures, Visual Basic 6 and LIKE clause

Sorry,

Mixing RPG IV and SQL again :-)

        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 = '%' concat criteria concat '%';
        OPEN C1 ;
        END  ; 

Also, unless the criteria field is VARCHAR, you'd want to use the TRIM function.

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: Thursday, June 09, 2005 1:41 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Stored Procedures, Visual Basic 6 and LIKE clause
> 
> 
> Same error as other statement.  Invalid use of +
> 
> On 6/9/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> > 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.
> > >
> > >
> > 
> > --
> > 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.
> > 
> > 
> 
> 
> -- 
> "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.
> 
>