Error Illegal attempt to convert to text/byte with Informix

[ukoegler]

After migrating to TD 5.2 SP1 i get the following error by executing the follwing Statements:

Call SqlPrepare( hSql, ' UPDATE einstellungen SET
color_focus = :nFocusfarbe ,
color_back = :nHintergrundfarbe ,
play_sound = :bPLAY_SOUND,
play_system_sound = :bPLAY_SYSTEM_SOUND ,
vl_timefrom = :nVorlageTIMEFROM,
vl_wann = :nVorlageWANN ,
vl_delete = :nVorlageDELETE ,
sch_select = :bVorlageSchSelect,
vorblenden = :bVorblenden,
mail_signatur = :strSignatur,
sel_wrk_nr = :nSelWrkNr,
timer_wv = :bTimerWV,
wf_datum = :dfWFDatum,
wf_prjnr = :nWFPrjNr,
wf_sort = :nWFSort,
wf_offen = :bWFOffen,
wf_akt_bearb = :strWFAktBearbeiter
WHERE dbuser = :SqlUserOwner ' )
Call SqlSetLongBindDatatype( 10, 22 )
Call SqlExecute( hSql )

Illegal attempt to convert Text/Byte blob type.

This error occurs by every Statement wich updates/inserts Text/Blob Columns
Database is Informix 10.

Any solutions?

Thanks Ulrike

[ukoegler]

Nobody any suggestion?
I really need an urgent solution because we want to migrate to 5.2 in the next 4 Weeks.
THX Ulrike

[Jeff Luther]

Ulrike:
I do see that you wrote:

After migrating to TD 5.2 SP1

From what earlier TD version? If v4.2 or earlier, this could be a Unicode issue. That is, Informix is expecting an ANSI string and strSignatur is in Unicode. If this is the case, see TD Help for the function: SalStrToMultiByte( strInput, strOutput, nEncoding )

That takes an input Unicode string and converts it to an output multi-byte string, using encoding. Like this:

Code: Select all

String: strSignaturInMultiByte
if NOT SalStrToMultiByte( strSignatur , strSignaturInMultiByte, ENC_ANSI )
        ...error

now your UPDATE statement would be: "... mail_signatur = :strSignaturInMultiByte
and see if that works for you. NOTE that if this is the issue, you will also need to convert back on the 'other side', that is on a SELECT. See Help for SalStrToWideChar() about converting an ANSI string after the fetch back into a Unicode string.


If this is not the issue, I cannot tell what the problem is. Here are a couple ideas, based on what I see:
* Call SqlSetLongBindDatatype( 10, 22 ):
10th position for the update is: mail_signatur = :strSignatur

* the error "Illegal attempt to convert Text/Byte blob type" tells me that mail_signatur on the DB end is a blob type, and of course the var. strSignatur is a string/text type

What is the column type for mail_signatur in the DB?
* If it is blob or byte, it's binary (not text) and this is the problem. If the type = clob or text, then it should work.

* if it is type = clob or text then I suggest you put together a small test case. This should include the CREATE TABLE for a test DB table with a mail_signatur column of the type your real app. has declared, and then an insert/update with strSignatur that fails.

[ukoegler]

The former Team Developer Version is 4.2.0. with the same database. the only thing thart changed is the TD version.

strSignatur is long String

The datatype of the database field is TEXT.

The test with SalStrToMultiByte fails. This was not the issue.
The Select-Statement with the column mail_signatur works well without converting with SalStrToWideChar()

A test with converting the TEXT datafield into LVARCHAR works well. But we have lots of TEXT datafields in the database an it would be lot of work to convert all that columns.
Also Byte datafields are no problem only the TEXT datatype.

I'll try to develop a test case an then send it to you. Maybe in that time you will find a different issue why this doesn't work with the infos above.

Sorry for posting double. I thought I was in the wrong board.

Regatrd

[ukoegler]

Hello Jeff ,
now here is my testapp with one Byte Field an one Text Field.
The Text Field is selectable but not storable
The Byte Field isn't selectabe and also not storable.

I tried it with the Function SalStrToMultiByte and Without. Nothing works.

In the zip file you will find create tab.sql -> The create table Statement an the load statement fot the testdata stored in the file x.unl

the file ordix.bmp you can use for testing the import and storage of a byte field.

testapp.app is the testapp. you have to adjust the parameters for your database name, user and password.

Thank you in advance.
Ulrike

[Jeff Luther]

I'm not sure I can run this. I think we have an Informix I can connect to but I have no front-end utility to run the SQL script. Not sure what all the PBs mean, either.

It would be much simpler if you had a test case that connected to Informix, created a small DB table with a TEXT column, maybe INSERT a value, then fetched it. Where the fetch for the TEXT value is what the problem is, right? Keep it very simple, if you would. I don't have a local Informix and can only get to one via a VPN connection to the office. Thanks.

[ukoegler]

here a new testapp hopefully now simple enough.

What works?
- Inserting a TEXT field
- Inserting a BYTE field
- Updateing a BYTE field
- Fetching a TEXT field

What doesn't work?
- Updating a TEXT field without SalStrToMultyByte
- Updating a TEXT field with SalStrToMultyByte
- Fetching a Byte Field , no error , but no output

THX

[Jeff Luther]

Thanks back -- that's an easier test case! I appreciate you changing it.

Couple changes to the test case:
** Note that your INSERT statements which use the set bind type call must have a "2" not "1" as the first parameter:
SqlSetLongBindDatatype( 2, 22 )
because the Long/signatur value is the 2nd/second value being inserted. "1" is the PK

** Fetching the PICT/LOGO value in the last section into strPict was failing because you had the PK = 1 in the select. You updated the pict/logo where PK = 2, so changing your select to:
! I added Set strPict = '' to verify that it was being fetched back in the query below
Set strPict = ''
If SqlPrepare( hSqlSelect, ' Select logo from testtab WHERE com_nr = 2 into :strPict ' )
now fetches the logo OK


WHAT WE DO SEE IS IN ERROR:
** UPDATE of TEXT value generates a -608 error: “Illegal attempt to convert Text/Byte blob type.”
but the INSERT -- and I added a SELECT to verify that -- both work OK

** I'd expect an UPDATE of the TEXT value after calling SalStrToMultiByte() to fail, since other update fails

I have the UPDATE TEXT 'fail' issue as a question out to our developer/router guru and if/when I hear of something I'll update this thread. Thanks again for the test case.

[Jeff Luther]

UPDATE: Well, "Live and learn" as they say

I got some more info. on the way to adding a TD defect about the Informix TEXT/UPDATE error we're seeing. Turns out, it is already an issue in our defect database, TD-2427, and was closed. Here is essentially the title of that defect from a couple years ago:

...fail due to an 'as designed' Informix limitation [doing an UPDATE in a TEXT column datatype] will cause an error

THE WORKAROUND: "the workaround is to DELETE and INSERT the value."

That's what we're seeing all right and it solves the mystery! It's the only SQL error I see in the test you'd provided . There were the corrections I'd mentioned last msg. and I see you found out that calling SalStrToMultiByte() for a binary/byte value won't work. Yes, that's to convert a Unicode string/text value to multi-byte.

I also found calling SqlSetLongBindDatatype( x, 24 ) (per TD Help), specific for Informix text works too, but again only for insert/select. Update still fails, per the TD issue above.

[ukoegler]

What can I learn from your suggestions?

First: It doesnt matter if I set SqlSetLongBindDatatype by inserting a TExT Field. The insert works also without SqlSetLongBindDatatype (very strange^^)

Second: Fetching and inserting works

Third: I cannot live with your workaround. Hopefully your just joking. Have you ever heard about referential integrity oder triggering statements in databases? Deleting an inserting instead of updating will not work in our environment. That would cause so much changes in the application that I can also migrate to a different developement tool.

Let me get this right, that your router guru thinks that this error cannot be resolved because the problem is up to informix and not to your router. I cannot believe that because the insert mecanism is the same as the update mechanism in informix (I'm a long time administrator/configurator/tuner of informix databases) and if inserting works updating must be work also properly. In former routers it also works.

If you won't find a bug fix for this problem, we have to change our development platform because we are using so many TEXT and Byte Fields.

We'd really appreciate if you would accept this case as an error an if you can fix the problem with a new revised router. Maybe you can contact your guru again and look for possible resolutions.

Please give me an official announcement wether you will be working on it or definitely not. If not we must really look for an alternative developing environment and Ireally dislike doing that as you will understand

Thanks in advance for your proposals.
Ulrike

[ivano_osti]

Hi.

I had problemns with Informix after install SP1. See the topic "SalPicSetBinary() problem after install SP1". If I read pictures from a Informix "byte" datatype into a binary variable, sometimes the application hangs when using SalPicSetBinary().

I'm not sure but maybe the problem is in the router.

I'm in talking with Jeff trying to solve the problem because I cannot deploy my applications using SP1 until this problem is solved.

Any tips/comments are welcome.

Sorry for don't have a tip for your problem.

Thank you.

[Jeff Luther]

Added defect TD-10635 re Informix TEXT UPDATE generating an error. This will be evaluated for a fix to be included in an upcoming EMP.

[ukoegler]

Is it possible to extend the defect with BYTE Field update? because BYTE Field Update also doesn't work properly.
Thanks Ulrike

[SHA]

Hello,

This will be evaluated for a fix to be included in an upcoming EMP.

This sounds good

Can you say, when this EMP will be released?
It's because, our customers are very very very very disappointed...

[Jeff Luther]

Can you say, when this EMP will be released?

No, I cannot.

FYI, if you need an urgent EMP fix for this or any issue, visit this forum page: https://support.guptatechnologies.com/supportforum/viewtopic.php?f=40&t=3398
to contact Ana directly about your request. Be sure to include all the required information shown in the message.

P.S. I did make mention of BYTE being as issue as well.