Active Query Builder support area

Loading Metadata XML

Avatar
Andrew Kennard
  • updated
  • Completed

Hi

I wish to interrogate the SQL database with something like this

SELECT TABLE_NAME,

COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name like 'vw%'

order by table_name,ORDINAL_POSITION

and I then wish to produce the MetaData XML so that I can load it into the control

Most of the information I need seems obvious except what is the size element for and how should I calculate it ?

<field>

<name>

<item name="Amount" case_sens="1" />

</name>

<type_name>numeric</type_name>
<type platform="net">Decimal</type>
<size>17</size>
<precision>10</precision>
<scale>2</scale>
<nullable>0</nullable>
</field>
Replies 10
Avatar
0
Andrew Kennard

Also what is being used in the MSSQL database to determine the value for the case_sens attribute

Thanks

Avatar
0
Andrey Zavyalov, PM

Hello,

Size is the size of field in symbols. Meaningful for string data types only. You can fill it from the CHARACTER_MAXIMUM_LENGTH column.

"case_sens" must be false for MS SQL Server as this server treats all identifiers as case insensitive.

Avatar
0
Andrew Kennard

Thanks very much

If I create this XML doc by hand is the type element important in the sense I am not using .NET only ActiveX

<type platform="net">Decimal</type>

Avatar
0
Andrey Zavyalov, PM

You should keep the platform="net" attribute. Otherwise you'll have to enter numeric codes for your data types. The following values are acceptable for the Type property: Object, AnsiString, Int16, Int32, UInt16, Boolean, Double, Currency, Decimal, Date, Time, DateTime, Binary, AnsiStringFixedLength, String, Int64, GUID.

Avatar
0
Andrew Kennard

I've just used this little tool to get all the unique element names from MetaData XML

http://taporware.ualberta.ca/~taporware/xmlTools/listxml.shtml

There are two more i'm not quite sure where to get the info from as it does not seem to be in INFORMATION_SCHEMA.COLUMNS

That is for readonly and primary_key

Also this sample MetaData was created using your editor and it seems to have a mix of case_sens=0 and 1 against a mssql database ?

Thanks again as always

Avatar
0
ActiveDBSoft support team

Hi,

MSSQLSyntaxProvider uses sys.indexes and sys.index_columns system views to calc primary key flag.

AQB currently does not use "readonly" attribute, so you can omit it in generated XML.

For case-insensitive SQL dialects like TransactSQL case_sens=0 is the better choice.

Please note - there are XSD schema exists for metadata XML format, which describes all possible attributes for all type of nodes in metadata XML file. The reference to this schema present in the header of every metadata file saved by QueryBuilder. Advanced XML editors can use this schema for code completion while you editing the document, and verify correctness of result XML.

Avatar
0
Andrew Kennard

Thanks

and finally (hopefully!)

Do you have any general problems with people using your control and not having permissions to INFORMATION_SCHEMA i.e. by default 'normal' users do not tend to have read permissions for this INFORMATION_SCHEMA database ?

Avatar
0
Andrey Zavyalov, PM

We use various ways of loading metadata: querying the INFORMATION_SCHEMA, getting lists of columns by preparing a "SELECT * FROM table" query, querying specific methods of OLE DB provider.

We haven't had any problems with this.

Avatar
0
Andrew Kennard

OK I have successfully added a view to the metadata and it shows up how I would expect

However when I choose columns they appear in the expression grid as

database.dbo.table.field

rather than if online mode was used to load the data when it would be

table.field

I tried setting the MetDataContainer.DefaultDatabase but that did not seem to make any difference ?

Thanks

Avatar
0
Andrey Zavyalov, PM

Default database and schemas should be defined either in the XML file or after the file is loaded to the MetadataContainer.