Page 1 of 1
How to set BLOB or TEXT field empty, but not NULL?
Posted: Thu 09 Jun 2011 14:04
by drd403
For TEXT fields, for example, after
Query.FieldByName('some_text_field').AsString := '';
or
Query.FieldByName('some_text_field').Value := '';
Query.FieldByName('some_text_field').IsNull still True.
Accordingly, Query.Post produces error message '#23000Column 'some_text_field' cannot be null'.
In other words, how to add (or update) record to a table what contains empty BLOB or TEXT fields which set as NOT NULL in the table?
I'm using DevartMySQLDirect.
I've asked the same question in the Embarcadero forum, no answer yet.
Posted: Fri 10 Jun 2011 12:28
by AndreyZ
Hello,
Thank you for your inquiry. We will investigate this question. As soon as we have any results, we will let you know.
Posted: Wed 15 Jun 2011 11:59
by AndreyZ
We've investigated the problem. This problem is connected with the TClientDataSet component work. When you are trying to insert an empty string, it transfers the NULL value to our driver. We cannot influence such TClientDataSet behaviour.
Posted: Wed 15 Jun 2011 20:14
by drd403
AndreyZ wrote:We've investigated the problem. This problem is connected with the TClientDataSet component work. When you are trying to insert an empty string, it transfers the NULL value to our driver. We cannot influence such TClientDataSet behaviour.
Thanks!
What about my another question:
In other words, how to add (or update) record to a table what contains empty BLOB or TEXT fields which set as NOT NULL in the table?
According you answer, it's just simply plain impossible using select *, Query.Append,..., Query.Post logic? Worked fine with BDE.
Posted: Wed 15 Jun 2011 20:27
by drd403
drd403 wrote:AndreyZ wrote:We've investigated the problem. This problem is connected with the TClientDataSet component work. When you are trying to insert an empty string, it transfers the NULL value to our driver. We cannot influence such TClientDataSet behaviour.
Thanks!
What about my another question:
In other words, how to add (or update) record to a table what contains empty BLOB or TEXT fields which set as NOT NULL in the table?
According you answer, it's just simply plain impossible using select *, Query.Append,..., Query.Post logic? Worked fine with BDE.
Yes, I just checked the MySQL server log.
BDE Query.Post creates the INSERT statement which doesn't include empty TEXT and BLOB fields.
dbExpress creates the INSERT statement which includes this fields and set the values to NULL.
What the f...? Any workaround?
What part of dbExpress responsible for this shit - CDS, Provider, SQLQuery?
Posted: Thu 16 Jun 2011 13:15
by AndreyZ
This problem is caused at the Provider level. You can look at it in the AddField nested procedure of the TSQLResolver.GenInsertSQL method (in the Provider unit). In this procedure the parameter value is set to the NULL value. You can check this question with the standard driver, it behaves in the same way as our driver. We cannot influence it.
Posted: Thu 16 Jun 2011 13:42
by drd403
AndreyZ wrote:This problem is caused at the Provider level. You can look at it in the AddField nested procedure of the TSQLResolver.GenInsertSQL method (in the Provider unit). In this procedure the parameter value is set to the NULL value. You can check this question with the standard driver, it behaves in the same way as our driver. We cannot influence it.
Thanks!
Sure I don't blame your driver
. I tested with the standard driver and this doesn't work as well.
Can you suggest any solution?
Provider's options/patching?
Use persistent fields instead of getting fields by select *?
Don't use Append,..., Post technique at all for adding records, use INSERT statement instead?
Posted: Thu 16 Jun 2011 14:47
by AndreyZ
The only workaround in this situation is to execute INSERT and UPDATE statements using the TSQLQuery component:
Code: Select all
SQLQuery.SQL.Text := 'insert into table_name(textfield) values(:textfield)';
SQLQuery.ParamByName('textfield').AsString := '';
SQLQuery.ExecSQL;
, or without parameters:
Code: Select all
SQLQuery.SQL.Text := 'insert into table_name(textfield) values('''')';
SQLQuery.ExecSQL;