This project has moved. For the latest updates, please go here.

DB2 iSeries

Mar 13, 2015 at 5:48 PM
Does the DatabaseSchemaReader work with DB2 iSeries? If so, what is the correct provider string? Thanks.
Coordinator
Mar 13, 2015 at 6:28 PM
This is what I use for a local test on Db2-Express-C. I've also used it against Db2 on z/OS.
const string providername = "IBM.Data.DB2";
const string connectionString = @"Server=localhost:50000;UID=db2admin;pwd=db2;Database=Sample";
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Different versions of IBM's ADO driver might have breaking changes. If it's a .net 1.0 driver, you're out of luck.
If you have any errors, let me know the details and I'll see what I can do.
Mar 13, 2015 at 7:39 PM
Edited Mar 13, 2015 at 7:41 PM
MartinJW,

I got connected using "IBM.Data.DB2.iSeries" as the provider. Unfortunately, this code ...
DatabaseReader d = new DatabaseReader(myConnectionString, "IBM.Data.DB2.iSeries");
DatabaseTable t = d.Table("Customer");
... throws this exception:
Critical: System.ArgumentException: Column 'SCHEMA' does not belong to table Columns.
   at System.Data.DataRow.GetDataColumn(String columnName)
   at System.Data.DataRow.get_Item(String columnName)
   at DatabaseSchemaReader.Conversion.SchemaConverter.FindSchema(DataTable dt)
   at DatabaseSchemaReader.SchemaExtendedReader.Table(String tableName)
   at DatabaseSchemaReader.DatabaseReader.Table(String tableName)
I'm not referencing columns at all.

Advice?
Mar 13, 2015 at 7:42 PM
Remember, we are talking about DB2 iSeries.
Coordinator
Mar 13, 2015 at 8:06 PM
Yep, unfortunately the DB2 providers must have significant differences. Thanks, IBM.
I don't have a DB2 iSeries readily available, but with your help we should get it running.

Can you run this and give me the console output?
DatabaseSchemaReader.Utilities.DiscoverProviderFactory.Discover(myConnectionString, "IBM.Data.DB2.iSeries");
This should tell me what the iSeries provider schema looks like.
Mar 13, 2015 at 8:29 PM
MetaDataCollections
DataSourceInformation
        CompositeIdentifierSeparatorPattern     String
        DataSourceProductName   String
        DataSourceProductVersion        String
        DataSourceProductVersionNormalized      String
        GroupByBehavior GroupByBehavior
        IdentifierPattern       String
        IdentifierCase  IdentifierCase
        OrderByColumnsInSelect  Boolean
        ParameterMarkerFormat   String
        ParameterMarkerPattern  String
        ParameterNameMaxLength  Int32
        ParameterNamePattern    String
        QuotedIdentifierPattern String
        QuotedIdentifierCase    IdentifierCase
        StatementSeparatorPattern       String
        StringLiteralPattern    String
        SupportedJoinOperators  SupportedJoinOperators
DataTypes
        TypeName        String
        ProviderDbType  Int32
        ColumnSize      Int64
        CreateFormat    String
        CreateParameters        String
        DataType        String
        IsAutoIncrementable     Boolean
        IsBestMatch     Boolean
        IsCaseSensitive Boolean
        IsFixedLength   Boolean
        IsFixedPrecisionScale   Boolean
        IsLong  Boolean
        IsNullable      Boolean
        IsSearchable    Boolean
        IsSearchableWithLike    Boolean
        IsUnsigned      Boolean
        MaximumScale    Int16
        MinimumScale    Int16
        IsConcurrencyType       Boolean
        IsLiteralSupported      Boolean
        LiteralPrefix   String
        LiteralSuffix   String
Restrictions
        CollectionName  String
        RestrictionName String
        RestrictionDefault      String
        RestrictionNumber       Int32
ReservedWords
        ReservedWord    String
Schemas
        CatalogName     String
        SchemaName      String
        Description     String
Tables
        CatalogName     String
        SchemaName      String
        TableName       String
        TableType       String
