0

c# code that doesn't work:

string connectionString = "Provider=OvHOleDbProv.OvHOleDbProv.1;Persist Security Info=True;User ID=user;Password=password;Data Source=192.168.7.96;Location=\"\";Mode=ReadWrite;Extended Properties=\"\";";
OleDbConnection _connection = new OleDbConnection(connectionString);
_connection.Open();
DataTable schema = _connection.GetSchema("Tables"); // Exception see below
_connection.Close();

Exception:

System.Data.OleDb.OleDbException (0x80004002): Сбой "OvHOleDbProv.OvHOleDbProv.1" без сообщения об ошибке, код результата: E_NOINTERFACE(0x80004002).

в System.Data.OleDb.OleDbConnectionInternal.ProcessResults(OleDbHResult hr)

в System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)

в System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)

в System.Data.OleDb.OleDbMetaDataFactory.PrepareCollection(String collectionName, String[] restrictions, DbConnection connection)

в System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)

в System.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions)

в System.Data.OleDb.OleDbConnection.GetSchema(String collectionName, String[] restrictionValues)

в System.Data.OleDb.OleDbConnection.GetSchema(String collectionName)

в SourceModule.Ovation.ApiOvation.ExportSignals(String filename) в E:\tfs\vm-tfs\SAn\Source code\SourceModules\Ovation\Development\Version 2\SourceModule.Ovation\ApiOvation.cs:строка 58

в SourceModule.Ovation.Program.Main(String[] args) в E:\tfs\vm-tfs\SAn\Source code\SourceModules\Ovation\Development\Version 2\SourceModule.Ovation\Program.cs:строка 20

c# code that works:

string connectionString = "Provider=OvHOleDbProv.OvHOleDbProv.1;Persist Security Info=True;User ID=user;Password=password;Data Source=192.168.7.96;Location=\"\";Mode=ReadWrite;Extended Properties=\"\";";
OleDbConnection _connection = new OleDbConnection(connectionString);
_connection.Open();
DataTable schema = _connection.GetSchema("Restrictions");
_connection.Close();

PS code that works for both cases:

$connectionString='Provider=OvHOleDbProv.OvHOleDbProv.1;Persist Security Info=True;User ID=user;Password=password;Data Source=192.168.7.96;Location="";Mode=ReadWrite;Extended Properties="";' 
$oCon = New-Object System.Data.OleDb.OleDbConnection $connectionString 
$oCon.Open() 
$schema = New-Object System.Data.DataTable
$schema = $oCon.GetSchema("Tables") 
$schema
$schema = $oCon.GetSchema("Restrictions")
$schema
$oCon.Close()

Restrictions output include collectionName "Tables":

CollectionName RestrictionName RestrictionDefault RestrictionNumber
-------------- --------------- ------------------ -----------------
Columns TABLE_CATALOG 1
Columns TABLE_SCHEMA 2
Columns TABLE_NAME 3
Columns COLUMN_NAME 4
Tables TABLE_CATALOG 1
Tables TABLE_SCHEMA 2
Tables TABLE_NAME 3
Tables TABLE_TYPE 4

Ageev Dmitry
  • 1
  • 5
  • what's in Tables? what's in Restrictions? – Francesco B. Mar 18 '18 at 13:43
  • 1
    @FrancescoB. , [msdn_restrictions](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/schema-restrictions). In **Tables** list of available tables in Database. – Ageev Dmitry Mar 18 '18 at 13:52
  • Yep, I saw the same page. What I don't understand is: why not use the same syntax? I.e. `string[] restrictions = new string[4]; restrictions[1] = "Sales"; System.Data.DataTable table = connection.GetSchema( "Tables", restrictions);` – Francesco B. Mar 18 '18 at 13:55
  • 1
    @FrancescoB., I can add link to files with result of GetSchema("Tables") and GetSchema("Restrictions") in PS. – Ageev Dmitry Mar 18 '18 at 13:58
  • 1
    @FrancescoB., I tried to use this syntax, but it also leads to an error. I can repeat it, but you need to wait a bit. The version of the oledb driver is old, and perhaps not complete. Because of this, there are many problems. – Ageev Dmitry Mar 18 '18 at 14:03
  • @FrancescoB., Oh, wait. Because I do not need restrictions. But in the PS works well. In c# received the same error. – Ageev Dmitry Mar 18 '18 at 14:11
  • @FrancescoB., I think that the error is due to the version of .NET framework. But how to determine which of the installed versions of the framework uses PS now? And I do not have any documentation for this oledb driver. – Ageev Dmitry Mar 18 '18 at 14:16
  • @FrancescoB., I can connect to the database from the Excel using this driver. And he also successfully gets a list of tables in the database. – Ageev Dmitry Mar 18 '18 at 14:19
  • apparently you can see what version of .NET PS is using like [this](https://stackoverflow.com/a/3347907/5480409) – Francesco B. Mar 18 '18 at 14:35
  • 1
    @FrancescoB., thank you. CLRVersion 4.0.30319. I'll try to build my application for this version. – Ageev Dmitry Mar 18 '18 at 14:41
  • @FrancescoB., No, it did not help. – Ageev Dmitry Mar 18 '18 at 14:56
  • what's the framework version your project is compiled for? by the way, is .NET 2.0 installed on your system? – Francesco B. Mar 19 '18 at 07:54
  • @FrancescoB., using [clrver](https://learn.microsoft.com/ru-ru/dotnet/framework/tools/clrver-exe-clr-version-tool): v.1.1.4322, v.2.0.50727, v.4.0.30319. App.config: . In project properties: .NET Framework 4. – Ageev Dmitry Mar 19 '18 at 08:25
  • are you compiling to 32 or 64 bit? can you try both ways? – Francesco B. Mar 19 '18 at 08:33
  • @FrancescoB., Ovh Provider supports only 32 bit. – Ageev Dmitry Mar 19 '18 at 12:07
  • How is OvHOleDbProv defined? – Francesco B. Mar 19 '18 at 13:42
  • @FrancescoB., Sorry, I did not understand the question. OvHOleDbProv.OvHOleDbProv.1 is name of oledb provider. I installed it. It works to some extent. With its help, I get the necessary data from the database. But for a new task, I'd like to get a scheme (because getting works in PS :) ). – Ageev Dmitry Mar 19 '18 at 14:32
  • Just not to give up on anything, what happens if you surround Restrictions in brackets, i.e. `[Restrictions]`? – Francesco B. Mar 19 '18 at 15:16
  • @FrancescoB., `System.Data.DataTable schema = _connection.GetSchema("[Restrictions]");` result exception: ex.Message = "the requested collection ([Restrictions]) is not defined.", ex.InnerException = null, ex.StackTrace = "in System.Data.ProviderBase.DbMetaDataFactory.FindMetaDataCollectionRow(String collectionName)\r\n in ....". – Ageev Dmitry Mar 19 '18 at 20:36
  • I found a [link](https://forums.asp.net/t/1378795.aspx?How+can+I+get+table+relationship+from+connection+GetSchema+) suggesting to try `GetOleDbSchemaTable` instead of `GetSchema`, with or without brackets – Francesco B. Mar 19 '18 at 21:19
  • @FrancescoB., I know about this method. The same result. In PS work well, c# - error. I can give access to tfs for convenience. So you can write tests, task. I'll check them. For this I need your email. Of course, if it's convenient for you – Ageev Dmitry Mar 20 '18 at 06:58
  • 1
    @FrancescoB., thank you very much for trying to find a solution. I found another way to solve. – Ageev Dmitry Mar 26 '18 at 08:20
  • Glad to hear that. Best regards! – Francesco B. Mar 26 '18 at 08:54

1 Answers1

0

The following code returns a list of tables in the database and save to xml:

string connectionString = "Provider=OvHOleDbProv.OvHOleDbProv.1;Persist Security Info=True;User ID=user;Password=password;Data Source=192.168.7.96;Location=\"\";Mode=ReadWrite;Extended Properties=\"\";";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand() { Connection = connection, CommandText = "select TABLE_NAME from SYSTABLES where TABLE_TYPE='TABLE'" };
OleDbDataReader datareader = command.ExecuteReader();
DataTable data = new DataTable();
data.Load(datareader);
data.TableName = "SYSTABLES"; // necessary for writing to xml
data.WriteXml("tables.xml");
connection.Close();
Ageev Dmitry
  • 1
  • 5