Re: Building a SQL statement...

["Mark Allen" <scprideandms@xxxxxxxxx>]

what was it?

On Thu, Oct 30, 2008 at 3:56 PM, Mira, Antonio <antonio.mira@xxxxxxxxxxx>wrote:

> Disregard my previous post...I found the error.  Thanks
>
> Thank you,
>
> Antonio Mira
> Application Developer
> Time Warner Cable www.timewarnercable.com Mid-Ohio Division
> 1015 Olentangy River Road - 2nd Floor
> Columbus, OH 43212
> email:  antonio.mira@xxxxxxxxxxx
> aim:    antomiratwc
> phone: 614 827 7949
>
>
>
> -----Original Message-----
> From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:
> systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
> Sent: Thursday, October 30, 2008 3:41 PM
> To: .net use with the System i
> Subject: Re: [SystemiDotNet] Building a SQL statement...
>
> Walden,
>
> Thanks for your suggestion...I went with it but now I'm getting a different
> error which I don't know how to solve since the string looks ok to me but
> yet the execution of the sp complains.  Here's the string that the VB
> program builds right before executing it...
>
> "SELECT CEITM , CESER , CESTC , CESDT , CEHED FROM cohamira.CONVRPF WHERE
> CENROV = 2 and CEITM in ('SA83HDC')"
>
> This is the error that I'm getting...
>
> Message . . . . :   String constant beginning 'S       ' not delimited.
> Cause . . . . . :   The string delimiter is missing in the constant
> beginning
>  with 'S       '.  The string is treated as if it were delimited by the end
>  of the source file.
> Recovery  . . . :   Delimit the string constant.  Check for any missing or
>  extra quotation marks and apostrophes.  These errors are likely to cause
>  other errors.  Some statements may not have been processed as the result
> of
>  either missing or extra string delimiters.  Precompile the program again.
>
>
> Thank you,
>
> Antonio Mira
> Application Developer
> Time Warner Cable www.timewarnercable.com Mid-Ohio Division
> 1015 Olentangy River Road - 2nd Floor
> Columbus, OH 43212
> email:  antonio.mira@xxxxxxxxxxx
> aim:    antomiratwc
> phone: 614 827 7949
>
>
>
> -----Original Message-----
> From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:
> systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
> Sent: Thursday, October 30, 2008 2:09 PM
> To: .net use with the System i
> Subject: Re: [SystemiDotNet] Building a SQL statement...
>
> You can't get there from here... :-)
>
> The problem you have it this, if there's one value you'd want to run:
>
> Select * from myFile where field1 in (parm1)
>
> but if there were two values you'd need:
>
> Select * from myFile where field1 in (parm1, parm2)
>
> and three:
>
> Select * from myFile where field1 in (parm1, parm2, parm3)
>
> A single parm can have only the single value. It's one of the uglier
> truths of SQL. BTW, same problem exists in SQLServer. :-)
>
> I would recommend you just build the SQL statement as a text string so
> long as you trust the values you're putting in not to be subject to SQL
> injection.
>
> -Walden
>
>
> --
> Walden H Leverich III
> Tech Software
> (516) 627-3800 x3051
> WaldenL@xxxxxxxxxxxxxxx
> http://www.TechSoftInc.com
>
> Quiquid latine dictum sit altum viditur.
> (Whatever is said in Latin seems profound.)
>
>
> -----Original Message-----
> From: systemidotnet-bounces@xxxxxxxxxxxx
> [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
> Sent: Thursday, October 30, 2008 1:55 PM
> To: systemidotnet@xxxxxxxxxxxx
> Subject: [SystemiDotNet] Building a SQL statement...
>
> Hello,
>
>
>
> I'm in the middle of writing a VB app that interacts with the iSeries.
> The windows form captures some data entered by the user, builds some
> parameters, and executes a stored procedure to return a data set.  The
> problem I'm having is that one of the parameters is a list of values to
> be used in an IN clause.
>
>
>
> For example:
>
>
>
> Select * from myFile where field1 in (parm1)
>
>
>
> I build parm1 in VB as a list of values like this: 'VALUE1', 'VALUE2',
> 'VALUE3'.  I include the single quotes with the parm.  If I pass just
> one value, I do get a data set back, but when I pass several values in
> the IN expression, I don't get anything back.  Has anybody done
> something like this?  If so, how did you make it work?
>
>
>
> So far I have not had luck in getting a data set back.  I fact, I've
> been trying to call the stored procedure from iSeries navigator typing
> the list of values but I have not been able to make it work.  Can
> anybody offer any suggestions or alternatives?
>
>
>
>
>
>
>
> Thank you,
>
>
>
> Antonio Mira
>
> Application Developer
>
> Time Warner Cable www.timewarnercable.com <http://www.twc.com>  Mid-Ohio
> Division
>
> 1015 Olentangy River Road - 2nd Floor
>
> Columbus, OH 43212
>
> email:  antonio.mira@xxxxxxxxxxx <mailto:antonio.mira@xxxxxxxxxxx>
>
> aim:    antomiratwc
>
> phone: 614 827 7949
>
>
>
>
>
>
> P Go Green! Print this email only when necessary. Thank you for helping
> Time Warner Cable be environmentally responsible.
>
>
> This E-mail and any of its attachments may contain Time Warner
> Cable proprietary information, which is privileged, confidential,
> or subject to copyright belonging to Time Warner Cable. This E-mail
> is intended solely for the use of the individual or entity to which
> it is addressed. If you are not the intended recipient of this
> E-mail, you are hereby notified that any dissemination,
> distribution, copying, or action taken in relation to the contents
> of and attachments to this E-mail is strictly prohibited and may be
> unlawful. If you have received this E-mail in error, please notify
> the sender immediately and permanently delete the original and any
> copy of this E-mail and any printout.
> --
> This is the .net use with the System i (SystemiDotNet) mailing list
> To post a message email: SystemiDotNet@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/systemidotnet
> or email: SystemiDotNet-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/systemidotnet.
> --
> This is the .net use with the System i (SystemiDotNet) mailing list
> To post a message email: SystemiDotNet@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/systemidotnet
> or email: SystemiDotNet-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/systemidotnet.
> This E-mail and any of its attachments may contain Time Warner
> Cable proprietary information, which is privileged, confidential,
> or subject to copyright belonging to Time Warner Cable. This E-mail
> is intended solely for the use of the individual or entity to which
> it is addressed. If you are not the intended recipient of this
> E-mail, you are hereby notified that any dissemination,
> distribution, copying, or action taken in relation to the contents
> of and attachments to this E-mail is strictly prohibited and may be
> unlawful. If you have received this E-mail in error, please notify
> the sender immediately and permanently delete the original and any
> copy of this E-mail and any printout.
>
> --
> This is the .net use with the System i (SystemiDotNet) mailing list
> To post a message email: SystemiDotNet@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/systemidotnet
> or email: SystemiDotNet-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/systemidotnet.
> This E-mail and any of its attachments may contain Time Warner
> Cable proprietary information, which is privileged, confidential,
> or subject to copyright belonging to Time Warner Cable. This E-mail
> is intended solely for the use of the individual or entity to which
> it is addressed. If you are not the intended recipient of this
> E-mail, you are hereby notified that any dissemination,
> distribution, copying, or action taken in relation to the contents
> of and attachments to this E-mail is strictly prohibited and may be
> unlawful. If you have received this E-mail in error, please notify
> the sender immediately and permanently delete the original and any
> copy of this E-mail and any printout.
>
> --
> This is the .net use with the System i (SystemiDotNet) mailing list
> To post a message email: SystemiDotNet@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/systemidotnet
> or email: SystemiDotNet-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/systemidotnet.