Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #1811 > unrolled thread
| Started by | Frank Leverenz <levi37@gmail.com> |
|---|---|
| First post | 2011-08-12 15:10 -0700 |
| Last post | 2011-08-14 08:21 -0700 |
| Articles | 5 — 4 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | Frank Leverenz <levi37@gmail.com> |
|---|---|
| Date | 2011-08-12 15:10 -0700 |
| Subject | SQL 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]
| From | "richard.townsendrose" <richard.townsendrose@gmail.com> |
|---|---|
| Date | 2011-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]
| From | Massimo Bighelli <info@arcasistemi.it> |
|---|---|
| Date | 2011-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]
| From | malcolm gray <malcolm.gray@ntlworld.com> |
|---|---|
| Date | 2011-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]
| From | Frank Leverenz <levi37@gmail.com> |
|---|---|
| Date | 2011-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