VFP - Sqlserver null field property 1

[Amferreira]

VFP - How can I check the nullability of a sqlserver Field ?

 

[Mike Lewis]

Execute sp_help (a system stored procedure), passing the name of the table as a parameter.

The procedure returns two cursors. The first one contains a single row. Ignore this. In the second cursor, look for a row that contains your field name in the Column_name field. Then look at the Nullability field in that row. It will contain yes or no, depending on the field's nullability.

You can do all this from within VFP by sending the sp_help command via SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Here's some VFP code that will show you how to do it:

lnConn = SQLCONNECT("MyODBCDataSource")
lcCmd = "EXEC sp_help 'Customers'"
SQLEXEC(lnConn, lcCmd, "csrHelp")
SELECT Nullable FROM csrHelp1 ;
  WHERE ALLTRIM(LOWER(Column_name)) == "cust_name" ;
  INTO ARRAY laTemp
IF ALLTRIM(LOWER(laTemp(1))) == "yes"
  * Field is nullable
ELSE
  * It's not nullable
ENDIF

In the above code, MyODBCDataSource is the ODBC data source; you can also use SQLSTRINGCONNECT() with a connection string. Customers is the name of your table. Cust_Name is the name of your field.

I've just tested it and it works for me. Give it a try, and come back if any problems.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Or use SQLColumns().

lnConn = SQLCONNECT("MyODBCDataSource") && or use SQLStringConnect, you know best yourself how you connect
SQLCOLUMNS(lnConn, 'Customers', 'NATIVE', 'curServerColumns')

Then look in curServerColumns, it has a field Nullable.

Bye, Olaf.

 

[Olaf Doschke]

Besides also the AFIELDS array of the cursor you retrieve, when you query data, tells you if the field is nullable, that is inherited.

Bye, Olaf.

 

[Mike Lewis]

Or use SQLColumns()

... which has the advantage of not being specific to SQL Server.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads