Re: Esql/c and OUTER JOIN
hobbes wrote:
> well another problem... it's going to make me crazy.
>
> This doesn't work => i get a -1213 error ...
-1213 is 'character to numeric conversion error'.
> if i replace the sql variable in the declare process : by arbitrary value
> ... it works.
> But for now, as i open the cursor => -1213...
>
> Is there any limitation for number of OUTER join that is used in a query in
> sqlc ??
No.
> with one join it works, two it works
> Then this is a modified version of our request that contains 8 Join (i know
> it's a lot, but in this case , it is not performance purpose but retrieving
> a lot of data with correpondance.
>
> I 've rewritten this query with only join and that OK, but that's the easier
> case to understand that i can provide t you, that create the -1213 error
> ....
> Or maybe it's a misconfigured database ??
We need to see the declarations of the host variables (records), and
we need to see how they are being initialized etc. The chances are
good that the problem is in the variable handling.
Guess 1 - the function that declares the cursor has local (structure)
variables large_det and large, but the cursor is being opened in a
different function.
Guess 2 - its all in a single function but the local variables are not
initialized properly.
The most likely candidate is local variable large.categorie - the
corresponding column is numeric, so if the code thinks you are passing
a string, it would attempt to convert it to a number - and fails.
> Anyway, thk for reading until here :)
> and thks for any answer...:)
>
> Arnaud
> ===================
> exec sql declare complet_curseur cursor for
> select l.num_libelle, l.lng_max,
> nvl(ll1.text,' '),
> nvl(ll2.text,' '),
> nvl(ll3.text,' ')
> from large l LEFT OUTER JOIN large_det ll1
> ON ll1.num_libelle = l.num_libelle and ll1.id_langue => :large_det.id_langue
> LEFT OUTER JOIN large_det ll2
> ON ll2.num_libelle = l.num_libelle and ll2.id_langue = '#'
> LEFT OUTER JOIN large_det ll3
> ON ll3.num_libelle = l.num_libelle and
> ll3.id_langue = :large_det.id_langue
> where l.categorie = :large.categorie;
>
> ======================= SCHEMA =============
> large:
> {
> num_libelle decimal(32,0)
> categ decimal(32,0)
> descript varchar(40,0)
> }
> large_det
> {
> num_libelle decimal(32,0)
> id_langue varchar(5,0)
> text varchar(255)
> trad decimal(32,0)
> }
I note that your code is using large.categorie but your schema
contains large.categ. You also reference large.lng_max (as l.lng_max)
but there is no such column in the schema. Using DECIMAL(32,0) like
that is unusual; do you really need 32-digit integers? Credit cards
use 16 digits...
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
"Jonathan Leffler" wrote:
> -1213 is 'character to numeric conversion error'.
Absolutely :)
> > Is there any limitation for number of OUTER join that is used in a query
in
> > sqlc ??
>
> No.
Well, with dbaccess, winsql or so... no problem, i can put thousand (euhh
well, maybe less ;))
of outer join, and it works ! But if i integrate in esql/c code... it
doesn't
> We need to see the declarations of the host variables (records), and
> we need to see how they are being initialized etc. The chances are
> good that the problem is in the variable handling.
>
> Guess 1 - the function that declares the cursor has local (structure)
> variables large_det and large, but the cursor is being opened in a
> different function.
>
> Guess 2 - its all in a single function but the local variables are not
> initialized properly.
>
> The most likely candidate is local variable large.categorie - the
> corresponding column is numeric, so if the code thinks you are passing
> a string, it would attempt to convert it to a number - and fails.
Right, but i give him a numeric !!! :)
> I note that your code is using large.categorie but your schema
> contains large.categ. You also reference large.lng_max (as l.lng_max)
> but there is no such column in the schema.
well it's a attempt to dissimulate schema information (a shot in the water )
;)
the new example should work fine :)
> Using DECIMAL(32,0) like that is unusual; do you really need
> 32-digit integers? Credit cards use 16 digits...
In fact it's Ingres herited, then it became Oracle herited with number(*,0)
which can reach a 38 in precision.
I certainly need to rewrite my converting database program , to check the
real size of data in columns before generate sql script :)
Thk you Jonathan for all your answers !!
============
This is the new code... :
I will try this time to include everything that is needed :)
ERROR checking is .... really basic :) (but quick for this test)
======CODE========
#include <stdio.h>
#include <string.h>
exec sql include sqlca;
exec sql begin declare section;
exec sql include 'large.h';
exec sql include 'large_det.h';
exec sql end declare section;
main()
{
EXEC SQL BEGIN DECLARE SECTION;
char pass[20];
char base[30];
long categorie;
long i = 0;
long num_un;
char txt[500],txt1[500],txt2[500];
EXEC SQL END DECLARE SECTION;
strcpy(pass,USERPASS);
strcpy(base,CONNEXION);
EXEC SQL connect to :base user :pass using :pass ;
i =0;
i =sqlca.sqlcode; /*NO ERROR*/
exec sql declare democursor cursor for
select l.num_lib,
nvl(ll1.text,' '),
nvl(ll2.text,' '),
nvl(ll3.text,' ')
from large l LEFT OUTER JOIN large_det ll1
ON ll1.num_lib = l.num_lib and ll1.id_langue = :large_det.id_langue
LEFT OUTER JOIN large_det ll2
ON ll2.num_lib = l.num_lib and ll2.id_langue = '#'
LEFT OUTER JOIN large_det ll3
ON ll3.num_lib = l.num_lib and
ll3.id_langue = :large_det.id_langue
where l.categ = :categorie;
/* TO BE SURE */
categorie = 100;
strcpy(large_det.id_langue,"FRA");
/* TO BE SURE */
EXEC SQL open democursor;
i =0;
i =sqlca.sqlcode;/* HERE I GET THE -1213 */
for (;;)
{
EXEC SQL fetch democursor into :num_un,:txt,:txt1,:txt2;
printf("\\n%ld",num_un);
i =0;
i =sqlca.sqlcode;
if(i != 0)
break;
}
EXEC SQL close democursor;
EXEC SQL free democursor;
EXEC SQL commit;
EXEC SQL disconnect current;
}
================================
The schema and include
=========================
large.h
struct large_ {
long num_lib;
long categ;
char desc[41];
} large ;
large_det.h
struct large_det_ {
long num_lib;
char id_langue[7];
char text[101];
char trad[101];
} large_det ;