Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-15 08:30:14 |
We've been using HXTT DBF in production for several years now, and in a recent project I've noticed that there is an insert command which completes successfully, however, there are several columns which are set to not allow nulls, which are being populated by nulls after the insert command.
Is there some setting that I have missed which guarantees that the insert command honors the non-nullable setting?
Thanks.
|
Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-15 12:04:29 |
Visual Foxpro format provides nullable column.
>however, there are several columns which are set to not allow nulls, which are being populated by nulls after the insert command.
But maybe you wish to use emptyStringAsNull=false connection property.
emptyStringAsNull: Indicates whether returns empty string as null value. You can use null, true, false Default value: true
|
Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-15 12:08:27 |
I am already using the emptyStringAsNull connection property, and it correctly returns nulls as empty strings.
However, my problem is on inserts. If I only set a few of the column in the insert statement, HXTT fills the other columns with nulls, and the schema of the table has most of the columns as non-nullable. The insert succeeds, but then the record is not correct and causes problems in VFP when the nulls are encountered. Other solutions return an error when we try to insert nulls into non-nullable columns.
|
Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-17 08:15:18 |
If you used nullable columns in VFP table, that table will use null value when there's no assigned value in INSERT sql, since HXTT DBF support VFP's nullable column.
You can send support@hxtt.com a sample.
|
Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-18 06:02:53 |
Yes, that's true, but my problem is, I have non-nullable columns, and if I don't explicitly set a value for the insert statement, HXTT allows the insertion of null values in those columns. That seems incorrect, and VFP doesn't seem to catch it either.
For example, if I have a table, address, defined to have a name, streetAddress, city, state columns, all non-nullable, and I then do INSERT into address (name) values("John Smith"), the results show that the name column is set correctly, but the other columns all contain nulls. We have another product (JDataConnect), which I believe handles such a case correctly, flagging that insert with an error: "Insert error: column streetAddress cannot contain null value."
Since my actual table contains over 100 columns, creating insert statements with every column value set is burdensome, and I was hoping that HXTT would supply defaults for those non-nullable columns. I appreciate if it can only be done for string and numeric data, but that would go a long way toward solving this problem for me.
|
Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-18 06:44:37 |
Since you have used emptyStringAsNull=true, then your columns should be VFP's nullable column. HXTT DBF can distinguish ''(empty string) and null value for VFP's nullable column.
For instance, if
select * from address where streetAddress=null;
select * from address where streetAddress='';
can return different result.
If possible, you can send us a table sample, so that we can check and provide a solution. Maybe we can provide a connection property to forbid null value for your table?
|
Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-18 07:18:40 |
No, I'm using emptyStringAsNull=false. I want to get back empty strings in my queries when a column is null.
My problem isn't queries, it's inserts.
How do I send you a table sample?
|
Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-18 07:19:35 |
|
Typo. Yeah, emptyStringAsNull=false. You can send support@hxtt.com those files.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-18 08:07:28 |
I have sent the requested information to support@hxtt.com
Let me know if you need anything else.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-18 16:56:22 |
|
I need a table (*.dbf) sample, because VFP's column type can be nullable, but it can store null value for old data type also.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-18 16:57:48 |
|
For instance, if you won't null value for datetime(timestampe) column, you want to get a 1970-01-01 value?
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-19 07:54:17 |
I can tell you that our foxpro application puts in blanks for non-nullable varchar columns, and zero (0) for non-null numeric fields, and blank for date/time columns. Our .dbf table is huge, so I really can't send that right now.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-19 18:47:34 |
|
So your issue is that your Foxpro application can distinguish ''(empty string) and null value for VFP's nullable column, so that it'll throw error if you missed the default empty value in your INSERT sql. I think that we can add a switch connection property so that it will complement automatically empty value, not null value, for your INSERT sql.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-19 22:27:45 |
Add new connection property:
emptyStringAsNull=false;emptyDateAsNull=false;emptyDecimalAsZero=true
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-20 10:43:37 |
This sounds good, but I already have the string and decimal properties set, and the inserts I had were failing on inserts of nulls to numeric fields.
Anyway, I'll try again; do I need to download a new jar file?
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-20 12:03:52 |
|
And I'd rather it didn't throw an exception but instead insert a non-null value where needed.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-20 16:39:32 |
|
With emptyStringAsNull=false;emptyDateAsNull=false;emptyDecimalAsZero=true , HXTT DBF will return "", 1970-01-01, 0 for null value.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-20 16:40:18 |
|
You need to download the HXTT DBF 7.0.011 packages which were released yesterday.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-21 07:56:28 |
You say it will 'return' those values, but this whole thread started with me wondering how the INSERT functionality worked, and if said INSERT statements would put non-null values in columns that are flagged as non-null.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
HXTT Support |
2019-03-21 17:08:20 |
> if said INSERT statements would put non-null values in columns that are flagged as non-null.
According to my test, for VFP's nullable column, HXTT DBF will insert empty value in default. I think that you need that feature to return non-null value.
|
Re:Re:Re:Re:Re:Re:Re:Re:Insert Doesn't Honor Non-Nullable Columns |
Kevin M Penrose |
2019-03-22 12:13:20 |
|
I will test and let you know how things work.
|
Main
webmaster@hxtt.com