Blank instead of .Null. on append from SQL (VFP 6)

[jestin1979]

There is a table on SQL Server that I'm trying to bring down to an FP table. The table doesn't allow nulls in a couple of numeric fields, but in SQL they are NULL. If I append a blank row, and fill this row out by hand, I could leave these non-nullable fields alone and the row would save, Even with 'nothing' in them. What I'd like to be able to do is fill these fields with 'nothing' instead of NULL. (As this is some very old legacy code I'm dealing with, I'm hesitant to allow nulls in these fields for fear of breaking something seemingly unrelated).

The only thing I can think of to do off the top of my head would be to append a blank row, then update only the fields I want. That sounds like a horrible way to do this.

Any suggestions?

 

[DSummZZZ]

When you say 'bring down', what are you doing? Are you doing an INSERT INTO? SCATTER/GATHER?

You should check into using NVL(). You could either loop through the fields to test for a null value and substitute a blank value, or just hard code it for the fields you already know are null.



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

As Dave says, we need to know how you are "bringing down" the data. I'll assume for now that you are using SQLEXEC() to create a local cursor in VFP.

If that's so, then you need to use the ISNULL() function to convert the NULLs to some other value. ISNULL() is SQL Server's equivalent of VFP's NVL().

So the SELECT statement that you send to the server will be something like this:

SELECT ... ISNULL(SomeField, 0) ... FROM ... etc.

This will replace the NULL in SomeField with a numeric zero.

Note that you can't use this method to make the local field completely empty. Given that it's numeric, it has to have some value, even if it's zero.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Just to add ....

If you are using a remote view or a cursor adapter rather than SQLEXEC(), the above will still apply. In both of those, there is an underlying SELECT statement, so it's a matter of editing that statement to include the ISNULL() function, as per my previous post.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

You could retrieve the data and then convert all fields with .NULL. to blank via BLANK FIELDS ...

BLANK FIELDS field FOR ISNULL(field)

And then you simpl can append that data to the dbf.

ISNULL(), as Mike suggest, would be able to replace those NULL Values server side already, but not with BLANK, this is a VFP only concept, so if you'd like BLANK numbers instead of 0 then you need to postprocess the cursor received by SQLEXEC() or a remote view or whatever you use.

Bye, Olaf.

 

[Dan Freeman]

I've read the question several times over and admit I can't figure out what you're asking.

The usual complaint with VFP using SQL-Server data is that null values on the server get inadvertently replaced with "empty" values from VFP, making VFP a menace to enterprise data where NULL actually may mean something.

It sounds like you have this problem coming in the other direction, which makes little sense.

(You are right to want to prevent NULL in an existing VFP application unless you're certain every expression in the app is NULL-proof. They almost never are.)

What are you really asking?

 

[Olaf Doschke]

Dan has a point there,

as far as I understand your situation you want to prevent changing dbfs to allow nulls, as they are not nullable, but you get nulls from another sql server database.

To rephrase dan's question I'd ask, if the dbfs actually already have blank (not NULL and not 0) values in the fields, so you know the legacy application can work with blank values. If not then you should not only prevent NULLs but also BLANK values or put in other words BLANKs then will cause as much unknown behaviour or sie effects than NULLs could.

Bye, Olaf.

 

[jestin1979]

Dan is correct; I'm having the problem of nulls in sql, but wanting blanks in FP. I'm using SQLEXEC to bring it into a local cursor, then appending to a table from that cursor.

Just after posting this I found the BLANK command. What I'm thinking of doing is an ISNULL in the sql to set the ones I don't want to a negative number, since there shouldn't be any, then use BLANK in FP to set them.

 

[jestin1979]

To clarify another point, when I run my FP program, I get an error saying the field doesn't accept NULL values.

 

[Olaf Doschke]

You misunderstood, justin.

It's clear that a non nullable field will give you an error, if appending NULLs.

You could also solve that by changing table fields to allow nulls, but your concern this will have side effects is a good concern, and you should have that smae concern about BLANK fields.

If, as you say, you don't want NULLs, then ask yourself if that result is really making any sense at all and if it's not your querying of data from SQL Server, which should change to not result in records containing NULLs.

Your solution just solves the technical problem to add data to the dbf. Still, if you now don't have any NULLs by technical restriction and also have no BLANKs in the dbf, you also don't know if BLANKs will have a negative effect.

Dan's point rather is, why you take the NULLS you get from SQL Server for granted, is this really what SQL Server should give you? Is ammending this by setting those values to 0 or blank or anything else really mending the real problem? That's his question.

Bye, Olaf.

 

[jestin1979]

The blanks in SQL are by OK. They are part of an online form that can be filled in, but don't need to be. They are then brought down to a VFP table, then read into a screen. On this screen I want blank values if the website user didn't fill anything in. The fields in the FP table are not new, and have always been initially blank. The ability for a user to put something in these fields remotely is new.

 

[jestin1979]

Sorry, the Nulls in SQL are OK.

 

[Olaf Doschke]

Ok, then you would be safe using BLANK FIELDS before appending the data. You can use ISNULL(field,-987) to init with some negative value and BLANK FIELDS field FOR field<0 or you can retreive NULLs and BLANK FIELDS field FOR ISNULL(field), that doesn't matter much.

Bye, Olaf.