Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
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
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-08 03:56:56.0
Hello,

I did read this question/answer: http://www.hxtt.com/support_view_issue.jsp?product=dbf&id=1277743708

Is it possible to add support for at least detecting that a VFP column is marked as NOT NULL? VFP did support that in the last few versions:
http://msdn.microsoft.com/en-us/library/z1cc2za0%28v=vs.71%29.aspx

Regards,
Andrei Costescu
Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-08 05:04:33.0
>Is it possible to add support for at least detecting that a VFP column is marked as NOT NULL?
In VFP, if you use
CREATE TABLE table1(a varchar (10) null, b varchar (10) NOT null,c varchar (10) ) to create a free table, you will find not null constraint has been ignored
INSERT INTO table1 (a) values('aaa')
will work too without any error.

Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-08 05:06:05.0
So HXTT DBF supports only null flag for VFP.
Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-08 06:08:22.0
Hmm.
Maybe it's related to VFP version or SET NULL OFF status when doing the INSERT.
VFP is able to block such null inserts for not null columns. I'll try to get back with more info soon.
Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
Sebastian Schlatow
2014-04-08 06:50:47.0
Example for not allow null into table column.:

http://i.stack.imgur.com/Qx1y7.png
Re:Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-08 07:00:16.0
In that example, it would probably have erred out sooner if it was SET NULL ON, cause otherwise the inserts will use default type non null values for unspecified columns - and not complain.
Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-08 07:05:55.0
SET null OFF results that prompt. If you use
INSERT INTO table1 (c) values(null)
You will find it refuse to insert null value for c column too.
So no way to add not null constraint in table level.
Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-08 07:18:15.0
But that's because the CREATE TABLE is affected by SET NULL OFF as well - so it creates the column as NOT NULL.
In Sebastian's example if you start with SET NULL ON instead of OFF, you are saying it will not error out?
Re:Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-08 07:39:14.0
>But that's because the CREATE TABLE is affected by SET NULL OFF as well - so it creates the column as NOT NULL.
Because my sample is created without "SET NULL OFF". If I used "SET NULL OFF", c columnn doesn't allow null too. If use "SET NULL ON", b column will allow null too.

Re: Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-08 23:57:39.0
Sebastian, can you confirm that? If yes then you can ignore the following...

My understanding was that SET NULL ON/OFF affects both CREATE and INSERT.
So if you have SET NULL ON, CREATE will create by default columns that allow null and INSERT will insert null for unspecified column values. And SET NULL OFF would CREATE columns that do not allow null, and INSERT would use blank (default) values based on type for unspecified column values instead of null.

Also when using CREATE you can override the value of SET NULL ON/OFF by specifying NOT NULL or NULL directly on the column.

http://msdn.microsoft.com/en-us/library/aa978399%28v=vs.71%29.aspx

So in that example where only one column 'b' in CREATE had NOT NULL, when SET NULL OFF was used all columns were created to not allow null; when SET NULL ON was used all columns except b would be created to allow null. But unfortunately I don't have VFP at hand to try it out right now.
Re:Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
Sebastian Schlatow
2014-04-09 01:01:22.0
SET NULL ON
CREATE TABLE table1(a varchar (10) null, b varchar (10) NOT null,c varchar (10) )
INSERT INTO table1 (a) values('aaa')

http://i.imgur.com/T4vZfOK.png

SET NULL OFF
CREATE TABLE table1(a varchar (10) null, b varchar (10) NOT null,c varchar (10) )
INSERT INTO table1 (a) values('aaa')

http://i.imgur.com/mi0qbVZ.png
Re: Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-09 02:05:11.0
So:
- 'b' does not allow null when SET NULL ON is used. (tested with Visual FoxPro 9.0 SP2)
- 'b' does not allow null when SET NULL OFF is used. INSERT just inserts "" in that case instead of null because of SET NULL OFF mode. If you would try to explicitly insert null as in one of the previous messages, you would still get an error.

NOT NULL info is stored at table column level.
Re:Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-09 02:30:20.0
I don't think so.
Create a table first,
then
with SET NULL ON
you will see b and c column doesn't accept null value.
with SET NULL OFF
you will see b and c column accept empty string value.
The conclusion is "SET NULL is scoped to the current data session".
In VFP, only null flag is useful, and a nullable column can accept null value and empty string.





