Hi,
It seems like libzdb (probaly) won't handle CLOBs in Oracle DBs very well. When selecting a CLOB directly nothing but the value 'null' is returned, when typecasting the CLOB to ex. VARCHAR2 it works perfectly.
The same problem occurs when inserting data to a CLOB.
Tests for Oracle also fails because of this.
I've tried libzdb 2.8, 2.12 and 3.0, the same problem occurs in all three. This happens with Oracle Instant Client 11,1 11,2 and 12,1.
Has anyone else encountered this? Is it a bug in libzdb or Instant Client?
Thank you very much!
Regards, Mats Christensen
On 31-01-14 10:41, Mats Christensen wrote:
Hi,
It seems like libzdb (probaly) won't handle CLOBs in Oracle DBs very well. When selecting a CLOB directly nothing but the value 'null' is returned, when typecasting the CLOB to ex. VARCHAR2 it works perfectly.
Mats,
Looking at the dbmail code involved I notice that the Oracle schema is the only one that defines the 'envelope' field as a CLOB. PostgreSQL and MySQL both treat it as a TEXT field. So DBMail uses ResultSet_getString to retrieve the value, not ResultSet_getBlob.
That might well explain the NULL result.
Now *why* Oracle defines that field as a CLOB, I don't know. I'm not familiar with Oracle's data-types, but if you can please use a TEXT field instead.
A cursory check of the oracle schema reveals one other instance where CLOB is used, but TEXT seems more appropriate, i.e. in the sievescripts.script field.
I'm not very familiar with Oracle either but it seems like CLOB is for Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it is used for several things, like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for upstream, perhaps it's a good idea to use 'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
On Fri, Jan 31, 2014 at 11:34 AM, Paul J Stevens paul@nfg.nl wrote:
Mats,
Looking at the dbmail code involved I notice that the Oracle schema is the only one that defines the 'envelope' field as a CLOB. PostgreSQL and MySQL both treat it as a TEXT field. So DBMail uses ResultSet_getString to retrieve the value, not ResultSet_getBlob.
That might well explain the NULL result.
Now *why* Oracle defines that field as a CLOB, I don't know. I'm not familiar with Oracle's data-types, but if you can please use a TEXT field instead.
A cursory check of the oracle schema reveals one other instance where CLOB is used, but TEXT seems more appropriate, i.e. in the sievescripts.script field.
-- ________________________________________________________________ Paul J Stevens pjstevns @ gmail, twitter, github, linkedin
Premium Hosting Services and Web Application Consultancy *
www.nfg.nl/info@nfg.nl/+31.85.877.99.97
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
ResultSet_getBlob or ResultSet_getString should be interchangeable on a blob or text column. Though of course, if the blob column contains binary data only ResultSet_getBlob should be used to get the correct value.
I should say that a problem with using ResultSet_getString on a blob column was just fixed in Oracle and confirmed fixed in version 3.0. So with one datapoint, the above seems to work. If you want us to investigate, we are also more inclined to do so if you can provide a small test program demonstrating the problem. Or argue in detail why the problem must be in libzdb.
Best regards -- Jan-Henrik Haukeland
On 31 Jan 2014, at 13:36, Mats Christensen matschristensen@gmail.com wrote:
I'm not very familiar with Oracle either but it seems like CLOB is for Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it is used for several things, like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for upstream, perhaps it's a good idea to use 'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
We have been using clob column in oracle and use resultset_getstring and it works well .
Regards,
Abhay Gupta
On 01-Feb-2014, at 5:43 am, Jan-Henrik Haukeland hauk@tildeslash.com wrote:
ResultSet_getBlob or ResultSet_getString should be interchangeable on a blob or text column. Though of course, if the blob column contains binary data only ResultSet_getBlob should be used to get the correct value.
I should say that a problem with using ResultSet_getString on a blob column was just fixed in Oracle and confirmed fixed in version 3.0. So with one datapoint, the above seems to work. If you want us to investigate, we are also more inclined to do so if you can provide a small test program demonstrating the problem. Or argue in detail why the problem must be in libzdb.
Best regards
Jan-Henrik Haukeland
On 31 Jan 2014, at 13:36, Mats Christensen matschristensen@gmail.com wrote:
I'm not very familiar with Oracle either but it seems like CLOB is for Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it is used for several things, like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for upstream, perhaps it's a good idea to use 'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
See the attachment select.c and clob_test.sql
Libzdb 3.0 and Oracle Instant Client 12.1 This setup returns the following for me:
mats@x220 ~/libzdb/libzdb-3.0/test $ ./select This is getString: this is a varchar (null) this is another var (null) This is getBlob: this is a varchar this is a clob this is another var this is another clob
Can you see if it works for you? The problem might be my setup although I've tried two completely different linux setups.
Thanks!
Regards, Mats Christensen
On Sat, Feb 1, 2014 at 1:12 AM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
ResultSet_getBlob or ResultSet_getString should be interchangeable on a blob or text column. Though of course, if the blob column contains binary data only ResultSet_getBlob should be used to get the correct value.
I should say that a problem with using ResultSet_getString on a blob column was just fixed in Oracle and confirmed fixed in version 3.0. So with one datapoint, the above seems to work. If you want us to investigate, we are also more inclined to do so if you can provide a small test program demonstrating the problem. Or argue in detail why the problem must be in libzdb.
Best regards
Jan-Henrik Haukeland
On 31 Jan 2014, at 13:36, Mats Christensen matschristensen@gmail.com wrote:
I'm not very familiar with Oracle either but it seems like CLOB is for
Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it
is used for several things,
like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on
Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for
upstream, perhaps it's a good idea to use
'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
I missed the clob data on the earlier sql, this is correct.
On Sat, Feb 1, 2014 at 1:44 PM, Mats Christensen matschristensen@gmail.comwrote:
See the attachment select.c and clob_test.sql
Libzdb 3.0 and Oracle Instant Client 12.1 This setup returns the following for me:
mats@x220 ~/libzdb/libzdb-3.0/test $ ./select This is getString: this is a varchar (null) this is another var (null) This is getBlob: this is a varchar this is a clob this is another var this is another clob
Can you see if it works for you? The problem might be my setup although I've tried two completely different linux setups.
Thanks!
Regards, Mats Christensen
On Sat, Feb 1, 2014 at 1:12 AM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
ResultSet_getBlob or ResultSet_getString should be interchangeable on a blob or text column. Though of course, if the blob column contains binary data only ResultSet_getBlob should be used to get the correct value.
I should say that a problem with using ResultSet_getString on a blob column was just fixed in Oracle and confirmed fixed in version 3.0. So with one datapoint, the above seems to work. If you want us to investigate, we are also more inclined to do so if you can provide a small test program demonstrating the problem. Or argue in detail why the problem must be in libzdb.
Best regards
Jan-Henrik Haukeland
On 31 Jan 2014, at 13:36, Mats Christensen matschristensen@gmail.com wrote:
I'm not very familiar with Oracle either but it seems like CLOB is for
Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it
is used for several things,
like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on
Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for
upstream, perhaps it's a good idea to use
'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
Is this still a problem? I was thinking about a 3.1 release and if this is a problem with Oracle, need to have this fixed first.
On 01 Feb 2014, at 13:52, Mats Christensen matschristensen@gmail.com wrote:
I missed the clob data on the earlier sql, this is correct.
On Sat, Feb 1, 2014 at 1:44 PM, Mats Christensen matschristensen@gmail.com wrote: See the attachment select.c and clob_test.sql
Libzdb 3.0 and Oracle Instant Client 12.1 This setup returns the following for me:
mats@x220 ~/libzdb/libzdb-3.0/test $ ./select This is getString: this is a varchar (null) this is another var (null) This is getBlob: this is a varchar this is a clob this is another var this is another clob
Can you see if it works for you? The problem might be my setup although I've tried two completely different linux setups.
Thanks!
Regards, Mats Christensen
On Sat, Feb 1, 2014 at 1:12 AM, Jan-Henrik Haukeland hauk@tildeslash.com wrote: ResultSet_getBlob or ResultSet_getString should be interchangeable on a blob or text column. Though of course, if the blob column contains binary data only ResultSet_getBlob should be used to get the correct value.
I should say that a problem with using ResultSet_getString on a blob column was just fixed in Oracle and confirmed fixed in version 3.0. So with one datapoint, the above seems to work. If you want us to investigate, we are also more inclined to do so if you can provide a small test program demonstrating the problem. Or argue in detail why the problem must be in libzdb.
Best regards
Jan-Henrik Haukeland
On 31 Jan 2014, at 13:36, Mats Christensen matschristensen@gmail.com wrote:
I'm not very familiar with Oracle either but it seems like CLOB is for Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it is used for several things, like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for upstream, perhaps it's a good idea to use 'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
I'm not sure, will check how it is working, would be nice if someone else also check it.... Good luck, Volodymyr!
On Monday, May 4, 2015 3:32 AM, Jan-Henrik Haukeland hauk@tildeslash.com wrote:
Is this still a problem? I was thinking about a 3.1 release and if this is a problem with Oracle, need to have this fixed first.
On 01 Feb 2014, at 13:52, Mats Christensen matschristensen@gmail.com wrote:
I missed the clob data on the earlier sql, this is correct.
On Sat, Feb 1, 2014 at 1:44 PM, Mats Christensen matschristensen@gmail.com wrote: See the attachment select.c and clob_test.sql
Libzdb 3.0 and Oracle Instant Client 12.1 This setup returns the following for me:
mats@x220 ~/libzdb/libzdb-3.0/test $ ./select This is getString: this is a varchar (null) this is another var (null) This is getBlob: this is a varchar this is a clob this is another var this is another clob
Can you see if it works for you? The problem might be my setup although I've tried two completely different linux setups.
Thanks!
Regards, Mats Christensen
On Sat, Feb 1, 2014 at 1:12 AM, Jan-Henrik Haukeland hauk@tildeslash.com wrote: ResultSet_getBlob or ResultSet_getString should be interchangeable on a blob or text column. Though of course, if the blob column contains binary data only ResultSet_getBlob should be used to get the correct value.
I should say that a problem with using ResultSet_getString on a blob column was just fixed in Oracle and confirmed fixed in version 3.0. So with one datapoint, the above seems to work. If you want us to investigate, we are also more inclined to do so if you can provide a small test program demonstrating the problem. Or argue in detail why the problem must be in libzdb.
Best regards
Jan-Henrik Haukeland
On 31 Jan 2014, at 13:36, Mats Christensen matschristensen@gmail.com wrote:
I'm not very familiar with Oracle either but it seems like CLOB is for Oracle what TEXT is for Postgres and MySQL.
There is a function in the dbmail-code called 'db_result_get_blob', it is used for several things, like '_fetch_headers'. HEADERVALUE is a CLOB.on Oracle and TEXT on Postgres.
Perhaps this function should have been used on '_fetch_envelope' too?
It will probably be easiest for me to just use getBlob instead, as for upstream, perhaps it's a good idea to use 'db_result_get_blob' for all TEXT/CLOB-fields if it works?
Thank you very much for the help!
Regards, Mats Christensen
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
On 31 Jan 2014, at 11:34, Paul J Stevens paul@nfg.nl wrote:
the Oracle schema is the only one that defines the 'envelope' field as a CLOB. PostgreSQL and MySQL both treat it as a TEXT field. So DBMail uses ResultSet_getString to retrieve the value, not ResultSet_getBlob.
This should be fine. According to the contract, using ResultSet_getString on a blob field is okay, especially if the value is a text type and vice a versa. I do not have Oracle installed either and it is Volodymyr who has worked on this part of libzdb. Volodymyr, do you have any suggestion?
Jan-Henrik
-
Abhay Gupta -
Jan-Henrik Haukeland -
Mats Christensen -
Paul J Stevens -
Volodymyr Tarasenko