Freeware - Logparser.OLEDB

Log Parser has a native extension which allows developers to write their own custom input formats and for users to query using the i:COM input format. I decided to write the OLEDB Log Parser COM input format plugin so I could extend the data sources which LogParser could query. By using OLEDB and LogParser you have an extremely powerful tool which can provide complex querying solutions.

Features

• Using this DLL with LogParser you can query any OLEDB datasource which Log Parser doesn't support natively. e.g Excel, Access, DBF, MSSQL, Oracle, MS Project, FoxPro, AS/400, Index Server, HTML Tables, Text Files, and any other custom OLEDB providers. There are a number of sites with OLEDB Connection Strings
• Provide a query for the base OLEDB datasource and a query for Log Parser. e.g. get the data you want back from the data source and let Log Parser provide its SQL querying features (e.g. min, max, avg, etc and numerous functions) to format the data correctly.
• Supports multiple datasources, so you can query in the same LogParser query across multiple Excel spreadsheets, Access databases, SQL databases etc. Extremely useful to query multiple data sources to discover the location of data.
• Implements iCheckPoint to support only showing the latest records. This is supported across multiple data sources in a single query.
• Implements 2 custom columns (as per standard LogParser). They are LogParserConnectionString, LogParserRecordNumber in addition to returned columns.
• Logs any errors to console and to EventLog.Application
• VB.NET 2005 source code included
• Note: Doesn't currently support conversion of OLEDB datatypes to LogParser datatypes - everything is provided to LogParser engine as as string.

Below are some of the challenges :-

1. Add new columns to a DataSet, placing in the right order and setting their value. The trick is to add the columns and then to reverse transverse through the records updating them.

'oDataSet.Tables(0).Columns.Add("LogParserRecordNumber", 'System.Type.GetType("System.String"))
'oDataSet.Tables(0).Columns("LogParserRecordNumber").SetOrdinal(1)
'Dim lIndex As Long = lRowCount - 1
'Do Until lIndex = -1
' Dim oRow As DataRow = oDataSet.Tables(0).Rows(lIndex)
' If (lIndex + 1) <= lCheckPointOffset Then ' 'Can delete this row from in-memory datatable
' oRow.Delete()
' oRow.AcceptChanges()
' Else
' ' Keep this row and add values to it.
' lRecordNumber = lRecordNumber + 1
' oRow.BeginEdit()
' oRow.Item("LogParserRecordNumber") = lRecordNumber
' oRow.EndEdit()
' End If
' lIndex = lIndex - 1
'Loop

2. Allowing Log Parser to escape quote (") in the command line request inside of the LogParser.exe query. The below query has quotes within quotes and to allow LogParser to accept this you must escape with slash (\). e.g. \"

LogParser.exe -i:COM "select * From 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\SOLVSAMP.XLS;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"'" -iProgId:Gluegood.LogParser.OLEDB -iCOMParams:iQuery="SELECT * FROM [Quick Tour$]" -o:DataGrid

*************************************************

Download. VB.NET source is included in download.

*************************************************
To install:
1. Copy the LogParser.OLEDB folder to a location on your harddrive
2. Run the command .\LogParser.OLEDB\InstalldotNETasCOM.bat (need GACUTIL.EXE - part of the .NET FW 2.0 SDK - why?)
3. Run your Log Parser query. e.g. LogParser.exe -i:COM "select * From 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;'" -iProgId:Gluegood.LogParser.OLEDB -iCOMParams:iQuery="SELECT * FROM [Employees]",iCheckPoint="C:\CheckPointTest.chk" -o:DataGrid

Syntax

LogParser.exe -i:COM "select * From '<oledbConnectionString>'" -iProgId:Gluegood.LogParser.OLEDB -iCOMParams:iQuery="<QueryString>", ,iCheckPoint="<CheckPointFile>"

*************************************************

** Legal **
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.