|| in 4GL and upshift in SQL
Neil Smith
one column equals a given value, and where two other columns
concatenated together equal another given string.
"select * from table_a where col_1 || col_2 = 'abcdefg'" works fine in
dbaccess, but it is not understood in 4GL. I thought that perhaps I
could just prepare the statement from a string and execute it, but that
fails on the | symbols when compiling, which is strange since it is
within a string so the compiler should not be trying to interpret it at
compile time. Does | mean something to the compiler? should I escape
it somehow?
As for uppercase, the only case conversion functions I can find in the
manuals are upshift and downshift, and these only seem to work in 4GL.
Can anyone help please. I am running out of hair to pull out.
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Patrick Stack
unable
to make it work, as well, so I worked around it using substrings. I'm not
so
sure that will help you though according to your example. Was trying to
set a 11 char SSN equal to a 9 char SSN in my where clause, so I just
divided it up. Sorry I can't help there.
What I can, hopefully, help you with is the upper case thing. In Informix
7.x
there is a char function called UPPER. So SELECT UPPER(first_name)
will pull any character string just fine in all upper case characters, and
unlike "||" it will compile in 4GL.
Hope that helps,
Patrick
Patrick Stack (pds...@mindspring.com)
Strategic Resource Company
Neil Smith wrote in message <7mvffn$upg$1...@nnrp1.deja.com>...
Jacob Salomon
I had to try it, Patrick. I got the error messsage:
select upper(username) from sysusers
674: Procedure (upper) not found.
111: ISAM error: no record found.
OK, someone on the group wrote a cumbersome (of necessity) stored
procedure to do this. But the real way to get this functionality is to
use a datablade data type for strings. Upper() and lower() are
operators on the string datablade so they can be used in SQL when
selecting a string data type. For regular character columns, upshift()
and downshift() are not SQL functions.
If you are using 4gl, and do not have the datablade or that procedure,
you can select the data as is into your variables and use upshift(), a
4GL library function. This is the reason it works in 4GL but not in
SQL.
Now, as for the || operator: For some silly reason, Informix did not
see fit to endow 4GL with an understanding of the full SQL syntax
understood by the engine. This is the reason it works in SQL but not in
4GL. (I guess Jonathan got tired of reasoning with the marketing
folks. ;-) However, you can piece together an SQL statement as a
string, including the || operators, and PREPARE it. Nearly all sql
statements in 4GL program should be prepared anyway as a matter of good
practice so PREPAREing is no big imposition.
HMmm..I wonder of dynamic SQL understands all these constructs..
You noticed the difference, Patrick, and now, hopefully, know the
reasons. Now contact Hair Club for Men and undo the damage you did
yourself! ;-)
Now, back to your claim that the compiler seemed to interpret within
the string. Sounds more like getting confused about where a quote
starts and ends. Observe:
-------------------------------------------------------
main
define sqlstr char(100)
define strbuf char(30)
define aa char(1)
database imm
let sqlstr
= "select ""Table name is "" || trim(tabname)",
" from systables",
" where tabid >= 100"
prepare stmt from sqlstr
declare foulmouth cursor for stmt
foreach foulmouth into strbuf
message strbuf attribute(reverse)
prompt "Press a key to continue" for char aa
end foreach
end main
-------------------------------------------------------
No problem at all! But note the double quotes for the quote within
quote.
HTH.
In article <7mvt3l$vcf$1...@nntp9.atl.mindspring.net>,
"Patrick Stack" <pds...@mindspring.com> wrote:
> I ran into the same problem with the concatination syntax (||). I was
> unable to make it work, as well, so I worked around it using
> substrings.
--- SNIP ---
> In Informix 7.x there is a char function called UPPER. So SELECT
> UPPER(first_name) will pull any character string just fine in all
> upper case characters, and unlike "||" it will compile in 4GL.
> Neil Smith wrote in message <7mvffn$upg$1...@nnrp1.deja.com>...
> >I want do a select in a 4GL program from a table where the uppercase
> >of one column equals a given value, and where two other columns
> >concatenated together equal another given string.
> >
> >"select * from table_a where col_1 || col_2 = 'abcdefg'" works fine
> >in dbaccess, but it is not understood in 4GL. I thought that
> >perhaps I could just prepare the statement from a string and execute
> >it, but that fails on the | symbols when compiling, which is strange
> >since it is within a string so the compiler should not be trying to
> >interpret it at compile time. Does | mean something to the
> >compiler? should I > >escape it somehow?
> >As for uppercase, the only case conversion functions I can find in
> >the manuals are upshift and downshift, and these only seem to work
> >in 4GL.
> >
> >Can anyone help please. I am running out of hair to pull out.
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
--
+---- Jacob Salomon DBA JSal...@bn.com ----------------------+
|(In perpetual pursuit of undomesticated semi-aquatic avians) |
| The expedient performance of a task with excessive concern |
| for its duration-to-completion engenders a virtual certainty |
| of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+--------------------------------------------------------------+
Rahul Dhuvad(2)
[.....]
>
> Now, as for the || operator: For some silly reason, Informix did not
> see fit to endow 4GL with an understanding of the full SQL syntax
> understood by the engine.
Informix 4GL handles[embedded(direct)] syntax of SQL only upto 4.10
engines. Then onwards 4GL is not upgraded to support new
features/grammar of SQL. But ...... read ahead .....
> This is the reason it works in SQL but not in 4GL.
TRUE.
> (I guess Jonathan got tired of reasoning with the marketing
> folks. ;-) However, you can piece together an SQL statement as a
> string, including the || operators, and PREPARE it. Nearly all sql
> statements in 4GL program should be prepared anyway as a matter of good
> practice so PREPAREing is no big imposition.
----------------------------------
Yes, PREPARing the *NEW* SQL is the way to tackle this limitation.
*****NEW*****
7.30 Tools has SQL ... END SQL block feature where one can embedd any
PREPARABLE SQL statement. So your 4GL code pasted below can then look
like :
main
define strbuf char(30)
define aa char(1)
database imm
declare foulmouth cursor for
SQL {**7.30.UC1 4GL onwards only**}
select "Table name is" || trim(tabname) from systables where tabid >=
100
END SQL
foreach foulmouth into strbuf
message strbuf attribute(reverse)
prompt "Press a key to continue" for char aa
end foreach
end main
This will make programmer's life cool as no need to define a string and
no need to prepare. Morever any syntax errors can be caught at compile
time (for c-code only, for p-code its runtime). I would like to mention
that PREPARE will NOT generate any compile time error. So one can even
write :
let sqlstr = "I can write anything"
PREPARE stmt from sqlstr
which will generate error at runtime and not at compile time.
regards,
Rahul.
-----------------------------------------