Columns
Mar 13, 2015 at 8:35 PM
BTW, that statement locks up the application. No exception, no nothing.
Coordinator
Mar 13, 2015 at 9:07 PM
Ok, this is pure ADO.net (and what that method should have been doing). If this doesn't work, the IBM driver is borked :(
var factory = DbProviderFactories.GetFactory("IBM.Data.DB2.iSeries");
using (var connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;
    connection.Open();
    var col = connection.GetSchema("Columns");
    foreach (System.Data.DataColumn column in col.Columns)
    {
        Console.WriteLine("\t" + column.ColumnName + "\t" + column.DataType.Name);
    }
}
Mar 13, 2015 at 9:15 PM
I will try that in a little while and report back. I know this for sure: Lots of universal* database tools have that asterisk because of DB2.
Coordinator
Mar 13, 2015 at 9:22 PM
Ok, thanks.
DB2 Express-C wasn't too bad, but that's runs on Windows and they expect .net developers. Other platforms, less so...
Coordinator
Mar 13, 2015 at 9:36 PM
The underlying metadata is completely different. DB2 for Unix/Windows has SYSCAT.COLUMNS, DB2 iSeries has QSYS2.SYSCOLUMNS.
My library expects the ADO provider to normalize it via GetSchema (and then fixes up the numerous inconsistencies and missing information).
This may not be easy...
Mar 14, 2015 at 3:53 PM
Oh, it gets worse. DB2 started with SYSIBM as its schema definition, then it added SYSCAT as a read-only view on top of that, then it added INFORMATION_SCHEMA as an attempt to comply with industry standards. Thus, database utility developers like you don't always use the same thing. (I have no idea which one to recommend.) Google those table names and you'll find a blizzard of questions and various answers about how to read the schema definition for DB2 iSeries.

Don't do it sober. It's too depressing.
Mar 14, 2015 at 5:04 PM
JW,

Your last chunk of code is crashing with the exception shown below, probably because my customer's database has many thousands of tables. Now what?
See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
   at System.Data.Common.StringStorage.SetCapacity(Int32 capacity)
   at System.Data.RecordManager.set_RecordCapacity(Int32 value)
   at System.Data.RecordManager.GrowRecordCapacity()
   at System.Data.RecordManager.NewRecordBase()
   at System.Data.DataTable.NewRecordFromArray(Object[] value)
   at System.Data.DataRowCollection.Add(Object[] values)
   at IBM.Data.DB2.iSeries.iDB2Schemas.Columns(iDB2Connection cn, String[] restrictions)
   at IBM.Data.DB2.iSeries.iDB2Schemas.GetSchema(String collectionName, String[] restrictionValues, iDB2Connection cn)
   at IBM.Data.DB2.iSeries.iDB2Connection.GetSchema(String collectionName)
   at DCV.Screens.System_Tools.buttonCompareSchemas_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3655 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
DCV
    Assembly Version: 1.2.5.0
    Win32 Version: 1.2.5.0
    CodeBase: file:///C:/Documents%20and%20Settings/woosley/Local%20Settings/Apps/2.0/B2A917CT.10J/EPB3KGAM.DDJ/dcv...tion_0000000000000000_0001.0002_47fa61fc428445a8/DCV.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3645 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3644 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3644 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
WWL.Shared.DataAccess
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Documents%20and%20Settings/woosley/Local%20Settings/Apps/2.0/B2A917CT.10J/EPB3KGAM.DDJ/dcv...tion_0000000000000000_0001.0002_47fa61fc428445a8/WWL.Shared.DataAccess.DLL
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3654 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3654 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Core
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.30729.1 built by: SP
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Core/3.5.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
MySql.Data
    Assembly Version: 6.8.3.0
    Win32 Version: 6.8.3.0
    CodeBase: file:///C:/Documents%20and%20Settings/woosley/Local%20Settings/Apps/2.0/B2A917CT.10J/EPB3KGAM.DDJ/dcv...tion_0000000000000000_0001.0002_47fa61fc428445a8/MySql.Data.DLL
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
IBM.Data.DB2.iSeries
    Assembly Version: 12.0.0.0
    Win32 Version: 13.0.6.0
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/IBM.Data.DB2.iSeries/12.0.0.0__9cdb2ebfb1f93a26/IBM.Data.DB2.iSeries.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
Coordinator
Mar 14, 2015 at 6:03 PM
I'll work out some ADO code for you to find out the iSeries schema. Probably in a day or 2, if that's ok.

The database schema reader will have problems with big schemas, but there are work-arounds. The ReadAll and AllTables methods don't scale to thousands of tables- after loading all the tables, there are tens of thousands of columns, and then it loads foreign keys and other collections. There are millions of rows in data tables before they get sorted into the schema model.

