viewpoint-particle

Author Topic: CLR - Oledb (continued)  (Read 2310 times)

stanl

  • Pundit
  • *****
  • Posts: 782
CLR - Oledb (continued)
« on: July 11, 2013, 02:08:55 pm »
The code relates to the .accdb I uploaded in the previous post on this topic. Removed the lines associated with connection builder as it was not needed. Tried to write out the schema as xml. The script works (I attached both the tables and columns xml), but the output was only the schema of the schema, not the actual tables or columns in oProvider.  Instead of WriteXmlSchema(cXML), perhaps I should have used just WriteXml() but that method is over-loaded [i.e. over my head] and didn't appear to have a filepath parameter. Trying to replicate the getschema() as done with COM that returns an xml recordset. As of my current reading, doing things that are simple in COM, like moving a recordset into Excel require an MVP in .NET - even Powershell has custom cmdlets that are hairy.
Code: Winbatch
;Winbatch 2013 - CLR - OLEDB {baby steps}
;
;
;Stan Littlefield July 7, 2013
;
;Updated July 10,2013 : added code to send getschema() results to xml
;////////////////////////////////////////////////////////////////////////////////////////////////////////
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
;cXML = Dirscript():"tables.xml" ;use for tables schema
cXML = Dirscript():"columns.xml"
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB  ; cannot use ';Connect Timeout=30;'
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()
oTables = ObjectClrNew("System.Data.DataTable")
oTables = oProvider.GetSchema("Columns")
oTables.WriteXmlSchema(cXML)
oTables = 0
oProvider.Dispose()
oProvider=0
Exit
 
 

stanl

  • Pundit
  • *****
  • Posts: 782
Re: CLR - Oledb (continued)
« Reply #1 on: July 12, 2013, 02:02:12 am »
My bad - if you do modify
;oTables.WriteXmlSchema(cXML) as
oTables.WriteXml(cXML)
then you do get the xml in a simple format (not rs:xml). And, as opposed to COM, it will overwrite the file if it already exists rather than error.