CODEWITHSUNDEEP
mysqlvbscriptasp-classicado
Combinatix
Combinatix

Reputation: 1216

How to check if Field can contain NULL (adFldIsNullable)

I have tried this code (ASP CLASSIC):

Public Function IsNullable(MyField)
    Dim RS, SQL, Tmp
    SQL = "SELECT " & MyField & " FROM mytable WHERE 1;"
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.Open SQL, conn 'I have connection opened elsewhere, using Driver={MySQL ODBC 5.2w Driver}

    'Now check for Attributes
    Tmp = RS.Fields(MyField).Attributes
    IsNullable = 0 <> (Tmp And adFldIsNullable) '0x20
    RS.Close
    Set RS = Nothing
End Function

Function works fine but sometimes the result is wrong. For example it returns True for ID field, which is definitely not nullable as it is Primary Index Autoincrement. How can I make it relable?? Thanks

Added: It seems like when column is set to AutoIncrement Not Null then function works wrong...

Upvotes: 1

Views: 1144

Answers (2)

Kul-Tigin
Kul-Tigin

Reputation: 16950

It's by design according to http://bugs.mysql.com/bug.php?id=3857

[21 Jul 2004 22:26] Timothy Smith (Senior Support Engineer in those days)

This is because MySQL reports the DEFAULT value for such a column as NULL. It means, if you
insert a NULL value into the column, you will get the next integer value for the table's
auto_increment counter.

It is still valid and @kordirko's solution is quite acceptable.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

In MySql you can query information_schema.columns view:

SELECT is_nullable
FROM information_schema.columns
WHERE table_schema = 'my_schema'
  AND table_name   = 'table-name'
  AND column_name = 'column name'

see this demo: http://www.sqlfiddle.com/#!2/743d6/3

Upvotes: 1

Related Questions