The pattern for the reader for huge databases is to start with dataReader.TableList(). This should give you just a list of table names (with no columns or other data). Then you have to load specific tables, using dataReader.Table(tablename). You won't be able to load everything, just parts of your schema at a time.
Mar 14, 2015 at 7:31 PM
I don't even need to read the entire schema. Just selected tables.

You might, however, do something like ReadAll(int max = 0), just in case someone wants to avoid a crash.

Anyway, I really appreciate your help with this.

David
Coordinator
Mar 14, 2015 at 7:33 PM
Ok, can you try this code?

The first bit is straight ADO, as before.

The second and third parts use SchemaReader, which is the underlying part that uses DataTables with no interpretation. If you set a winforms datagrid's datasource to these datatables, you can see the raw schema data.
            var provider = "IBM.Data.DB2.iSeries";
            Console.WriteLine("1. Collection names");
            var factory = DbProviderFactories.GetFactory(provider);
            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                string metaDataCollections = DbMetaDataCollectionNames.MetaDataCollections;
                var dt = connection.GetSchema(metaDataCollections);
                foreach (DataRow row in dt.Rows)
                {
                    var collectionName = (string)row["CollectionName"];
                    Console.WriteLine(collectionName);
                }
            }


            Console.WriteLine("2. Column schema");
            var sr = new SchemaReader(connectionString, provider);
            var dataTable = sr.Columns("Customer");
            foreach (System.Data.DataColumn column in dataTable.Columns)
            {
                Console.WriteLine("\t" + column.ColumnName + "\t" + column.DataType.Name);
            }

            Console.WriteLine("3. Tables (this may fail if the collection names in (1) don't match the norm)");
            var dataSet = sr.Table("Customer");
            foreach (DataTable dTable in dataSet.Tables)
            {
                Console.WriteLine("Table " + dTable.TableName);
                foreach (DataColumn column in dTable.Columns)
                {
                    Console.WriteLine("\t" + column.ColumnName + "\t" + column.DataType.Name);
                }
            }
Mar 15, 2015 at 7:05 PM
This piece of code didn't populate the DataGridView:
dataGridView1.DataSource = dataSet;
Here is the console output:
1. Collection names
MetaDataCollections
DataSourceInformation
DataTypes
Restrictions
ReservedWords
Schemas
Tables
Columns
Databases
Procedures
ProcedureParameters
Indexes
IndexColumns
Views
ViewColumns
2. Column schema
        CatalogName     String
        SchemaName      String
        TableName       String
        ColumnName      String
        OrdinalPosition Int32
        ColumnDefault   String
        IsNullable      Boolean
        DataType        String
        CharacterMaximumLength  Int32
        CharacterOctetLength    Int32
        NumericPrecision        Int32
        NumericPrecisionRadix   Int32
        NumericScale    Int32
        CCSID   Int32
