GetOleDbSchemaTable(OleDbSchemaGuid.Columns,...

[Markus Ohlenroth]

hello outside,

when i retrieve column information on an acces table:
by using: GetOleDbSchemaTable(OleDbSchemaGuid.Columns,

I get something like the following:

<field TABLE_CATALOG="" TABLE_SCHEMA="" TABLE_NAME="articles"
COLUMN_NAME="ID" COLUMN_GUID="" COLUMN_PROPID="" ORDINAL_POSITION="1"
COLUMN_HASDEFAULT="False" COLUMN_DEFAULT="" COLUMN_FLAGS="90"
IS_NULLABLE="False" DATA_TYPE="3" TYPE_GUID="" CHARACTER_MAXIMUM_LENGTH=""
CHARACTER_OCTET_LENGTH="" NUMERIC_PRECISION="10" NUMERIC_SCALE=""
DATETIME_PRECISION="" CHARACTER_SET_CATALOG="" CHARACTER_SET_SCHEMA=""
CHARACTER_SET_NAME="" COLLATION_CATALOG="" COLLATION_SCHEMA=""
COLLATION_NAME="" DOMAIN_CATALOG="" DOMAIN_SCHEMA="" DOMAIN_NAME=""
DESCRIPTION="" />

the meaning of some of the information is obvoius, but some is not.

Can anybody point me to a documentation on this information?

thanks
Markus

 

[Stefan Hoffmann]

hi Markus,

when i retrieve column information on an acces table:
by using: GetOleDbSchemaTable(OleDbSchemaGuid.Columns,

Why don't you use the DAO.TableDefs collection?

Can anybody point me to a documentation on this information?

Maybe

http://msdn.microsoft.com/de-de/lib...edbconnection.getoledbschematable(VS.80).aspx

?


mfG
--> stefan <--

 

[Markus Ohlenroth]

hi Stefan
thanks for your information.

Maybe I was inaccurate in my question.

But actually I was looking for documentation on the information returned,
like e.g.
COLUMN_FLAGS="90"
and not on how the GetOledbSchemaTable works

 

[Stefan Hoffmann]

hi Markus,

Maybe I was inaccurate in my question.

Yes, maybe .)

But actually I was looking for documentation on the information returned,
like e.g.
COLUMN_FLAGS="90"
and not on how the GetOledbSchemaTable works

http://support.microsoft.com/kb/309681

see the links at the end. Maybe also

http://msdn.microsoft.com/en-us/library/kcax58fh.aspx


mfG
--> stefan <--

 

[Markus Ohlenroth]

hi Stefan,
which link do you mean?
I could not find one piece of documentation on the values the returned field
named
COLUMN_FLAGS can take.
mfg
Markus

 

[Stefan Hoffmann]

hi Markus,

which link do you mean?
I could not find one piece of documentation on the values the returned field
named
COLUMN_FLAGS can take.

http://msdn.microsoft.com/en-us/library/bb677266.aspx


mfG
--> stefan <--

 

[Markus Ohlenroth]

hi Stefan,
thank you for your answer

Forgive me, maybe I am just too knew to ms access but I cannot see the
connection between the links you are offering and the question I have

For instance the last link you offer:

http://msdn.microsoft.com/en-us/library/bb677266.aspx

It refers to SQL Server Date/Time and Schema Rowsets - as fas I can see -
not to MS access. Can you give me a clue how your links have to do with the
values the field COLUMN_FLAGS can take

I figured out that the
The values COLUMN_FLAGS - as one example - can take when reading from access
via GetOledbSchema has to to with autoincrement and type properteis of a
field.

But where is the documentation from Microsoft for these values?
Or how do I connect the information you offer with my question?

thanks
Markus
This thread does not seem to get us anywhere

 

[Stefan Hoffmann]

hi Markus,

Forgive me, maybe I am just too knew to ms access but I cannot see the
connection between the links you are offering and the question I have

And I don't see the connection to Access. Can you provide a complete
code example with a brief description of your goal(s)?


mfG
--> stefan <--

 

[Markus Ohlenroth]

hi Stefan,
I work with visual studio, C# and read out metadata from access databases
via oledb

one method I use is : GetOledbSchemaTable
cf:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getoledbschematable.aspx


the returned values are stored in a datatable:
DataTable schemaTable;
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,....


if you query an access database and write the datatable to xml you get
something like:

<field TABLE_CATALOG="" TABLE_SCHEMA="" TABLE_NAME="articles"
COLUMN_NAME="ID" COLUMN_GUID="" COLUMN_PROPID="" ORDINAL_POSITION="1"
COLUMN_HASDEFAULT="False" COLUMN_DEFAULT="" COLUMN_FLAGS="90"
IS_NULLABLE="False" DATA_TYPE="3" TYPE_GUID="" CHARACTER_MAXIMUM_LENGTH=""
CHARACTER_OCTET_LENGTH="" NUMERIC_PRECISION="10" NUMERIC_SCALE=""
DATETIME_PRECISION="" CHARACTER_SET_CATALOG="" CHARACTER_SET_SCHEMA=""
CHARACTER_SET_NAME="" COLLATION_CATALOG="" COLLATION_SCHEMA=""
COLLATION_NAME="" DOMAIN_CATALOG="" DOMAIN_SCHEMA="" DOMAIN_NAME=""
DESCRIPTION="" />


now my question once again. Where can I find documentation on the values
each field can take?
thanks Markus


You wanted a complete code example:

Below I have added my first try on reading metadata from an access database.
It builds xml from all tables and fields in an access database. It is quick
and dirty, based on a number of assumptions (e.g. dbmetadata.xml file should
exist, linq namespace, xml namespace, etc.).


private void btnGetOledbSchemaTable_Click(object sender, EventArgs e)
{

XElement newTables = new XElement("tables", null);
DataTable schemaTable;
String ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source='d:\\temp\\Database.mdb'";
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();

// add all table names to newTables
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });


for (int i = 0; i < schemaTable.Rows.Count; i++)
{
newTables.Add(new XElement("table",new
XAttribute("Name",schemaTable.Rows.ItemArray[2].ToString())));
}

schemaTable.Clear();
XNode node;
XNode fieldNode;
node = newTables.FirstNode;
do {

//retrieve all field names to the schemaTable
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
new Object[] { null, null, (node as
XElement).Attribute("Name").Value, null });
(node as XElement).Add(new XElement("fields",null));

//get the node just added
fieldNode = (node as XElement).LastNode;

schemaTable.WriteXmlSchema("D:\\temp\\schemaTable.xsd");


for (int k = 0; k < schemaTable.Rows.Count; k++)
{
((node as XElement).LastNode as XElement).Add(new
XElement("field",null));

for (int l = 0; l < schemaTable.Columns.Count; l++)
{
((fieldNode as XElement).LastNode as XElement).
Add(new
XAttribute(schemaTable.Columns[l].ColumnName,schemaTable.Rows[k].ItemArray[l].ToString()));
};
}
schemaTable.WriteXml("d:\\temp\\schemaTable.xml");
schemaTable.Clear();

}
while ((node = node.NextNode)!= null );
con.Close();
XDocument po = XDocument.Load("d:\\temp\\dbMetaData.xml");
XElement oldTables = new XElement("tables", null);

