Most recent post

Wednesday, April 23, 2008

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.

9 Comments:

Anonymous AARON said...

funky logparser plugin. can think of a number of uses already

Ta A

April 23, 2008 at 10:02 PM  
Anonymous Gluegood said...

New version uploaded to support commas in iQuery. e.g.

REM *** Access Single Connection with commas in iQuery ***
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 [EmployeeId]\u002c[LastName] FROM [Employees]" -o:DataGrid

Enjoy!

September 6, 2008 at 5:31 PM  
Blogger PD said...

Great plugin. But i am unable to make it work for SQL queries having a group by clause. Would you be kind enough to give a sample of executing a SQL statment with a group by clause, which could generate multiple files ?

September 17, 2008 at 11:19 PM  
Blogger Gluegood Software said...

New version has been released. It now maps datatypes between OLEDB and LogParser. In addition it is now coded as an interface so you can call it via the LogParser COM interface.

Thanks
Gluegood

April 1, 2009 at 10:57 PM  
Anonymous Anonymous said...

Great job. Would you be kind to show how to call logparser.dll via com interface(vb or c#) when using a com plugin ? Thanks

June 9, 2010 at 4:16 AM  
Anonymous Anonymous said...

I mean that when I want to add a com plugin for my proprer input format, how to call logparser via its com interface ?

June 9, 2010 at 4:20 AM  
Blogger Gluegood Software said...

Below is an example in VBA.

Enjoy!
Gluegood

Dim oLogQuery
Dim oWSSInputFormat
Dim sQuery As String
Dim oRecordSet
Dim oRecord
Dim sTask as string

Set oLogQuery = CreateObject("MSUtil.LogQuery")

' Create Input Format object
Set oWSSInputFormat = CreateObject("Gluegood.LogParser.WSS30")

' Create query text
sQuery = "SELECT * FROM 'http://mysharepoint.com/TeamSite/Documents/Folder/Forms/Document Mgmt.aspx'"

' Execute query and receive a LogRecordSet
oWSSInputFormat.iVersion = True
oWSSInputFormat.iCheckPoint = "C:\Checkpoint.chk"
Set oRecordSet = oLogQuery.Execute(sQuery, oWSSInputFormat)

' Visit all records
Do While Not oRecordSet.atEnd
' Get a record
Set oRecord = oRecordSet.getRecord
' Get values
If Not IsNull(oRecord.getValue(TaskNo")) Then
sTaskId = oRecord.getValue("TaskNo")
Else
sTaskId = ""
End If
Next

' Close RecordSet
oRecordSet.Close
oRecordSet = Nothing
oLogQuery = Nothing

June 10, 2010 at 8:22 PM  
Blogger BadBacchus said...

Hi. First up. Wow! Great extension of logparser, thanks!
Is there any chance the OLEDB plugin can be used with the logRecordSet object and if so how can I set the "iQuery" parameter?
Thanks in advance
BB

November 12, 2010 at 11:32 AM  
Blogger Gluegood said...

As per above example, below is showing how the iQuery parameter would work.

' Create Input Format object
Set oOLEDBInputFormat = CreateObject("Gluegood.LogParser.OLEDB")

' Create query text
sQuery = "select * From 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;'"

' Execute query and receive a LogRecordSet
oOLEDBInputFormat.iQuery = "SELECT [EmployeeId]\u002c[LastName] FROM [Employees]"
Set oRecordSet = oLogQuery.Execute(sQuery, oOLEDBInputFormat)

November 12, 2010 at 7:15 PM  

Post a Comment

<< Home