Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #1811 > unrolled thread

SQL Select Execute(aArgs) with NULL parameter (Oracle)

Started byFrank Leverenz <levi37@gmail.com>
First post2011-08-12 15:10 -0700
Last post2011-08-14 08:21 -0700
Articles 5 — 4 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  SQL Select Execute(aArgs) with NULL parameter (Oracle) Frank Leverenz <levi37@gmail.com> - 2011-08-12 15:10 -0700
    Re: SQL Select Execute(aArgs) with NULL parameter (Oracle) "richard.townsendrose" <richard.townsendrose@gmail.com> - 2011-08-13 03:41 -0700
      Re: SQL Select Execute(aArgs) with NULL parameter (Oracle) Massimo Bighelli <info@arcasistemi.it> - 2011-08-20 20:42 +0200
    Re: SQL Select Execute(aArgs) with NULL parameter (Oracle) malcolm gray <malcolm.gray@ntlworld.com> - 2011-08-13 20:18 +0100
      Re: SQL Select Execute(aArgs) with NULL parameter (Oracle) Frank Leverenz <levi37@gmail.com> - 2011-08-14 08:21 -0700

#1811 — SQL Select Execute(aArgs) with NULL parameter (Oracle)

FromFrank Leverenz <levi37@gmail.com>
Date2011-08-12 15:10 -0700
SubjectSQL Select Execute(aArgs) with NULL parameter (Oracle)
Message-ID<fe50fb41-94a7-4d37-a0e8-5e65da4d9fd4@d7g2000vbv.googlegroups.com>
System:VO 2.8 sp3, Oracle 10g

example:
sSql := "select * from mytable where 'myfield' = ?"
aArgs := {"X"}
oSelect:Execute(aArgs)

This works fine except if I try to select all recs where myfield is
empty.
I tried:
aArgs := {" "}
aArgs := {""}
aArgs := {NIL}
aArgs := {NULL}
aArgs := {NULL_STRING}

but I can't get it work.
I think it's because the correct syntax (without args) is
sSql := "select * from mytable where 'myfield' is null"

Is it possible to transfer a null value as an argument?
(without changing the statement)

Frank

[toc] | [next] | [standalone]


#1814

From"richard.townsendrose" <richard.townsendrose@gmail.com>
Date2011-08-13 03:41 -0700
Message-ID<46660cb5-83a0-46eb-88fa-7621f9d77f98@p19g2000yqa.googlegroups.com>
In reply to#1811
Frank

why sql cannot get s decent syntax i dont know ...

then you wouldn't have these prpblems.

have you tried geoff's sql master ?

richard

[toc] | [prev] | [next] | [standalone]


#1860

FromMassimo Bighelli <info@arcasistemi.it>
Date2011-08-20 20:42 +0200
Message-ID<j2ov68$5a1$1@speranza.aioe.org>
In reply to#1814
 > why sql cannot get s decent syntax i dont know ...

he he ...
because it's a query language, not a programming language.

:-)

Massimo

[toc] | [prev] | [next] | [standalone]


#1816

Frommalcolm gray <malcolm.gray@ntlworld.com>
Date2011-08-13 20:18 +0100
Message-ID<A6A1q.131407$OV1.114417@newsfe29.ams2>
In reply to#1811
On 12/08/2011 23:10, Frank Leverenz wrote:
> System:VO 2.8 sp3, Oracle 10g
>
> example:
> sSql := "select * from mytable where 'myfield' = ?"
> aArgs := {"X"}
> oSelect:Execute(aArgs)
>
> This works fine except if I try to select all recs where myfield is
> empty.
> I tried:
> aArgs := {" "}
> aArgs := {""}
> aArgs := {NIL}
> aArgs := {NULL}
> aArgs := {NULL_STRING}
>
> but I can't get it work.

Well with that SQL syntax and a NULL value it is working only if it 
returns no rows - that is the rules of sql nothing is equal to null, 
even null

> I think it's because the correct syntax (without args) is
> sSql := "select * from mytable where 'myfield' is null"
>
> Is it possible to transfer a null value as an argument?
> (without changing the statement)

The nearest I can think of is

"select * from mytable where NVL("myfield",'MY NULL PLACEHOLDER') = ?"
and query for 'MY NULL PLACEHOLDER' rather than NULL if you want null - 
but I suspect that will defeat any indexes

[toc] | [prev] | [next] | [standalone]


#1818

FromFrank Leverenz <levi37@gmail.com>
Date2011-08-14 08:21 -0700
Message-ID<8809c8d8-d4d5-4a73-874b-3b245e28db1d@z7g2000vbp.googlegroups.com>
In reply to#1816
Hi Richard and Malcolm,

thanx for your answers.

Now I solved it with the PreExecute() method:
If it's an oracle connection and if a string is an empty string, I
change the statement from "= ''" to "is null"

It works

Frank

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web