3. Tables (this may fail if the collection names in (1) don't match the norm)
Table Columns
        CatalogName     String
        SchemaName      String
        TableName       String
        ColumnName      String
        OrdinalPosition Int32
        ColumnDefault   String
        IsNullable      Boolean
        DataType        String
        CharacterMaximumLength  Int32
        CharacterOctetLength    Int32
        NumericPrecision        Int32
        NumericPrecisionRadix   Int32
        NumericScale    Int32
        CCSID   Int32
Table Indexes
        ConstraintCatalog       String
        ConstraintSchema        String
        ConstraintName  String
        CatalogName     String
        SchemaName      String
        TableName       String
        ConstraintType  String
Table IndexColumns
        ConstraintCatalog       String
        ConstraintSchema        String
        ConstraintName  String
        CatalogName     String
        SchemaName      String
        TableName       String
        ColumnName      String
        OrdinalPosition Int32
Table PrimaryKeys
Table ForeignKeys
Table ForeignKeyColumns
Mar 15, 2015 at 7:07 PM
Edited Mar 15, 2015 at 7:10 PM
I obviously substituted "Customer" with my table name. The table has an identity field ("ID"), but otherwise no indexes or foreign keys .
Coordinator
Mar 15, 2015 at 9:58 PM
Just checked in initial support. Doesn't seem to be any support for foreign keys or even primary keys. Perhaps IBM doesn't think they're important...

You can't bind a dataSet to a datagrid, you must bind one of it's tables.

More later.. I'll look into the primary keys and other interesting information.
Mar 15, 2015 at 11:33 PM
Don't worry about the keys and indexes on my behalf. My requirement is to simply compare the table and column definitions across several types of databases.
Mar 15, 2015 at 11:35 PM
Please don't waste your weekend on DB2 schema definitions. Life is too short.
Coordinator
Mar 16, 2015 at 8:01 PM
There is a planning release binary here: http://dbschemareader.codeplex.com/releases/view/612558

You will almost certainly have an error, and maybe for the next version too... But we should flush the bugs out quite quickly.
Mar 17, 2015 at 7:19 PM
Sweet. I'll keep you posted if I run into anything more than garden-variety exceptions.

d.
Mar 18, 2015 at 1:41 PM
I corrected my boneheaded code to:
dataGridView1.DataSource = dataSet.Tables[0]
It worked like a charm against the DB2 iSeries database, and that's no easy feat. Impressive work.

Thanks,

David Woosley
President
AppNation Inc.
Mar 19, 2015 at 2:36 PM
Martin,

Good morning. I'm getting the error shown below on the DB2 iSeries. Granted, I'm not using the planning release binary to which you linked above.
Critical: Column 'COLUMN_NAME' does not belong to table Columns.
Mar 19, 2015 at 2:47 PM
I'm using this code, which works perfectly against MySQL.
 var schemaReader = new SchemaReader(myConnectionString, myProvider);
 var dataset = schemaReader.Table(myTableName);
 var dataTable = dataSet.Tables[0];
 foreach (DataRow row in dataTable.Rows)
 {
     String columnName = row["COLUMN_NAME"].ToString();
     String dataType = row["DATA_TYPE"].ToString();
     ..... other processing
  }
Coordinator
Mar 19, 2015 at 3:00 PM
Yes, unfortunately the datatables are different. In MySql and SqlServer it's COLUMN_NAME, in DB2 it's ColumnName, in postgresql it's Name.
Other properties have even more variation.

The SchemaReader gives you these raw datatables, but the DatabaseReader sits on top of that, and should be translating everything to the same model.

Unless you're willing to do the translating of datatable columns (you could use the translating logic in ColumnsKeyMap), you'll have to use DatabaseReader (from the planning build).
Mar 19, 2015 at 3:57 PM
I don't mind hard-coding the two ways at this time. What's your projected release date on the new version? Next 60 days? We are definitely not in a hurry.
Coordinator
Mar 19, 2015 at 7:41 PM
The planned release will probably go stable around end March/start April.
Mar 24, 2015 at 6:21 PM
Excellent. Thanks.
May 23, 2015 at 1:02 AM
Latest release works great. Thank you very much.
Nov 12, 2015 at 1:51 PM
Hi Martin. Long time, no talk. These two lines of code:
DatabaseReader dr = new DatabaseReader(DbConfiguration.ConnectionString, getProvider());
IList<DatabaseTable> list = dr.TableList();
Produce the error shown below. This is running against DB2 with thousands of tables.
************** Exception Text **************
System.ArgumentException: Column 'NAME' does not belong to table Tables.
   at System.Data.DataRow.GetDataColumn(String columnName)
   at System.Data.DataRow.get_Item(String columnName)
   at DatabaseSchemaReader.Conversion.SchemaConverter.Tables(DataTable dt)
   at DatabaseSchemaReader.DatabaseReader.TableList()
   at DCV.DB.Metadata.MetadataTesting.DoTest()
   at DCV.Admin.Programmer_Tools.ViewDatabaseSchemaReport()
   at DCV.Admin.Programmer_Tools.listBoxTasks_DoubleClick(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnDoubleClick(EventArgs e)
   at System.Windows.Forms.ListBox.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Coordinator
Nov 12, 2015 at 6:58 PM
Hi again! This is a problem in the TableKeyMap, perhaps a different version of the driver?

Try this bit of ADO:
var factory = DbProviderFactories.GetFactory("xxx"); //use your ado provider name
using (var connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;
    connection.Open();
    var col = connection.GetSchema("Tables");
    foreach (System.Data.DataColumn column in col.Columns)
    {
        Console.WriteLine("\t" + column.ColumnName + "\t" + column.DataType.Name);
    }
}
It's looking for column named "TABLE_NAME" or "NAME" but I guess it's "TABNAME". Possibly we'll find other different column names after this one :)
Nov 12, 2015 at 11:25 PM
Here's the output from DB2:
CatalogName ... String
SchemaName .... String
TableName ..... String
TableType ..... String
Thanks. D.
Nov 12, 2015 at 11:53 PM
It's the same issue we had with columns.

DB2 uses ...
ThisNamingConvention
Whereas you are expecting ...
THIS_NAMING_CONVENTION
Nov 13, 2015 at 12:41 AM
BTW, the customer's DB2 database, which I did not design, has 68,000 tables. Believe it or not, connection.getSchema("Tables") returns the DataTable of 68,000 tables in less than two seconds, which is impressive. DB2 is indeed fast.
Coordinator
Nov 13, 2015 at 5:23 PM
There's a pre-release build with the fix at https://github.com/martinjw/dbschemareader/releases/tag/1.4.3.9 - just download the zip and extract.

Let me know if it's ok! Thanks.
Nov 16, 2015 at 4:35 PM
Edited Nov 16, 2015 at 4:39 PM
I'm testing the pre-release build this morning.

By the way, have you tried using ....
String[] restrictions = new string[4] { null, null, "Customer", null } ;
DataTable myTables  = connection.GetSchema("Tables", restrictions);
Since table names are almost assuredly indexed within the schema, telling connection.GetSchema() the name of the desired table works much quicker in MySQL and, I suspect, DB2. Caveat: I'm not 100% certain that the number of restrictions or the order in which they should appear is consistent across all databases. Nonetheless, it's worth considering since connection.GetSchema() returns only one table.
Nov 16, 2015 at 5:08 PM
Edited Nov 16, 2015 at 5:09 PM
I'm researching the restrictions, and I may have the solution in a little while.

No need for both of us to research it, so give me a few minutes to determine the restrictions by collection name. A cool solution would be to create a helper class that returns the correct restrictions for a specific collection and database type. For example:
Restrictions r = RestrictionHelper.getRestrictions(String collectionName, [stuff I know]);
Where stuff I know contains key-value pair of attributes. The key-value pairs could be a delimited string, simple list, dictionary or whatever.
Restrictions r =  RestrictionHelper.getRestrictions("Tables", "TableName=Customer,Owner=Martin");
DataTable myTables = connection.GetSchema("Tables", r.ToString());
Nov 16, 2015 at 5:48 PM
Edited Nov 16, 2015 at 5:49 PM
The parameter array, which is not simply strings, for restrictions for MySQL and DB2 is the same:
CollectionName ........ String
RestrictionName ....... String
RestrictionDefault .... String
RestrictionNumber ..... Int32
This is good news. Now, I'll determine the acceptable values (including nulls) for each element in the array for the Tables collection. In theory, we could determine the acceptable values for all of the collections, although that might be overkill.

d.
Nov 16, 2015 at 6:01 PM
Coordinator
Nov 16, 2015 at 6:48 PM
See https://dbschemareader.codeplex.com/SourceControl/latest#DatabaseSchemaReader/SchemaRestrictions.cs

It's used in the SchemaReader, depending how you call it.
If you use ReadAll() it won't apply much apart from the Owner/Schema (if specified), because it needs everything.

If you're specifying table names, it should ensure you only load what's required- for memory as well as speed.
Nov 16, 2015 at 10:47 PM
Has that always existed, and I've missed it?
Nov 16, 2015 at 10:49 PM
What's an example of how to get a single table in DB2? It balks with four parameters, despite what the IBM docs say.
Nov 16, 2015 at 10:58 PM
Can you post/email the source code for SchemaRestrictions.cs. It can't be selected on that page. Thanks.
Coordinator
Nov 17, 2015 at 6:47 AM
Github is easier to grab the source: https://raw.githubusercontent.com/martinjw/dbschemareader/master/DatabaseSchemaReader/SchemaRestrictions.cs

ADO has a standard way to get the restrictions:
dbConnection.GetSchema(DbMetaDataCollectionNames.Restrictions)
This is a dataTable with all the restrictions for every metadata collection. We can dynamically discover what restrictions to use.

The SchemaReader has a SchemaRestrictions getter, which lazily creates the Restrictions class.
https://raw.githubusercontent.com/martinjw/dbschemareader/master/DatabaseSchemaReader/SchemaReader.cs
Then it's used where it's needed, for instance in columns:
        protected virtual DataTable Columns(string tableName, DbConnection connection)
        {
            string[] restrictions = SchemaRestrictions.ForTable(connection, ColumnsCollectionName, tableName);
            return connection.GetSchema(ColumnsCollectionName, restrictions);
        }
If you do ReadAll, this gets called with a null tableName, and the restrictions are empty *
If you call Table(tableName), then the tableName is used as a restriction for all the metadata lookups- columns, foreign keys, indexes etc.
  • Owner (sometimes called Schema) is always available and applied if it is set. If you have a simple single-schema database it doesn't make much difference, but Oracle databases expose huge internal schemas so setting Owner explicitly is almost essential.
Nov 17, 2015 at 3:51 PM
I'm learning how to use SchemaRestrictions.cs right now. I saw your reference to Oracle, but not DB2. I figure your code has that covered somehow..

By the way, I wonder why IDbConnection doesn't have an implementation of GetSchema(). Recasting to DbConnection fixes that.

I'll keep you posted. Many, many thanks.

d.
Nov 17, 2015 at 7:43 PM
Martin,

Let me clarify my basic requirement. I simply need to determine if a specific table exists. That's all.

What is the fastest way to do that.

d.
Nov 17, 2015 at 8:30 PM
Woohoo!

It works like a charm for both MySQL and DB2. And, it's F-A-S-T. Checking for the existence of a dozen tables takes less than 1/2 a second.

You are the man.. Thank you.
Nov 18, 2015 at 12:37 AM
Question:

I need a straightforward method with this signature:
public bool tableExists(String tableName)
{
    // using SchemaRestrictions to find the table?
}
Any advice?

Thanks.
Coordinator
Nov 18, 2015 at 8:10 AM
Yes, I think that should be straightforward. Something like this... Almost the same as Tables, with the additional restriction for table name.
        public bool TableExists(string tableName)
        {
            if (string.IsNullOrEmpty(tableName)) throw new ArgumentException("TableName is null or empty", "tableName");
            string collectionName = TablesCollectionName;
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();
                string[] restrictions = SchemaRestrictions.ForTable(conn, TablesCollectionName, tableName);
                var dt = conn.GetSchema(collectionName, restrictions);
                //could have same name in different schemas
                return dt.Rows.Count > 0;
            }
        }
I'll have a new release in a little while...
Nov 18, 2015 at 1:20 PM
This works well:
public Boolean tableExists(String tableName)
{
    //
    // application-specific values
    //
    String provider           = WWL.Shared.DataAccess.DbConnection.getProvider();
    String connectionString   = WWL.Shared.DataAccess.DbConfiguration.ConnectionString;
    //
    // generic from here on down
    //
    DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;
        connection.Open();
        string[] restrictions = this.ForTable(connection , "Tables", tableName);
        var dt = connection.GetSchema("Tables", restrictions);
        return dt.Rows.Count > 0;
    }
}
Coordinator
Nov 18, 2015 at 1:50 PM
Nov 18, 2015 at 9:17 PM
Martin,