Re:Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-09 05:04:21.0
:) This discussion is getting long.

>Create a table first,
In which mode? NULL OFF or NULL ON?

>with SET NULL OFF
>you will see b and c column accept empty string value
Yes, that's what I said as well; it allows empty. But I was talking about null value which is not an empty value. It also fails for you if you try to insert null here right? 'b' does not accept null. 'c' accepts null or not depending on the mode when creating the table.
See http://i.stack.imgur.com/Qx1y7.png again. It does:

INSERT INTO table1 (b) values(null)

So what I am saying is that because this insert fails no matter if SET NULL is ON or OFF, the HXTT jdbc driver should also report that column B is not nullable (thought IS_NULLABLE and NULLABLE in connection metadata.getColumns()). Ideally it would even enforce that for INSERT and other queries as it happens inside VFP.

Or am I missing something?
Re:Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-10 07:13:48.0
SET NULL OFF
CREATE TABLE table1(a varchar (10) null, b varchar (10) NOT null,c varchar (10) )
INSERT INTO table1 (a) values('aaa')
INSERT INTO table1 (b) values('aaa')
INSERT INTO table1 (c) values('aaa')
INSERT INTO table1 (b) values(null) /* Field b does not accept null value*/
INSERT INTO table1 (c) values(null) /* Field c does not accept null value*/

So both of b and c column doesn't accept null value.
In VFP, there's only nullable column, which accepts null value.
HXTT DBF allows to INSERT INTO table1 (c) values(null), but store as empty string. So null, '', and 'n space', has the same store result.
HXTT DBF provides emptyStringAsNull connection, which indicates whether returns empty string as null value.
Re:Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-16 08:56:02.0
Your example:
SET NULL OFF
CREATE TABLE table1(a varchar (10) null, b varchar (10) NOT null,c varchar (10) ) /* ***c is created with NOT NULL*** type because of line above */
INSERT INTO table1 (a) values('aaa') /* in b and c blank values '' are inserted by default because of SET NULL OFF */
INSERT INTO table1 (b) values('aaa') /* in a and c blank values '' are inserted by default because of SET NULL OFF */
INSERT INTO table1 (c) values('aaa') /* in a and b blank values '' are inserted by default because of SET NULL OFF */
INSERT INTO table1 (b) values(null) /* Field b does not accept null value */
INSERT INTO table1 (c) values(null) /* ***Field c does not accept null*** value because it was created that way */

My example:
SET NULL ON
CREATE TABLE table1(a varchar (10) null, b varchar (10) NOT null,c varchar (10) ) /* ***c is created with ALLOW NULL** type because of line above */
INSERT INTO table1 (a) values('aaa') /* in b and c null values are to be inserted by default because of SET NULL ON. ***Fails*** because b does not accept null values. */
INSERT INTO table1 (b) values('aaa') /* in a and c null values are inserted by default because of SET NULL ON */
INSERT INTO table1 (c) values('aaa') /* in a and b null values are to be inserted by default because of SET NULL ON. ***Fails*** because b does not accept null values. */
INSERT INTO table1 (b) values(null) /* Field b does not accept null value */
INSERT INTO table1 (c) values(null) /* ***Field c does accept*** null value because it was created that way but fails because of b which doesn't accept default null value */
INSERT INTO table1 (b, c) values('aaa', null) /* ***Field c does accept*** null value because it was created that way */

That would suggest that NOT NULL / NULL info is kept and used at column level.
Hope my example is valid.
Re:Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-19 01:37:58.0
Supported in DatabaseMeta.getColumns
Re:Visual Fox Pro DBF - NOT NULL support
Andrei Costescu
2014-04-22 22:59:32.0
That is good news.
But for me it didn't work - using 5.1.202.

Looked at NULLABLE and IS_NULLABLE.
I could provide a sample .DBF if needed.
Re:Re:Re:Re:Re:Re:Re:Re:Visual Fox Pro DBF - NOT NULL support
HXTT Support
2014-04-23 00:04:00.0
The latest version is v5.1.214 .

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap