GetSchema COLUMN_FLAGS

Question

Similar Request

I made a similar request earlier for the DATA_TYPE property that is returned for the GetSchema method. My question now is what do the different COLUMN_FLAGS integer values represent?

Cheers,
Ryan

Answer 1

This might be my ignorance, but as far as I am aware your best bet is to dig around in the GetSchema help files on MSDN.  When I have had to work with it, I recollect having to dig through numerous MSDN pages to find what values mean what.

Wish I could help more.

AAron

Answer 2

Yeah I've had a bit of a time trying to figure out all this Schema stuff.  Can you at least point me to the MSDN articles that you looked at to retrieve some info.  Perhaps that will point me in the right direction.

Thanks,
Ryan

Answer 3

I believe that the values returned in the COLUMN_FLAGS column are OLEDB provider specific. It's used in conjunction with the DATA_TYPE column to identify the exact data type. For example, you could have a DATA_TYPE value that identifies a column data type of INTEGER and a COLUMN_FLAG which further identifies the column as an IDENTITY or AUTOINCREMENT field. 

The following article documents the mapping of SQL Server data types and the corresponding values of these two columns:

Data Type Mapping with Distributed Queries 

The following may help as well if you're working with an Excel file and the Jet OLEDB Provider:

HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET

 

Answer 4

The basic sql connection GetSchema page is here:

http://msdn2.microsoft.com/en-us/library/ms136365.aspx


Look at the bottom of the page under "Other Resources".  This is a good place to start digging.

I would also check out this article and links within the text on this page:

http://www.davidhayden.com/blog/dave/archive/2006/01/15/2734.aspx

If all else fails you might have to discern it through debugging and trial and error.

Answer 5

Well I have not really gotten anywhere.  But since I'm running Access 2003 I'll post what little data I've gleaned through trial and error.

If your DATA_TYPE is 3 and your COLUMN_FLAGS is 90 then you have an auto-increment.  The next one I want to figure out is if it is a unique column.

Answer 6

I realise this is an old thread but as i've had probems finding info i thought i'd share what i've found.

 

The OleDb.OleDbType enum is your best bet at converting the Type codes into .NET data types.

Secondly for unique and primary key information are all obtained from  GetSchema("Indexes"...)

 

In Access 2003 it seems that you are correct in 90 being the flag for AutoNumbers.

Answer 7

According to this http://msdn.microsoft.com/en-us/library/ms175970.aspx

"For the COLUMNS schema rowset, the DATA_TYPE and COLUMN_FLAGS columns represent the DBTYPE and DBCOLUMNFLAGS values. For the IColumnsInfo::GetColumnInfo interface, the wType and dwFlags members of the DBCOLUMNINFO structure represent these values."

Which leads me to find DBCOLUMNFLAGS Enumerated Type on this page

http://msdn.microsoft.com/en-us/library/ms722704(VS.85).aspx

little hunting and I find the enums listed here

http://msdn.microsoft.com/en-us/library/ms716934(VS.85).aspx

typedef DWORD DBCOLUMNFLAGS;
enum DBCOLUMNFLAGSENUM {
     DBCOLUMNFLAGS_ISBOOKMARK,
     DBCOLUMNFLAGS_MAYDEFER,
     DBCOLUMNFLAGS_WRITE,
     DBCOLUMNFLAGS_WRITEUNKNOWN,
     DBCOLUMNFLAGS_ISFIXEDLENGTH,
     DBCOLUMNFLAGS_ISNULLABLE,
     DBCOLUMNFLAGS_MAYBENULL,
     DBCOLUMNFLAGS_ISLONG,
     DBCOLUMNFLAGS_ISROWID,
     DBCOLUMNFLAGS_ISROWVER,
     DBCOLUMNFLAGS_CACHEDEFERRED,
     DBCOLUMNFLAGS_SCALEISNEGATIVE,
     DBCOLUMNFLAGS_RESERVED,
     DBCOLUMNFLAGS_ISROWURL,
     DBCOLUMNFLAGS_ISDEFAULTSTREAM,
     DBCOLUMNFLAGS_ISCOLLECTION,
     DBCOLUMNFLAGS_ISSTREAM,
     DBCOLUMNFLAGS_ISROWSET,
     DBCOLUMNFLAGS_ISROW,
     DBCOLUMNFLAGS_ROWSPECIFICCOLUMN
};

now for the integer value it will be formed by a bit mask with the enums each representing another bit of the number. Easiest is not to mess with that, but rather check the value returned against your own bit mask formed by the enum above. If you don't know how to do that... well... you might want to post on a programming forum

Answer 8

using that enum your mask is just the sum over 2 ^ DBCOLUMNFLAGS_? 

Answer 9

Have some similar problems. I can't identify AutoNumber field in my Access Table.

It does seem that 90 being the flag for AutoNumbers. But it's not usable. I mean AutoNumber does have COLUMN_FLAGS 90 and DATA_TYPE 3, but so do other fields.

Has anyone solved this issue?

Answer 10

Found the way. Feel stupid now).

If U use adapters FillSchema() method it will do it for U. I actually don't understant why it doesn't fill information about schema in DataTable when you "really" fill it with Fill() method. wasted a lot of time trying to detect autonumbers in access. anyhow here method i wrote, hope it'll save somebody time:

public string GetAutonumberByTableName(string mdbTableName)
        {
            if (String.IsNullOrWhiteSpace(mdbTableName))
                return null;
            
            if (this.dbConnection.State == ConnectionState.Closed)
                this.dbConnection.Open();
            string res = null;
            try
            {
                string statement = string.Format("SELECT * FROM [{0}]", mdbTableName);                
                OleDbCommand command = new OleDbCommand(statement, this.dbConnection);
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);

                DataTable schema = new DataTable();
                adapter.FillSchema(schema, SchemaType.Source);

                int found = 0; // the counter of autonumber columns
                foreach (DataColumn col in schema.Columns)
                {
                    if (col.AutoIncrement)
                    {
                        if ((++found) > 1)
                            throw new Exception("Cannot detect correctly autonumber column in "+mdbTableName+" table");
                        res = col.ColumnName;                        
                    }
                }                
                adapter.Dispose();
                command.Dispose();
                schema.Dispose();
            }
            catch (Exception e)
            {
                // do some work with your Exception. I generated an event                  
            }
            finally
            {
                this.dbConnection.Close();
            }
            return res;
        }

---

Anthony

Answer 11

Very nice, Anthony. Even AccessDataSource does not correctly reflect AutoNumber/Unique (PrimaryKey) on Access 2003 mdb. You have saved me many hours of frustration with this. Very strange behavior, in my opinion...but now that I know how to get the correct column attributes, it will help me down the road. Thanks again. Here is my VB.NET version of your nice code block....

    Private Function GetAccessDataTableSchema(ByVal theConnectionString As String, _
                                              ByVal theTable As String)
        Try

            Dim theConnection As New OleDbConnection(theConnectionString)

            theConnection.Open()

            Dim theSelect As String = String.Format("SELECT * FROM [{0}]", theTableName)
            Dim theOleDBCommand As New OleDbCommand(theSelect, theConnection)
            Dim theAdapter As New OleDbDataAdapter(theOleDBCommand)

            theSchemaTable = New DataTable
            theAdapter.FillSchema(theSchemaTable, SchemaType.Source)


            With DataGridView1
                .DataSource = theSchemaTable
                .Visible = True
            End With

            theAdapter.Dispose()
            theOleDBCommand.Dispose()

            Return True

        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, _
                            "jinzai studio CSV Importer", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Exclamation)
        End Try

        Return False
    End Function