That is super.

For applications like mine that create missing customer-specific tables on the fly and therefore must check for the tables' existence in many places in the application, this functionality is crucial. Wading through 68,000 table names in the schema definitions was simply unworkable.

Also, the fact that you jumped on this so quickly and created the required methods is very impressive and appreciated.

My client thanks you too.

David
Dec 1, 2015 at 2:49 PM
Was the latest DatabaseSchemaReader.DLL compiled with .NET 4.0 by any chance?
Coordinator
Dec 1, 2015 at 3:33 PM
It's built with 4.0
In the top folder of the download, the target for the dll is 2.0
In the net40 folder, the target is 4.0
Dec 1, 2015 at 4:20 PM
I'm using your 2.0 version from the root folder of your installation, so it's probably not you. I'll let you know either way. Give me a few. Thanks.
Dec 1, 2015 at 8:41 PM
Please confirm:

The DatabaseSchemaReader.dll of exactly 413,184 bytes will run with only .NET 3.5 installed?

(The DLL in the net4 folder is exactly 413,696 bytes.)

Thanks.
Coordinator
Dec 1, 2015 at 9:12 PM
Yes, it should run with only .net 3.5, without .net 4 installed.
Dec 1, 2015 at 10:55 PM
That's what I thought. OK, thanks.
Dec 3, 2015 at 3:30 PM
Martin,