try
{
oldTables =
po.Root.DescendantsAndSelf().Elements("tables").First();
oldTables.ReplaceWith(newTables);
}
catch (System.InvalidOperationException)
{
po.Root.Add(newTables);
}
finally
{
XmlWriter writer = XmlWriter.Create("d:\\temp\\dbMetaData.xml");
po.WriteTo(writer);
writer.Flush();
writer.Close();
}

}
}

 

[Stefan Hoffmann]

hi Markus,

I work with visual studio, C# and read out metadata from access databases
via oledb

Now, your source gives me the clues I was missing, take a look at these:

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

and

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


mfG
--> stefan <--

 

[Markus Ohlenroth]

thanks Stefan,
Yes, your links are what I needed.

In the refrence it says:
DBCOLUMNFLAGS Enumerated Type

Now I want do bitwise querying with the constants mentioned in the msdn
reference (Icolumnsinfo:getcolumninfo).

like eg. with
DBCOLUMNFLAGS_CACHEDEFERRED
or
DBCOLUMNFLAGS_ISFIXEDLENGTH


But I do not know how I can access these constants in my c# vs .net program?
through what class, library, namespace are these constants qualified?
Can you give me a hint?
thanks


wfg
Markus

 

[Stefan Hoffmann]

hi Markus,

But I do not know how I can access these constants in my c# vs .net program?
through what class, library, namespace are these constants qualified?

http://social.msdn.microsoft.com/Search/en-US/?query=DBCOLUMNFLAGS_ISNULLABLE&ac=8
http://msdn.microsoft.com/en-us/library/ms716934.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
};
I'm not sure, it's C/C++, but this should translate to the 2^order for
the bit values. But you better ask in a more C/C++ specific newsgroup.

Can you give me a hint?

hmm, I can, show more patience will searching in the MSDN .)


mfG
--> stefan <--