Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
Insert Doesn't Honor Non-Nullable Columns
Kevin M Penrose
2019-03-15 08:30:14.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
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.0
> 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.0
I will test and let you know how things work.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap