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.
I really need an urgent solution because we want to migrate to 5.2 in the next 4 Weeks.
I do see that you wrote:
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 )After migrating to TD 5.2 SP1
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
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.
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.
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.
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.
- 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
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.
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:
THE WORKAROUND: "the workaround is to DELETE and INSERT the value."...fail due to an 'as designed' Informix limitation [doing an UPDATE in a TEXT column datatype] will cause an error
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.
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.
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.
This sounds goodThis will be evaluated for a fix to be included in an upcoming EMP.
Can you say, when this EMP will be released?
It's because, our customers are very very very very disappointed...
No, I cannot.Can you say, when this EMP will be released?
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.
Who is online
Users browsing this forum: [Ccbot] and 0 guests