error 201 when using functional index
An I4GL 7.32 application on IDS 10.FC9 (AIX) started failing with error -201 only after a functional index (on a UDR) was created with DB-Access; the same queries ran fine in DB-Access. Art Kagel first explained that the 4GL parser doesn't understand functional-index syntax (prepare it as a string or use SQL...END SQL), then suggested avoiding SELECT * / LIKE-based record definitions. Jonathan Leffler suggested SQLIDEBUG=2:prefix plus sqliprint to compare client/server traffic; this showed the real first error was -206/-111 (table edc_det not found) followed by the -201. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good afternoon, we are having a rare problem when we are using a functional
index. There is a 4GL which gives error 201 when there is create a functional
index.
We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
The 4GL application get broken if this functional index is created:
create table "informix".edcve
(
cod1 decimal(8,0) not null ,
cod2 decimal(4,0) not null
);
CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
WITH (NOT VARIANT);
RETURN cod1 ;
END FUNCTION;
create index ix_edcve on edcve(f_test1(cod1));
update statistics for table edcve;
Debugging and using the onmode -I we have found the 4GL stops with error 201
when this sql is executed:
select * from edc_det where cod_emp =? and cod_pto =? and num_edc =? order bycla_ent desc
When this index is not created the 4GL does not get broken.
Before this sql statement the 4GL querys the table in the following way:
select * from edcdev where cod1=48
Both queries dont get broken if are executed in dbaccess.
Anyone knows why is this happening??
Thanks a lot in advanced.
The 4GL compiler syntax checks all SQL and it's parser doesn't understand =
functional indexes. It only supports 7.3x supported syntax. You can prepa=
re and execute the create index as a string or include it in a BEGIN SQL...=
END SQL block to bypass the parser.
Art=20
-----Original Message-----
From: LYNKZ MIKE <yellr@telecom.com.co>
Sent: Thursday, November 26, 2009 3:40 PM
To: ids@iiug.org
Subject: error 201 when using functional index [18210]
Good afternoon, we are having a rare problem when we are using a functional=
=20
index. There is a 4GL which gives error 201 when there is create a function=
al=20
index.=20
We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32=20
The 4GL application get broken if this functional index is created:=20
create table "informix".edcve=20
(=20
cod1 decimal(8,0) not null ,=20
cod2 decimal(4,0) not null=20
);=20
CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)=20
WITH (NOT VARIANT);=20
RETURN cod1 ;=20
END FUNCTION;=20
create index ix_edcve on edcve(f_test1(cod1));=20
update statistics for table edcve;=20
Debugging and using the onmode -I we have found the 4GL stops with error 20=
1=20
when this sql is executed:=20
select * from edc_det where cod_emp =3D? and cod_pto =3D? and num_edc =3D? =
order by=20cla_ent desc=20
When this index is not created the 4GL does not get broken.=20
Before this sql statement the 4GL querys the table in the following way:=20
select * from edcdev where cod1=3D48=20
Both queries dont get broken if are executed in dbaccess.=20
Anyone knows why is this happening??=20
Thanks a lot in advanced.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hi Art, thanks a lot for response, i dont understand well, The 4GL application
cant run queries on table with functional indexes??
we create the functional indexes using dbaccess just once.
Thanks a lot.
The 4GL compiler syntax checks all SQL and it's parser doesn't understand =
functional indexes. It only supports 7.3x supported syntax. You can prepa=
re and execute the create index as a string or include it in a BEGIN SQL...=
END SQL block to bypass the parser.
Art=20
Subject: error 201 when using functional index [18210]
Good afternoon, we are having a rare problem when we are using a functional=
=20
index. There is a 4GL which gives error 201 when there is create a function=
al=20
index.=20
We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32=20
The 4GL application get broken if this functional index is created:=20
create table "informix".edcve=20
(=20
cod1 decimal(8,0) not null ,=20
cod2 decimal(4,0) not null=20
);=20
CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)=20
WITH (NOT VARIANT);=20
RETURN cod1 ;=20
END FUNCTION;=20
create index ix_edcve on edcve(f_test1(cod1));=20
update statistics for table edcve;=20
Debugging and using the onmode -I we have found the 4GL stops with error 20=
1=20
when this sql is executed:=20
select * from edc_det where cod_emp =3D? and cod_pto =3D? and num_edc =3D? =
order by=20cla_ent desc=20
When this index is not created the 4GL does not get broken.=20
Before this sql statement the 4GL querys the table in the following way:=20
select * from edcdev where cod1=3D48=20
Both queries dont get broken if are executed in dbaccess.=20
Anyone knows why is this happening??=20
Thanks a lot in advanced.=20
***************************************************************************=
****=20
The problem is that I4GL, like ISQL, thinks that statements end at
semi-colons, and functions are created with multiple semi-colons. You can't
do CREATE FUNCTION like that in I4GL (or, indeed, in ESQL/C, which is what
I4GL uses under the covers).
The quickest workaround is probably to build the create function statement
into a string and EXECUTE IMMEDIATE it (or PREPARE and EXECUTE and FREE it).
You could try an SQL ... END SQL block, but I doubt if it will handle the
embedded semi-colons any better.
Also remember to run with WHENEVER ERROR STOP or check the result from each
SQL statement religiously.
On Thu, Nov 26, 2009 at 12:40, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Good afternoon, we are having a rare problem when we are using a functional
> index. There is a 4GL which gives error 201 when there is create a
> functional
> index.
>
> We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
>
> The 4GL application get broken if this functional index is created:
>
> create table "informix".edcve
> (
>
> cod1 decimal(8,0) not null ,
>
> cod2 decimal(4,0) not null
> );
>
> CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)>
> WITH (NOT VARIANT);
>
> RETURN cod1 ;
>
> END FUNCTION;
>
> create index ix_edcve on edcve(f_test1(cod1));>
> update statistics for table edcve;>
> Debugging and using the onmode -I we have found the 4GL stops with error
> 201
> when this sql is executed:
>
> select * from edc_det where cod_emp =? and cod_pto =? and num_edc =? order> by
> cla_ent desc
>
> When this index is not created the 4GL does not get broken.
> Before this sql statement the 4GL querys the table in the following way:
> select * from edcdev where cod1=48>
> Both queries dont get broken if are executed in dbaccess.
>
> Anyone knows why is this happening??
>
> Thanks a lot in advanced.
>
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Ted Turner <http://www.brainyquote.com/quotes/authors/t/ted_turner.html>; -
"Sports is like a war without the killing."
--000e0cd2df6c6c67b304795ad789
On Fri, Nov 27, 2009 at 05:52, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Art, thanks a lot for response, i dont understand well, The 4GL
> application
> cant run queries on table with functional indexes??
>
It can run the queries; it just has problems building the functional
indexes.
> we create the functional indexes using dbaccess just once.
>
I thought we were looking at I4GL code trying to create the functional
index.
If not, then my previous answer was less relevant (even 'less than
relevant').
> The 4GL compiler syntax checks all SQL and it's parser doesn't understand
> functional indexes. It only supports 7.3x supported syntax. You can prepare
> and execute the create index as a string or include it in a BEGIN SQL...
> END SQL block to bypass the parser.
>
> Art
>
> Subject: error 201 when using functional index [18210]
>
> Good afternoon, we are having a rare problem when we are using a functional
> index. There is a 4GL which gives error 201 when there is create a
> functional
> index.
>
> We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
>
> The 4GL application get broken if this functional index is created:
>
> create table "informix".edcve
> (
> cod1 decimal(8,0) not null ,
> cod2 decimal(4,0) not null
> );
>
> CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> WITH (NOT VARIANT);> RETURN cod1 ;
> END FUNCTION;
>
> create index ix_edcve on edcve(f_test1(cod1));>
> update statistics for table edcve;>
> Debugging and using the onmode -I we have found the 4GL stops with error
> 201
> when this sql is executed:
>
> select * from edc_det where cod_emp =? and cod_pto =? and num_edc =?
> order by cla_ent desc>
This does not reference the table ecdve with the functional index AFAICS.
> When this index is not created the 4GL does not get broken.
> Before this sql statement the 4GL querys the table in the following way:
> select * from edcdev where cod1=48>
Neither does this query reference the table edcde; it is referencing another
table edcdev.
Are you sure the functional index is the factor?
Ultimately, both DB-Access and I4GL use versions of ESQL/C to access the
database.
In general, that means they should get similar results. However, DB-Access
doesn't allow you to play with placeholders where I4GL does, so they are not
completely equivalent.
Sadly, DB-Access uses a different version of ESQL/C (built at the same time
as the server) from the separate CSDK product. That means there could be a
bug fix in the DB-Access version that isn't in the I4GL version.
If the problem is consistent, then I would probably want to look at the
information flowing between the client (DB-Access or I4GL) and the server -
using SQLIDEBUG=2:something in the environment to capture the
communications, and then using sqliprint on the log files to compare the
information flows. You'd want to keep the program/script very small - the
bare minimum that reproduces the problem.
Typically, this isolates the problem to 'client-side' or 'server-side'.
> Both queries dont get broken if are executed in dbaccess.
>
> Anyone knows why is this happening??
>
No real idea - it shouldn't be happening.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Stephen
Leacock<http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html>;
- "I detest life-insurance agents: they always argue that I shall some
day
die, which is not so."
--000e0cd158a80fd41c04795b11c9
Hi Jonathan, thanks a lot for response, i typo the table name
The query is :
select * from edcve where cod1=48
We dont build the functional index neither the function for it in 4gl
application, we built them by dbaccess.
When we drop the functional index the 4GL application does not show the error
201, then the functional index exists, the application shows error 201.
And even is more rare that application stops with error when opens this cursor:
declare cursor1 cursor for select * from edc_det
where cod_emp = v_emp
and cod_pto = v_cod_pto
and num_edc = v_num_edc
order by cla_ent desc
end function
What it could be?? Its very strange,
How do i do to use SQLIDEBUG=2 ??
Thanks a lot
Then with vi i set list, then
> Hi Art, thanks a lot for response, i dont understand well, The 4GL
> application
> cant run queries on table with functional indexes??
>
It can run the queries; it just has problems building the functional
indexes.
> we create the functional indexes using dbaccess just once.
>
I thought we were looking at I4GL code trying to create the functional
index.
If not, then my previous answer was less relevant (even 'less than
relevant').
> The 4GL compiler syntax checks all SQL and it's parser doesn't understand
> functional indexes. It only supports 7.3x supported syntax. You can prepare
> and execute the create index as a string or include it in a BEGIN SQL...
> END SQL block to bypass the parser.
>
> Art
>
> Subject: error 201 when using functional index [18210]
>
> Good afternoon, we are having a rare problem when we are using a functional
> index. There is a 4GL which gives error 201 when there is create a
> functional
> index.
>
> We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
>
> The 4GL application get broken if this functional index is created:
>
> create table "informix".edcve
> (
> cod1 decimal(8,0) not null ,
> cod2 decimal(4,0) not null
> );
>
> CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> WITH (NOT VARIANT);> RETURN cod1 ;
> END FUNCTION;
>
> create index ix_edcve on edcve(f_test1(cod1));>
> update statistics for table edcve;>
> Debugging and using the onmode -I we have found the 4GL stops with error
> 201
> when this sql is executed:
>
> select * from edc_det where cod_emp =? and cod_pto =? and num_edc =?
> order by cla_ent desc>
This does not reference the table ecdve with the functional index AFAICS.
> When this index is not created the 4GL does not get broken.
> Before this sql statement the 4GL querys the table in the following way:
> select * from edcdev where cod1=48>
Neither does this query reference the table edcde; it is referencing another
table edcdev.
Are you sure the functional index is the factor?
Ultimately, both DB-Access and I4GL use versions of ESQL/C to access the
database.
In general, that means they should get similar results. However, DB-Access
doesn't allow you to play with placeholders where I4GL does, so they are not
completely equivalent.
Sadly, DB-Access uses a different version of ESQL/C (built at the same time
as the server) from the separate CSDK product. That means there could be a
bug fix in the DB-Access version that isn't in the I4GL version.
If the problem is consistent, then I would probably want to look at the
information flowing between the client (DB-Access or I4GL) and the server -
using SQLIDEBUG=2:something in the environment to capture the
communications, and then using sqliprint on the log files to compare the
information flows. You'd want to keep the program/script very small - the
bare minimum that reproduces the problem.
Typically, this isolates the problem to 'client-side' or 'server-side'.
> Both queries dont get broken if are executed in dbaccess.
>
> Anyone knows why is this happening??
>
No real idea - it shouldn't be happening.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Stephen
Leacock<http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html>;
- "I detest life-insurance agents: they always argue that I shall some
day
die, which is not so."
On Fri, Nov 27, 2009 at 06:51, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Jonathan, thanks a lot for response, i typo the table name
> The query is :
>
> select * from edcve where cod1=48>
> We dont build the functional index neither the function for it in 4gl
> application, we built them by dbaccess.
>
> When we drop the functional index the 4GL application does not show the
> error
> 201, then the functional index exists, the application shows error 201.
>
> And even is more rare that application stops with error when opens this
> cursor:
>
> declare cursor1 cursor for select * from edc_det
> where cod_emp = v_emp
> and cod_pto = v_cod_pto
> and num_edc = v_num_edc
> order by cla_ent desc
> end function
>
Wha't the 'end function' doing?
> What it could be?? Its very strange,
>
> How do i do to use SQLIDEBUG=2 ??
>
export SQLIDEBUG=2:something
(the something is used as a prefix to the dump file names).
Then run:
$ dbaccess dbname script
$ minimal-i4gl-program
You should have two files, both starting with 'something'.
sqliprint something_...name1...
sqliprint something_...name2...
Look to see if there's anything startlingly different between what works
(DB-Access) and what doesn't (I4GL). It may be uninformative - but since
-201 syntax error often means a broken SQL statement, you may be able to
spot what is wrong with what the I4GL sent to IDS.
> > Hi Art, thanks a lot for response, i dont understand well, The 4GL
> > application cant run queries on table with functional indexes??
>
> It can run the queries; it just has problems building the functional
> indexes.
>
> > we create the functional indexes using dbaccess just once.
>
> I thought we were looking at I4GL code trying to create the functional
> index.
> If not, then my previous answer was less relevant (even 'less than
> relevant').
>
> > The 4GL compiler syntax checks all SQL and it's parser doesn't understand
> > functional indexes. It only supports 7.3x supported syntax. You can
> prepare
> > and execute the create index as a string or include it in a BEGIN SQL...
> > END SQL block to bypass the parser.
> >
> > Art
> >
> > Subject: error 201 when using functional index [18210]
> >
> > Good afternoon, we are having a rare problem when we are using a
> functional
> > index. There is a 4GL which gives error 201 when there is create a
> > functional
> > index.
> >
> > We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
> >
> > The 4GL application get broken if this functional index is created:
> >
> > create table "informix".edcve
> > (
> > cod1 decimal(8,0) not null ,
> > cod2 decimal(4,0) not null
> > );
> >
> > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> > WITH (NOT VARIANT);> > RETURN cod1 ;
> > END FUNCTION;
> >
> > create index ix_edcve on edcve(f_test1(cod1));> >
> > update statistics for table edcve;> >
> > Debugging and using the onmode -I we have found the 4GL stops with error
> > 201
> > when this sql is executed:
> >
> > select * from edc_det where cod_emp =? and cod_pto =? and num_edc =?
> > order by cla_ent desc> >
>
> This does not reference the table ecdve with the functional index AFAICS.
>
> > When this index is not created the 4GL does not get broken.
> > Before this sql statement the 4GL querys the table in the following way:
> > select * from edcdev where cod1=48> >
>
> Neither does this query reference the table edcde; it is referencing
> another
> table edcdev.
>
> Are you sure the functional index is the factor?
>
> Ultimately, both DB-Access and I4GL use versions of ESQL/C to access the
> database.
>
> In general, that means they should get similar results. However, DB-Access
> doesn't allow you to play with placeholders where I4GL does, so they are
> not
> completely equivalent.
>
> Sadly, DB-Access uses a different version of ESQL/C (built at the same time
> as the server) from the separate CSDK product. That means there could be a
> bug fix in the DB-Access version that isn't in the I4GL version.
>
> If the problem is consistent, then I would probably want to look at the
> information flowing between the client (DB-Access or I4GL) and the server -
> using SQLIDEBUG=2:something in the environment to capture the
> communications, and then using sqliprint on the log files to compare the
> information flows. You'd want to keep the program/script very small - the
> bare minimum that reproduces the problem.
>
> Typically, this isolates the problem to 'client-side' or 'server-side'.
>
> > Both queries dont get broken if are executed in dbaccess.
> >
> > Anyone knows why is this happening??
>
> No real idea - it shouldn't be happening.
>
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Mike Ditka <http://www.brainyquote.com/quotes/authors/m/mike_ditka.html>; -
"If God had wanted man to play soccer, he wouldn't have given us arms."
--000e0cd170f0bb91a504795b94af
Hi Jonatahn, thanks for SQLIDEBUG tip, look at what i found:
The real erorr sis 206 and ISAM 111, what says the data server cant find the
table, then shows error 201, then program finish with a dbclose
This is trace with functional indexes created. After trace without functional
index.
+14282
+14283 C->S (102) Time: 2009-11-27 10:23:32.00000
+14284 SQ_PREPARE
+14285 # values: 3
+14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and
num_edc =? order by cla_ent desc
" [90]
+14287 SQ_NDESCRIBE
+14288 SQ_WANTDONE
+14289 SQ_EOT
+14290
+14291 S->C (20) Time: 2009-11-27 10:23:32.00000
+14292 SQ_ERR
+14293 SQL error..........: -206
+14294 ISAM/RSAM error....: -111
+14295 Offset in statement: 23
+14296 Error message......: "edc_det" [7]
+14297 SQ_EOT
+14298
+14299 C->S (24) Time: 2009-11-27 10:23:32.00000
+14300 SQ_PREPARE
+14301 # values: 0
+14302 CMD.....: "a_pobla_2001" [12]
+14303 SQ_NDESCRIBE
+14304 SQ_WANTDONE
+14305 SQ_EOT
+14306
+14307 S->C (12) Time: 2009-11-27 10:23:32.00000
+14308 SQ_ERR
+14309 SQL error..........: -201
+14310 ISAM/RSAM error....: 0
+14311 Offset in statement: 1
+14312 Error message......: "" [0]
+14313 SQ_EOT
---------------------------------------------------------
TRACE WITHOUT FUNCTIONAL INDEX:
+14282
+14283 C->S (102) Time: 2009-11-27 10:40:02.00000
+14284 SQ_PREPARE
+14285 # values: 3
+14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and
num_edc =? order by cla_ent desc
" [90]
+14287 SQ_NDESCRIBE
+14288 SQ_WANTDONE
+14289 SQ_EOT
+14290
+14291 S->C (284) Time: 2009-11-27 10:40:02.00000
+14292 SQ_DESCRIBE
+14293 Stmt Type...........: 2
+14294 Server Stmt Id......: 49
+14295 Estimated Cost......: 0
+14296 Size of output tuple: 64
+14297 # output fields.....: 12
+14298 Size of string table: 96
+14299 0) Field 'cod_emp'
+14300 Index into string table: 0
+14301 Starting offset in tuple: 0
+14302 Type....................: DECIMAL; NOT NULLABLE
+14303 Length : 2048 (0x800)
+14304
+14305 1) Field 'cod_pto'
+14306 Index into string table: 8
+14307 Starting offset in tuple: 5
+14308 Type....................: DECIMAL; NOT NULLABLE
+14309 Length : 1024 (0x400)
What you think??
Thanks
On Fri, Nov 27, 2009 at 06:51, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Jonathan, thanks a lot for response, i typo the table name
> The query is :
>
> select * from edcve where cod1=48>
> We dont build the functional index neither the function for it in 4gl
> application, we built them by dbaccess.
>
> When we drop the functional index the 4GL application does not show the
> error
> 201, then the functional index exists, the application shows error 201.
>
> And even is more rare that application stops with error when opens this
> cursor:
>
> declare cursor1 cursor for select * from edc_det
> where cod_emp = v_emp
> and cod_pto = v_cod_pto
> and num_edc = v_num_edc
> order by cla_ent desc
> end function
>
Wha't the 'end function' doing?
> What it could be?? Its very strange,
>
> How do i do to use SQLIDEBUG=2 ??
>
export SQLIDEBUG=2:something
(the something is used as a prefix to the dump file names).
Then run:
$ dbaccess dbname script
$ minimal-i4gl-program
You should have two files, both starting with 'something'.
sqliprint something_...name1...
sqliprint something_...name2...
Look to see if there's anything startlingly different between what works
(DB-Access) and what doesn't (I4GL). It may be uninformative - but since
-201 syntax error often means a broken SQL statement, you may be able to
spot what is wrong with what the I4GL sent to IDS.
> > Hi Art, thanks a lot for response, i dont understand well, The 4GL
> > application cant run queries on table with functional indexes??
>
> It can run the queries; it just has problems building the functional
> indexes.
>
> > we create the functional indexes using dbaccess just once.
>
> I thought we were looking at I4GL code trying to create the functional
> index.
> If not, then my previous answer was less relevant (even 'less than
> relevant').
>
> > The 4GL compiler syntax checks all SQL and it's parser doesn't understand
> > functional indexes. It only supports 7.3x supported syntax. You can
> prepare
> > and execute the create index as a string or include it in a BEGIN SQL...
> > END SQL block to bypass the parser.
> >
> > Art
> >
> > Subject: error 201 when using functional index [18210]
> >
> > Good afternoon, we are having a rare problem when we are using a
> functional
> > index. There is a 4GL which gives error 201 when there is create a
> > functional
> > index.
> >
> > We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
> >
> > The 4GL application get broken if this functional index is created:
> >
> > create table "informix".edcve
> > (
> > cod1 decimal(8,0) not null ,
> > cod2 decimal(4,0) not null
> > );
> >
> > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> > WITH (NOT VARIANT);> > RETURN cod1 ;
> > END FUNCTION;
> >
> > create index ix_edcve on edcve(f_test1(cod1));> >
> > update statistics for table edcve;> >
> > Debugging and using the onmode -I we have found the 4GL stops with error
> > 201
> > when this sql is executed:
> >
> > select * from edc_det where cod_emp =? and cod_pto =? and num_edc =?
> > order by cla_ent desc> >
>
> This does not reference the table ecdve with the functional index AFAICS.
>
> > When this index is not created the 4GL does not get broken.
> > Before this sql statement the 4GL querys the table in the following way:
> > select * from edcdev where cod1=48> >
>
> Neither does this query reference the table edcde; it is referencing
> another
> table edcdev.
>
> Are you sure the functional index is the factor?
>
> Ultimately, both DB-Access and I4GL use versions of ESQL/C to access the
> database.
>
> In general, that means they should get similar results. However, DB-Access
> doesn't allow you to play with placeholders where I4GL does, so they are
> not
> completely equivalent.
>
> Sadly, DB-Access uses a different version of ESQL/C (built at the same time
> as the server) from the separate CSDK product. That means there could be a
> bug fix in the DB-Access version that isn't in the I4GL version.
>
> If the problem is consistent, then I would probably want to look at the
> information flowing between the client (DB-Access or I4GL) and the server -
> using SQLIDEBUG=2:something in the environment to capture the
> communications, and then using sqliprint on the log files to compare the
> information flows. You'd want to keep the program/script very small - the
> bare minimum that reproduces the problem.
>
> Typically, this isolates the problem to 'client-side' or 'server-side'.
>
> > Both queries dont get broken if are ex
OK, sorry, I misread your post. What I said holds though. Is the -201
error coming at compile time or at runtime?
I think that the problem may be occurring because the 4GL compiler is
parsing the SQL and also querying the system catalogs. Do you have a
structure defined as 'LIKE edc_det'? If so, try defining the structure
manually with all of the columns specified in the code. Also, using SELECT
* is a bad idea. It breaks more code than it helps. The select should also
list all of the columns that you want to retrieve in the projection clause
rather than '*'. If neither fixes the problem, try the expanded structure
but also prepare the SELECT or embed it in an SQL...END SQL block:
SQL
select col1, col2, ...
into edc_det_struct.*
from edc_det
where cod_emp = ?
and cod_pto = ?
and num_edc = ?
order by= cla_ent desc
END SQL
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Nov 27, 2009 at 7:52 AM, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Art, thanks a lot for response, i dont understand well, The 4GL
> application
> cant run queries on table with functional indexes??
>
> we create the functional indexes using dbaccess just once.
>
> Thanks a lot.
>
> The 4GL compiler syntax checks all SQL and it's parser doesn't understand =
> functional indexes. It only supports 7.3x supported syntax. You can prepa=
> re and execute the create index as a string or include it in a BEGIN
> SQL...=
> END SQL block to bypass the parser.
>
> Art=20
>
> Subject: error 201 when using functional index [18210]
>
> Good afternoon, we are having a rare problem when we are using a
> functional=
> =20
> index. There is a 4GL which gives error 201 when there is create a
> function=
> al=20
> index.=20
>
> We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32=20
>
> The 4GL application get broken if this functional index is created:=20
>
> create table "informix".edcve=20
> (=20
>
> cod1 decimal(8,0) not null ,=20
>
> cod2 decimal(4,0) not null=20
> );=20
>
> CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)=20>
> WITH (NOT VARIANT);=20
>
> RETURN cod1 ;=20
>
> END FUNCTION;=20
>
> create index ix_edcve on edcve(f_test1(cod1));=20>
> update statistics for table edcve;=20>
> Debugging and using the onmode -I we have found the 4GL stops with error
> 20=
> 1=20
> when this sql is executed:=20
>
> select * from edc_det where cod_emp =3D? and cod_pto =3D? and num_edc =3D?> =
> order by=20
> cla_ent desc=20
>
> When this index is not created the 4GL does not get broken.=20
> Before this sql statement the 4GL querys the table in the following way:=20
> select * from edcdev where cod1=3D48=20>
> Both queries dont get broken if are executed in dbaccess.=20
>
> Anyone knows why is this happening??=20
>
> Thanks a lot in advanced.=20
>
>
> ***************************************************************************=
> ****=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174769c8dbcacb04795ca20e
On Fri, Nov 27, 2009 at 07:49, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Jonathan, thanks for SQLIDEBUG tip, look at what i found:
>
Well done - you're onto something - not sure what, but...
> The real erorr sis 206 and ISAM 111, what says the data server cant find
> the
> table, then shows error 201, then program finish with a dbclose
>
There's several lots of weird stuff happening - and I don't have a good
explanation for it.
However...
This is trace with functional indexes created. After trace without
> functional
> index.
>
> +14282
> +14283 C->S (102) Time: 2009-11-27 10:23:32.00000
> +14284 SQ_PREPARE
> +14285 # values: 3
> +14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and
> num_edc =? order by cla_ent desc
> " [90]
> +14287 SQ_NDESCRIBE
> +14288 SQ_WANTDONE
> +14289 SQ_EOT
> +14290
> +14291 S->C (20) Time: 2009-11-27 10:23:32.00000
> +14292 SQ_ERR
> +14293 SQL error..........: -206
> +14294 ISAM/RSAM error....: -111
> +14295 Offset in statement: 23
> +14296 Error message......: "edc_det" [7]
> +14297 SQ_EOT
> +14298
>
This is the server saying it doesn't know what you mean by table edc_det.
Are you sure that it exists with that name? Is the table used earlier in
the program, by any chance? It is very odd.
Is there a chance that edc_det is a temp table that's been dropped?
> +14299 C->S (24) Time: 2009-11-27 10:23:32.00000
> +14300 SQ_PREPARE
> +14301 # values: 0
> +14302 CMD.....: "a_pobla_2001" [12]
> +14303 SQ_NDESCRIBE
> +14304 SQ_WANTDONE
> +14305 SQ_EOT
> +14306
> +14307 S->C (12) Time: 2009-11-27 10:23:32.00000
> +14308 SQ_ERR
> +14309 SQL error..........: -201
> +14310 ISAM/RSAM error....: 0
> +14311 Offset in statement: 1
> +14312 Error message......: "" [0]
> +14313 SQ_EOT
>
This code is sending the statement "a_pobla_2001" to the database server,
and not unreasonably it is saying "I don't understand you". There's a
problem in your I4GL not detecting the -206 error. As I mentioned once
before, have you got WHENEVER ERROR STOP in place, and if not, are you
religiously checking every SQL statement? And how did you create this bogus
statement?
> ---------------------------------------------------------
>
> TRACE WITHOUT FUNCTIONAL INDEX:
>
> +14282
> +14283 C->S (102) Time: 2009-11-27 10:40:02.00000
> +14284 SQ_PREPARE
> +14285 # values: 3
> +14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and
> num_edc =? order by cla_ent desc
> " [90]
> +14287 SQ_NDESCRIBE
> +14288 SQ_WANTDONE
> +14289 SQ_EOT
> +14290
> +14291 S->C (284) Time: 2009-11-27 10:40:02.00000
> +14292 SQ_DESCRIBE
> +14293 Stmt Type...........: 2
> +14294 Server Stmt Id......: 49
> +14295 Estimated Cost......: 0
> +14296 Size of output tuple: 64
> +14297 # output fields.....: 12
> +14298 Size of string table: 96
> +14299 0) Field 'cod_emp'
> +14300 Index into string table: 0
> +14301 Starting offset in tuple: 0
> +14302 Type....................: DECIMAL; NOT NULLABLE
> +14303 Length : 2048 (0x800)
> +14304
> +14305 1) Field 'cod_pto'
> +14306 Index into string table: 8
> +14307 Starting offset in tuple: 5
> +14308 Type....................: DECIMAL; NOT NULLABLE
> +14309 Length : 1024 (0x400)
>
> What you think??
>
Since it appears that you send the same statement at the same point in the
execution sequence, and the server does recognize the table when there is no
functional index and fails to recognize it when there is a functional index,
it is possible to think that there's a bug in the server that you are
somehow tickling. I'm a bit cautious about claiming 'server bug', but I
think you have enough evidence to go to IBM Informix Tech Support and
request assistance.
>
> On Fri, Nov 27, 2009 at 06:51, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> > Hi Jonathan, thanks a lot for response, i typo the table name
> > The query is :
> >
> > select * from edcve where cod1=48> >
> > We dont build the functional index neither the function for it in 4gl
> > application, we built them by dbaccess.
> >
> > When we drop the functional index the 4GL application does not show the
> > error
> > 201, then the functional index exists, the application shows error 201.
> >
> > And even is more rare that application stops with error when opens this
> > cursor:
> >
> > declare cursor1 cursor for select * from edc_det
> > where cod_emp = v_emp
> > and cod_pto = v_cod_pto
> > and num_edc = v_num_edc
> > order by cla_ent desc
> > end function
> >
>
> Wha't the 'end function' doing?
>
> > What it could be?? Its very strange,
> >
> > How do i do to use SQLIDEBUG=2 ??
> >
>
> export SQLIDEBUG=2:something
>
> (the something is used as a prefix to the dump file names).
>
> Then run:
>
> $ dbaccess dbname script>
> $ minimal-i4gl-program>
> You should have two files, both starting with 'something'.
>
> sqliprint something_...name1...
>
> sqliprint something_...name2...
>
> Look to see if there's anything startlingly different between what works
> (DB-Access) and what doesn't (I4GL). It may be uninformative - but since
> -201 syntax error often means a broken SQL statement, you may be able to
> spot what is wrong with what the I4GL sent to IDS.
>
> > > Hi Art, thanks a lot for response, i dont understand well, The 4GL
> > > application cant run queries on table with functional indexes??
> >
> > It can run the queries; it just has problems building the functional
> > indexes.
> >
> > > we create the functional indexes using dbaccess just once.
> >
> > I thought we were looking at I4GL code trying to create the functional
> > index.
> > If not, then my previous answer was less relevant (even 'less than
> > relevant').
> >
> > > The 4GL compiler syntax checks all SQL and it's parser doesn't
> understand
> > > functional indexes. It only supports 7.3x supported syntax. You can
> > prepare
> > > and execute the create index as a string or include it in a BEGIN
> SQL...
> > > END SQL block to bypass the parser.
> > >
> > > Art
> > >
> > > Subject: error 201 when using functional index [18210]
> > >
> > > Good afternoon, we are having a rare problem when we are using a
> > functional
> > > index. There is a 4GL which gives error 201 when there is create a
> > > functional
> > > index.
> > >
> > > We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
> > >
> > > The 4GL application get broken if this functional index is created:
> > >
> > > create table "informix".edcve
> > > (
> > > cod1 decimal(8,0) not null ,
> > > cod2 decimal(4,0) not null
> > > );
> > >
> > > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> > > WITH (NOT VARIANT);> > > RETURN cod1 ;
> > > END FUNCTION;
> > >
> > > create index ix_edcve on edcve(f_test1(cod1));> > >
> > > update statistics for table edcve;> > >
>
Hi Art, thanks for response, this is the complete function which fails:
function cursor_20(v_cod_pto, v_num_edc)
define
v_cod_pto like edc_cab.cod_pto,
v_num_edc like edc_cab.num_edc
declare c_edcdet_2001 cursor for select * from edc_det
where cod_emp = v_emp
and cod_pto = v_cod_pto
and num_edc = v_num_edc
order by cla_ent desc
end function
What you think?? think its an informix bug??
Regards.
OK, sorry, I misread your post. What I said holds though. Is the -201
error coming at compile time or at runtime?
I think that the problem may be occurring because the 4GL compiler is
parsing the SQL and also querying the system catalogs. Do you have a
structure defined as 'LIKE edc_det'? If so, try defining the structure
manually with all of the columns specified in the code. Also, using SELECT
* is a bad idea. It breaks more code than it helps. The select should also
list all of the columns that you want to retrieve in the projection clause
rather than '*'. If neither fixes the problem, try the expanded structure
but also prepare the SELECT or embed it in an SQL...END SQL block:
SQL
select col1, col2, ...
into edc_det_struct.*
from edc_det
where cod_emp = ?
and cod_pto = ?
and num_edc = ?
order by= cla_ent desc
END SQL
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Nov 27, 2009 at 7:52 AM, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Art, thanks a lot for response, i dont understand well, The 4GL
> application
> cant run queries on table with functional indexes??
>
> we create the functional indexes using dbaccess just once.
>
> Thanks a lot.
>
> The 4GL compiler syntax checks all SQL and it's parser doesn't understand =
> functional indexes. It only supports 7.3x supported syntax. You can prepa=
> re and execute the create index as a string or include it in a BEGIN
> SQL...=
> END SQL block to bypass the parser.
>
> Art=20
>
> Subject: error 201 when using functional index [18210]
>
> Good afternoon, we are having a rare problem when we are using a
> functional=
> =20
> index. There is a 4GL which gives error 201 when there is create a
> function=
> al=20
> index.=20
>
> We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32=20
>
> The 4GL application get broken if this functional index is created:=20
>
> create table "informix".edcve=20
> (=20
>
> cod1 decimal(8,0) not null ,=20
>
> cod2 decimal(4,0) not null=20
> );=20
>
> CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)=20>
> WITH (NOT VARIANT);=20
>
> RETURN cod1 ;=20
>
> END FUNCTION;=20
>
> create index ix_edcve on edcve(f_test1(cod1));=20>
> update statistics for table edcve;=20>
> Debugging and using the onmode -I we have found the 4GL stops with error
> 20=
> 1=20
> when this sql is executed:=20
>
> select * from edc_det where cod_emp =3D? and cod_pto =3D? and num_edc =3D?> =
> order by=20
> cla_ent desc=20
>
> When this index is not created the 4GL does not get broken.=20
> Before this sql statement the 4GL querys the table in the following way:=20
> select * from edcdev where cod1=3D48=20>
> Both queries dont get broken if are executed in dbaccess.=20
>
> Anyone knows why is this happening??=20
>
> Thanks a lot in advanced.=20
>
>
> ***************************************************************************=
> ****=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Excellent Jonathan, you cleared my doubts. Inmediatly im going to open the PMR
with this case.
Thank you very much.
> Hi Jonathan, thanks for SQLIDEBUG tip, look at what i found:
>
Well done - you're onto something - not sure what, but...
> The real erorr sis 206 and ISAM 111, what says the data server cant find
> the
> table, then shows error 201, then program finish with a dbclose
>
There's several lots of weird stuff happening - and I don't have a good
explanation for it.
However...
This is trace with functional indexes created. After trace without
> functional
> index.
>
> +14282
> +14283 C->S (102) Time: 2009-11-27 10:23:32.00000
> +14284 SQ_PREPARE
> +14285 # values: 3
> +14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and
> num_edc =? order by cla_ent desc
> " [90]
> +14287 SQ_NDESCRIBE
> +14288 SQ_WANTDONE
> +14289 SQ_EOT
> +14290
> +14291 S->C (20) Time: 2009-11-27 10:23:32.00000
> +14292 SQ_ERR
> +14293 SQL error..........: -206
> +14294 ISAM/RSAM error....: -111
> +14295 Offset in statement: 23
> +14296 Error message......: "edc_det" [7]
> +14297 SQ_EOT
> +14298
>
This is the server saying it doesn't know what you mean by table edc_det.
Are you sure that it exists with that name? Is the table used earlier in
the program, by any chance? It is very odd.
Is there a chance that edc_det is a temp table that's been dropped?
> +14299 C->S (24) Time: 2009-11-27 10:23:32.00000
> +14300 SQ_PREPARE
> +14301 # values: 0
> +14302 CMD.....: "a_pobla_2001" [12]
> +14303 SQ_NDESCRIBE
> +14304 SQ_WANTDONE
> +14305 SQ_EOT
> +14306
> +14307 S->C (12) Time: 2009-11-27 10:23:32.00000
> +14308 SQ_ERR
> +14309 SQL error..........: -201
> +14310 ISAM/RSAM error....: 0
> +14311 Offset in statement: 1
> +14312 Error message......: "" [0]
> +14313 SQ_EOT
>
This code is sending the statement "a_pobla_2001" to the database server,
and not unreasonably it is saying "I don't understand you". There's a
problem in your I4GL not detecting the -206 error. As I mentioned once
before, have you got WHENEVER ERROR STOP in place, and if not, are you
religiously checking every SQL statement? And how did you create this bogus
statement?
> ---------------------------------------------------------
>
> TRACE WITHOUT FUNCTIONAL INDEX:
>
> +14282
> +14283 C->S (102) Time: 2009-11-27 10:40:02.00000
> +14284 SQ_PREPARE
> +14285 # values: 3
> +14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and
> num_edc =? order by cla_ent desc
> " [90]
> +14287 SQ_NDESCRIBE
> +14288 SQ_WANTDONE
> +14289 SQ_EOT
> +14290
> +14291 S->C (284) Time: 2009-11-27 10:40:02.00000
> +14292 SQ_DESCRIBE
> +14293 Stmt Type...........: 2
> +14294 Server Stmt Id......: 49
> +14295 Estimated Cost......: 0
> +14296 Size of output tuple: 64
> +14297 # output fields.....: 12
> +14298 Size of string table: 96
> +14299 0) Field 'cod_emp'
> +14300 Index into string table: 0
> +14301 Starting offset in tuple: 0
> +14302 Type....................: DECIMAL; NOT NULLABLE
> +14303 Length : 2048 (0x800)
> +14304
> +14305 1) Field 'cod_pto'
> +14306 Index into string table: 8
> +14307 Starting offset in tuple: 5
> +14308 Type....................: DECIMAL; NOT NULLABLE
> +14309 Length : 1024 (0x400)
>
> What you think??
>
Since it appears that you send the same statement at the same point in the
execution sequence, and the server does recognize the table when there is no
functional index and fails to recognize it when there is a functional index,
it is possible to think that there's a bug in the server that you are
somehow tickling. I'm a bit cautious about claiming 'server bug', but I
think you have enough evidence to go to IBM Informix Tech Support and
request assistance.
>
> On Fri, Nov 27, 2009 at 06:51, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> > Hi Jonathan, thanks a lot for response, i typo the table name
> > The query is :
> >
> > select * from edcve where cod1=48> >
> > We dont build the functional index neither the function for it in 4gl
> > application, we built them by dbaccess.
> >
> > When we drop the functional index the 4GL application does not show the
> > error
> > 201, then the functional index exists, the application shows error 201.
> >
> > And even is more rare that application stops with error when opens this
> > cursor:
> >
> > declare cursor1 cursor for select * from edc_det
> > where cod_emp = v_emp
> > and cod_pto = v_cod_pto
> > and num_edc = v_num_edc
> > order by cla_ent desc
> > end function
> >
>
> Wha't the 'end function' doing?
>
> > What it could be?? Its very strange,
> >
> > How do i do to use SQLIDEBUG=2 ??
> >
>
> export SQLIDEBUG=2:something
>
> (the something is used as a prefix to the dump file names).
>
> Then run:
>
> $ dbaccess dbname script>
> $ minimal-i4gl-program>
> You should have two files, both starting with 'something'.
>
> sqliprint something_...name1...
>
> sqliprint something_...name2...
>
> Look to see if there's anything startlingly different between what works
> (DB-Access) and what doesn't (I4GL). It may be uninformative - but since
> -201 syntax error often means a broken SQL statement, you may be able to
> spot what is wrong with what the I4GL sent to IDS.
>
> > > Hi Art, thanks a lot for response, i dont understand well, The 4GL
> > > application cant run queries on table with functional indexes??
> >
> > It can run the queries; it just has problems building the functional
> > indexes.
> >
> > > we create the functional indexes using dbaccess just once.
> >
> > I thought we were looking at I4GL code trying to create the functional
> > index.
> > If not, then my previous answer was less relevant (even 'less than
> > relevant').
> >
> > > The 4GL compiler syntax checks all SQL and it's parser doesn't
> understand
> > > functional indexes. It only supports 7.3x supported syntax. You can
> > prepare
> > > and execute the create index as a string or include it in a BEGIN
> SQL...
> > > END SQL block to bypass the parser.
> > >
> > > Art
> > >
> > > Subject: error 201 when using functional index [18210]
> > >
> > > Good afternoon, we are having a rare problem when we are using a
> > functional
> > > index. There is a 4GL which gives error 201 when there is create a
> > > functional
> > > index.
> > >
> > > We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
> > >
> > > The 4GL application get broken if this functional index is created:
> > >
> > > create table "informix".edcve
> > > (
> > > cod1 decimal(8,0) not null ,
> > > cod2 decimal(4,0) not null
> > > );
> > >
> > > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> > > WITH (NOT VARIANT);> > > RETURN cod1 ;
> > > END FUNCTION;
> > >
> > > create index ix_edcve on edcve(f_test1(cod1));> > >@@NL@
I don't see a definition for v_emp, is it a global variable? Jonathan may
be right. It's certainly a good idea to open a case with IBM tech support
and see.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Nov 27, 2009 at 1:23 PM, LYNKZ MIKE <yellr@telecom.com.co> wrote:
> Hi Art, thanks for response, this is the complete function which fails:
>
> function cursor_20(v_cod_pto, v_num_edc)
>
> define
>
> v_cod_pto like edc_cab.cod_pto,
>
> v_num_edc like edc_cab.num_edc
>
> declare c_edcdet_2001 cursor for select * from edc_det
>
> where cod_emp = v_emp
>
> and cod_pto = v_cod_pto
>
> and num_edc = v_num_edc
>
> order by cla_ent desc
> end function
>
> What you think?? think its an informix bug??
>
> Regards.
>
> OK, sorry, I misread your post. What I said holds though. Is the -201
> error coming at compile time or at runtime?
>
> I think that the problem may be occurring because the 4GL compiler is
> parsing the SQL and also querying the system catalogs. Do you have a
> structure defined as 'LIKE edc_det'? If so, try defining the structure
> manually with all of the columns specified in the code. Also, using SELECT
> * is a bad idea. It breaks more code than it helps. The select should also
> list all of the columns that you want to retrieve in the projection clause
> rather than '*'. If neither fixes the problem, try the expanded structure
> but also prepare the SELECT or embed it in an SQL...END SQL block:
>
> SQL
> select col1, col2, ...
> into edc_det_struct.*
> from edc_det
> where cod_emp = ?
> and cod_pto = ?
> and num_edc = ?
> order by= cla_ent desc>
> END SQL
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> On Fri, Nov 27, 2009 at 7:52 AM, LYNKZ MIKE <yellr@telecom.com.co> wrote:
>
> > Hi Art, thanks a lot for response, i dont understand well, The 4GL
> > application
> > cant run queries on table with functional indexes??
> >
> > we create the functional indexes using dbaccess just once.
> >
> > Thanks a lot.
> >
> > The 4GL compiler syntax checks all SQL and it's parser doesn't understand
> =
> > functional indexes. It only supports 7.3x supported syntax. You can
> prepa=
> > re and execute the create index as a string or include it in a BEGIN
> > SQL...=
> > END SQL block to bypass the parser.
> >
> > Art=20
> >
> > Subject: error 201 when using functional index [18210]
> >
> > Good afternoon, we are having a rare problem when we are using a
> > functional=
> > =20
> > index. There is a 4GL which gives error 201 when there is create a
> > function=
> > al=20
> > index.=20
> >
> > We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32=20
> >
> > The 4GL application get broken if this functional index is created:=20
> >
> > create table "informix".edcve=20
> > (=20
> >
> > cod1 decimal(8,0) not null ,=20
> >
> > cod2 decimal(4,0) not null=20
> > );=20
> >
> > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)=20> >
> > WITH (NOT VARIANT);=20
> >
> > RETURN cod1 ;=20
> >
> > END FUNCTION;=20
> >
> > create index ix_edcve on edcve(f_test1(cod1));=20> >
> > update statistics for table edcve;=20> >
> > Debugging and using the onmode -I we have found the 4GL stops with error
> > 20=
> > 1=20
> > when this sql is executed:=20
> >
> > select * from edc_det where cod_emp =3D? and cod_pto =3D? and num_edc> =3D?
> > =
> > order by=20
> > cla_ent desc=20
> >
> > When this index is not created the 4GL does not get broken.=20
> > Before this sql statement the 4GL querys the table in the following
> way:=20
> > select * from edcdev where cod1=3D48=20> >
> > Both queries dont get broken if are executed in dbaccess.=20
> >
> > Anyone knows why is this happening??=20
> >
> > Thanks a lot in advanced.=20
> >
> >
> >
> ***************************************************************************=
> > ****=20
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747654e3802fc04795f8c28