Our tests strongly suggest that DatabaseSchemaReader.dll depends on something higher than .NET 3.5. A published project that doesn't use your DLL runs fine on 3.5, whereas the same project that references your DLL does not. We've tested this from several different angles.

Advice? Thanks.

David
Coordinator
Dec 3, 2015 at 7:28 PM
Odd. I've certainly had to deploy to XP and Win2003 without .net 4 before now, but now we are using msbuild from 4.5. According to the documentation, msbuild and Visual Studio should still build all targets back to 2.0. I'm running this in iis .net 2.0 application pools, but every machine does have .net 4 these days.

Checking with corflags it clearly shows v2:

corflags DatabaseSchemaReader.dll
Microsoft (R) .NET Framework CorFlags Conversion Tool. Version 4.6.1055.0
Copyright (c) Microsoft Corporation. All rights reserved.

Version : v2.0.50727
CLR Header: 2.5
PE : PE32
CorFlags : 0x1
ILONLY : 1
32BITREQ : 0
32BITPREF : 0
Signed : 0

Anything helpful in the error messages?
Dec 3, 2015 at 8:10 PM
When your DLL is referenced in the project, we get this message upon startup:
Unable to find a version of the runtime to run this application.
Also, I just confirmed that the application is running with CLR = 2.0.50727.3655.

