Error Illegal attempt to convert to text/byte with Informix

[ukoegler]

Hy Jeff,
maybe you remember this problem (little bit ago^^)
Ich now testes TD 6.1 in the hope that the error is now being solved.

An what can I say, the same problem as in 5.1 and 5.2 .

Is there any chance that some developers can reactivate this error to solve the problem?
I'm very confused that I am the only one who has this problem becaus its not unusual to use TEXT/Byte fields in Informix.

Maybe it is the combination of your informix router an the Informix Client SDK Version.

Thanks for your help.

Regards Ulrike

[Ana Paula Bonani]

Hi,

Just wanted to update this thread because this problem is actually an Informix known issue.
The same error can be reproduced from Informix tools, so it's not a TD defect.
Here are our Developer's findings:

Initially, I found that for UPDATE, the Informix describe call failed to return parameter information as it did in the case of INSERT. As a result, we didn't set up the necessary LOCATOR structure correctly, so we got the error as identified in the bug. Faking things out in the debugger (i.e., running through the LOCATOR code), I was able to get the update to happen correctly, but I couldn't identify why describe was failing.

At this point I created an ESQL sample (i.e., a sample using the Informix embedded SQL mechanism), using the same query against the same table (at first -- the current version runs against the same table with another byte column removed to rule that out). My expectation was that the app would succeed and I could use the resulting C code as a starting point for the fix. Instead, what happened was the APP failed with exactly the same error that TD was getting, i.e., "-608 Illegal attempt to convert Text/Byte data type."

I also was able to find that IBM documents that text literals cannot be used to update TEXT fields (and verified this). The documentation talks about using a load utility to update TEXT fields, and suggests that in ESQL this isn't necessary, but offers no examples of how one would go about it in that environment. One would think that if it were do-able at all the call to describe ("sqli_describe_stmt") would succeed.

At this point unless we can find IBM documentation about how one would do this, it's looking very much like you can't get there from here, since IBM's own code generation tool can't get there from here.

Best Regards,

[ukoegler]

You are right that you cannot make an update with SQL Syntax and a literal with TEXT fields. But this is nothing new. You have to use special tools for that.

But why does the update of an informix TEXT field work until Gupta version 4.2 but after that not anymore. That seems to be strange...an the problem... maybe it has something to do with new implemetation of unicode ??

[Ana Paula Bonani]

It could be, a lot of things changed in the router since TD4.2... we weren't able to verify that because we couldn't make it work even outside of TD, by using the Informix tools.

So if you could point us to detailed Informix's documentation or describe the steps to achieve the UPDATE via Informix tools then Dev could continue the investigation.

Regards,

[ukoegler]

Here now there ist some example source code how you can do an update in an text field with ESQL C.
Hopefully this will help you to implement such function into the informix router.
regards Ulrike

EXEC SQL BEGIN DECLARE SECTION;
loc_t images; /* the locator structure */
EXEC SQL END DECLARE SECTION;

char *blobfile = "/tmp/image.gif";

/*
* Set up the locator structure to load the contents of
* a file by name.
*/
images.loc_loctype = LOCFNAME; /* blob is named file */
images.loc_fname = blobfile; /* here is its name */
if(mode == 'e')
images.loc_oflags = LOC_WONLY; /* contents are to be read by engine */
else
images.loc_oflags = LOC_RONLY; /* contents are to be read by engine */
images.loc_size = -1; /* read to end of file */
images.loc_indicator = 0; /* not a null blob */


### if you want to have TEXT/Blob in memory
images.loc_loctype = LOCMEMORY; /* blob in Memeory */
images.loc_buffer = buf; /* char buf[BUFSIZ] */
images.loc_bufsiz = BUFSIZ;




###build the statement

void build_upd()
{
mint n;

sprintf(str_spec,
"UPDATE %s SET %s = ? WHERE %s = \"%s\""
,tabname,blobcol, keycols[0],keyvals[0] );
for (n = 1; n < num_keys; ++n)
{
strcat(str_spec," AND ");
strcat(str_spec,keycols[n]);
strcat(str_spec," = \"");
strcat(str_spec,keyvals[n]);
strcat(str_spec,"\"");
}
strcat(str_spec,";");
if (verbose)
{
printf("\nprepared stmt =\n%s\n\n",str_spec);
fflush(stdout);
}
}


##executes####

if (mode == 'u')
{
build_upd();
EXEC SQL prepare the_update from :str_spec;
error_check("prepare of update");
EXEC SQL execute the_update using :images;
error_check("update statement");
if (verbose)
printf("%d row(s) updated.\n",sqlca.sqlerrd[2]);
}