How to set BLOB or TEXT field empty, but not NULL?

[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.

[AndreyZ]

Hello,

Thank you for your inquiry. We will investigate this question. As soon as we have any results, we will let you know.

[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.

[drd403]

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.

[drd403]

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?

[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.

[drd403]

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?

[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;