This is weird.
Coordinator
Dec 3, 2015 at 8:53 PM
There are well known problems with 4.5 and 4.0, which had some breaking changes despite both 4.0 and 4.5 identifying as "v4.0.30319".

This didn't seem to happen with v2.0/3.0/3.5, which are all "v2.0.50727". Normally, if you have 3.5 on your machine, any dll marked v2.0.50727 will actually be loaded by .net with 3.5.

If you only have 2.0, with no 3.5 install... maybe there will be a problem.
The databaseschemareader.dll is marked as v2, but it really targets 3.5.
Dec 3, 2015 at 9:20 PM
We are targeting 3.5, and everything works great until we integrate the DatabaseSchemaReader.DLL. We must be doing something wrong on our end, so I'll keep researching. We truly want to use the GetTable() and other enhancements you've made. I appreciate your help.

Here's an idea: Is it possible for you to send me your source code and let me attempt to build/publish it along with my project? I'll delete your source code immediately after the test. If you'd rather not, I completely understand.
Coordinator
Dec 4, 2015 at 4:44 AM
The source code and build script is available here.
Click the "Source Code" menu item on the top to go to https://dbschemareader.codeplex.com/SourceControl/latest
Then on the top right is a link "Download". It will give you a zip of the complete source.
Dec 4, 2015 at 3:05 PM
Excellent. I'm integrating it now. I feel like such an amateur when I see the (complex?) way in which you have solution/projects configured.

For the sake of debugging the dependencies, I'd like to integrate the class structure and classes from your projects into my project as if I'd written them myself. Is that possible? It doesn't need to be easy, just possible. I presume I'd simply copy your folders of classes to be alongside mine.
Dec 4, 2015 at 4:10 PM
The answer appears to be no, that's not really possible. Shucks.
Dec 4, 2015 at 4:16 PM
Our application is deployed to several servers, each with its own .NET 3.5 installation. It appears we must upgrade .NET on all of the servers, then try again.
Coordinator
Dec 4, 2015 at 7:21 PM
In Visual Studio click the solution, then Add -> Existing project, and pick the .csproj (2008 version for VS2008).
Change the reference in your project from the dll to the project.
Then you can build your solution with the schema reader
Dec 7, 2015 at 3:54 PM
Will do.

In the interim, I extracted and integrated the code needed to use GetTable() and it works like a charm.

Thanks again for your help.
Dec 27, 2015 at 9:07 PM
Martin,

Do you have a class that retrieves the permissions granted on a database table?

Thanks,

David

P.S. The GetTable() code is working great.
Coordinator
Jan 3, 2016 at 11:34 AM
No, there's nothing for permissions. It's not difficult, but every database is going to be different...
Jan 4, 2016 at 6:43 PM
I've got the permissions for MySQL, but, no surprise, DB2 is another story. If you have hints, I'll take them. Otherwise, I'll let you know what, if any, solution I find. Thanks.
Jan 29, 2016 at 9:43 PM
Martin,

Do you have something like:
Boolean b = IndexExists(String tableName, String indexName);
Which works similar to TableExists(String tableName)? (Which works great.) I'd try substituting 'Index' for 'Table' in your TableExists() code, but I don't think that will work because more than one table can have the same index name.

Idea?

David
Jan 29, 2016 at 10:08 PM
Never mind. I think I can do it.

d.