| Author |
Message
|
| Angshuman |
 Posted: Thu May 16, 2013 10:27 pm Post subject: ESQL INSERT statments fails to insert a string into INFORMIX |
 |
|
Apprentice
Joined: 30 Apr 2009
Posts: 29
|
Hi,
We are writing ESQL statment to insert data into a table (INFORMIX DB).
It is failing to insert a string to a column which is of type CHAR (2). I understand CHAR type should truncate the field and insert first 2 characters into the table.
Writing the SQL statment in PASSTHRU works fine, but not when you write
ESQL statment.
In both cases, we are using odbc connection to connect to the INFORMIX DB.
Can someone please help here.
Thanks,
Angshuman |
|
| Back to top |
|
 |
| Esa |
| Posted: Thu May 16, 2013 11:17 pm Post subject: Re: ESQL INSERT statments fails to insert a string into INFO |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| Angshuman wrote: |
It is failing to insert a string to a column which is of type CHAR (2). I understand CHAR type should truncate the field and insert first 2 characters into the table.
|
I understand the type doesn't do any truncating for you, it's your ESQL code that will have to make sure that the data does not exeed the required length. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu May 16, 2013 11:28 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| if you don't post the esql statement and the error , it is only guesswork. The comment above is correct, and the error message you get probably tells you that there is an overflow |
|
| Back to top |
|
|
| Angshuman |
| Posted: Fri May 17, 2013 12:53 am Post subject: |
|
|
Apprentice
Joined: 30 Apr 2009
Posts: 29
|
It is an overflow error for the same field. It is a CHAR type and not VARCHAR type.
Like I have mentioned, PASSTHRU function works well with the same data.
Even I run the SQL statement in Database directly, the value gets truncated. But why not when using ESQL statement |
|
| Back to top |
|
|
| dogorsy |
| Posted: Fri May 17, 2013 1:12 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
so it is an overflow error.
you say that passthru works, meaning what ? probably that it executes and the data gets truncated. Is that what you want ? if so, in your compute esql you can truncate the data yourself, using RIGHT or LEFT function, depending on what you want. if the problem with your data is that is padded with spaces, use TRIM.
But if your database column is defined as CHAR(2) you MUST send 2 characters. |
|
| Back to top |
|
|
| Angshuman |
| Posted: Fri May 17, 2013 1:55 am Post subject: |
|
|
Apprentice
Joined: 30 Apr 2009
Posts: 29
|
I can truncate the data in ESQL, just trying to understand the reason behind it. Moreover truncation should be done by the table, as it is CHAR datatype and not needed by the code. Why does it behave otherwise in ESQL. SQL works fine with the same data.
PASSTHRU statment does not compile the prepared statement, which gets executed in the Database, which is not the case with ESQL. But I am using odbc connection in both the cases.
[/quote] |
|
| Back to top |
|
|
| dogorsy |
| Posted: Fri May 17, 2013 2:22 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Angshuman wrote: |
I can truncate the data in ESQL, just trying to understand the reason behind it. Moreover truncation should be done by the table, as it is CHAR datatype and not needed by the code. Why does it behave otherwise in ESQL. SQL works fine with the same data.
PASSTHRU statment does not compile the prepared statement, which gets executed in the Database, which is not the case with ESQL. But I am using odbc connection in both the cases.
|
[/quote]
No, truncation means data loss. Maybe that is good for you, but unacceptable for most people. If you pay '1001' euros into your bank account, would you like to be credited with '10' or '01' ? , Let me know and I will keep the rest |
|
| Back to top |
|
|
| Angshuman |
| Posted: Fri May 17, 2013 4:43 am Post subject: |
|
|
Apprentice
Joined: 30 Apr 2009
Posts: 29
|
Data is not sensitive. We can live with truncated data.
This is for app logging. insert should not fail at any cost |
|
| Back to top |
|
|
| dogorsy |
| Posted: Fri May 17, 2013 4:49 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Angshuman wrote: |
Data is not sensitive. We can live with truncated data.
This is for app logging. insert should not fail at any cost |
if data is not sensitive and insert should not fail at any cost, then insert always the string '00'. |
|
| Back to top |
|
|
| Angshuman |
| Posted: Fri May 17, 2013 5:21 am Post subject: |
|
|
Apprentice
Joined: 30 Apr 2009
Posts: 29
|
Still does not answer my question. It is a workaround what you are saying and this whole thing is going round and round.
Can someone please help here? Why is it failing in ESQL and not in SQL? |
|
| Back to top |
|
|
| dogorsy |
| Posted: Fri May 17, 2013 5:33 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Angshuman wrote: |
Still does not answer my question. It is a workaround what you are saying and this whole thing is going round and round.
Can someone please help here? Why is it failing in ESQL and not in SQL? |
no, it is not a workaround. If you don't like it, use passthru, otherwise code your ESQL properly or change the database column definition. Also, go and read some documentation about message broker and odbc. The reason why it is failing in ESQL is clear from the error message. If you want to know why it does not fail in SQL, post the question in the Informix forum |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri May 17, 2013 6:10 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Angshuman wrote: |
Still does not answer my question. It is a workaround what you are saying and this whole thing is going round and round.
Can someone please help here? Why is it failing in ESQL and not in SQL? |
Because ESQL and SQL are not 100% the same?
Just truncate it in the code and we'll no longer speak about it. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| new2z |
| Posted: Fri May 17, 2013 12:35 pm Post subject: |
|
|
Novice
Joined: 16 May 2013
Posts: 15
|
Even if you did it in other RDBMS using SQL Server or Oracle or DB2, you will still get these type of errors
Database will always throw an error if you try to insert more than it is supposed to get. It is not the responsibility of the database. it is your responsibility not to feed more than it can take.
There is no solution to this unless you define your CHAR field to a CLOB (if Informix supports it) and allow huge amounts of character data in 1 field |
|
| Back to top |
|
|
|
|
