How to avoid SQLEXEC() function being posting NULL value into sql server table

[MSiddeek]

Hi,

my sql table has 10 columns the first five being updated when with following function

SQLEXEC(lnHandle,"INSERT INTO table (column1, column2,..., column5) VALUES (?val1, ?val2...?val5)")

when FoxPro execute the above function the rest of the columns (column6 to column10) getting NULL automatically. is there any way I can avoid the NULL is being posted.

Regard MSIDDEEK

 

[GriffMG]

If you have control of the database design on the SQL database, you could set a non NULL default for each column.

Otherwise you could code a non NULL value into the INSERT INTO for each column

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

If the remote database is VFP based, you could make the column NOT NULL when you define it as well.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

I second Griff, specifically you have to define default values in the MSSQL table. If there are no defaults defined and a field is nullable null is taken as value, unlike VFP tables, where a missing default value is filled with a data type spcific empty value and you explicitly need to set default to NULL.

But actually, MSSQL is ANSI compliant with that behavior and VFP is not.

But - again but - VFP does not fill in NULLs, just try this without any Foxpro involvement in MS SQL Management studio:

INSERT INTO table (column1, column2,..., column5) VALUES (1, 2, ..., 5)

You'll see the other columns become NULL, also if nothing is specified for them.

So it's not VFP forcing in NULLs, it's the columns nullability that does that.

Same goes for MySQL, this is the standard of SQL. If you want some column to have a default value, you have to set that in the table definition. If you set a column not nullable and specify no default value it becomes even worse and INSERT statements without specifying values for the columns fail, because still NULL is tried to be inserted even though the SQL engine knows these columns can't be null and could use empty strings for many character data types and 0 for numeric data types, FALSE for bit fields and so on, but then it stops to have a logical value for date or datetimes, there is no empty date/datetime in any database besides VFP.

And the rest of the datasbase world here has the normal and logical concept about missing values, that is the meaning of NULL.

In essence, your two options are 1. to define default values in the table definition or 2. to specify them specifically in the insert statement. Whether to disallow null in the columns is a separate decision, as it doesn't only depend on whether a record only optionally has a value in those columns at the initial "birth" state of the life cycle of such a record, but also whether that columns can become null later,w whether that attribute can be purged without deleting the whole record.

So, you can have any combination of nullable and non-nullable fields with or without a default value. A bit off topic, as your columns obviously are nullable: Non-nullable fields without default values obviously are forcing clients to specify all values and are the least comfortable, so you're a bit luckier. But it's also a valid concept, it makes all such fields mandatory and if they are it would be an error to allow nulls and if there is no sensible default it also would be an error to define one just to prevent errors. If you have legitimate demands you have legitimate demands of clients to specify all columns values.

The combinations you can do are not designed to guarantee error-free table usage.

Ok, enough food for thought, I think. But if you're accusing VFP to force in NULLs you're barking up the wrong tree, you can turn on SQL PRofile and see for yourself what statements arrive. It's SQL Server who fills in the NULLs for unmentioned columnns when they have no default.

A simple examplem fully MSSQL contained without any Foxpro influence:

Declare @testtable as table (id int identity(1,1), somecolumn char(10) not null, othercolumn char(10) null)

Insert into @testtable (somecolumn) values ('Hello') -- works and inserts 'Hello' into somecolumn and nothing into othercolumn, which is NULL, therefore.

Select * from  @testtable

Insert into @testtable (othercolumn) values ('World') -- errors as soomecolumn isn't nullable (not null) and so some value has to be specified

Select * from  @testtable -- shows the table therefore is unchanged and only has the first record

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[MSiddeek]

Thanks Olaf, Griff
For your valuable comments and advice. Olaf I got your point it's SQL who is the culprit.
Is there any way when I recall the SQL records (ie SELECT * FROM table) these NULL words can be eliminated, so the result cursor will not have these NULL words on it?.

Thanks MSIDDEEK

 

[MSiddeek]

because the rest of the five columns are filled by another user.

 

[Olaf Doschke]

Well, you can either select COALESCE(column6,'') to change the nulls into empty strings or you get the NULLS as they are and SET NULLDISPLAY TO ''.

Also, see the discussion or rather the lengthy monologue I just had recently in thread184-1794142.

Which makes me wonder why such duplicated questions always occur so often, but that's just a side note, ignore it.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[MSiddeek]

Thank you Olaf for your valuable thoughts, I did SET NULLDISPLAY TO '', it is working fine my only concern is will it have an impact on numeric or date field?? I mean will it change a numeric field into char field, I checked on the resulting cursor but it has not changed the numeric field or date fields they are as it is.

I appreciate your dedication on this forum.

Regards MSiddeek

 

[Olaf Doschke]

NULL is the non-existing value of any type, but the from the inverted perspective that means there are typed null values. NULL itself is not any type, but a NULL in an int field is an int NULL. If you query COALESCE(column6,'') you turn it into char, even if column6 would be other types. For staying with the type there is ISNULL(), if you read the help topic on COALESCE this also is pointed out as a difference.

Anyway doing all these selects I get 0 as result value:

select ISNULL(CAST(NULL AS integer),0)

select ISNULL(CAST(NULL AS integer),'')

select COALESCE(CAST(NULL AS integer),'')

So, in the end you, better stay with your simpler typesafe SELECT *, the VFP type still will be the sql server data type, just set to NULL as "value" or non-value.

If you then set the values in the VFP cursor you need an updatable cursor to write those values back to MSSQL, so from that perspective you're not having anything from staying with the same type unless you're going for updatable SPT cursors, which is where I'd suggest using cursor adapters with cursor schema clearly defining the result cursor data type structure.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[MSiddeek]

Thanks Olaf,

I will follow as you advice, once again thank you very much for your feed back.

Regards MSiddeek