Visual Fox Pro DBF - NOT NULL support |
Andrei Costescu |
2014-04-08 03:56:56 |
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 |
>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 |
|
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 |
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 |
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 |
|
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 |
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 |
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 |
>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 |
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 |
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 |
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 |
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 |
:) 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 |
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 |
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 |
|
Supported in DatabaseMeta.getColumns
|
Re:Visual Fox Pro DBF - NOT NULL support |
Andrei Costescu |
2014-04-22 22:59:32 |
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 |
|
The latest version is v5.1.214 .
|
Main
webmaster@hxtt.com