Re: Stored Procedures, Visual Basic 6 and LIKE clause

There we go.  That did it.  Thanks much for the help everyone.

On 6/9/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> 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.
> >
> >
> 
> --
> 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.
> 
>