次の方法で共有

SQL Select data from a text file

  • [アーティクル]

It’s often very useful to be able to execute a SQL SELECT statement on a text file.

I was debugging a multiprocess multithread Foxpro COM DLL problem in a COM+ application.

To help diagnose the problem, I wanted to figure out the sequence of events that occurred. Since there were several processes, each with perhaps dozens of threads instantiating a VFP COM object, figuring out the exact sequence of events was essential.

I wrote a function that would append a string to a log file (it also can output to the Windows Event log via RegisterEventSource or any attached debugger via OutputDebugString). It prepended the line with a date/timestamp, process ID, thread ID. I sprinkled the code strategically with calls to that function, with various string hints describing the location.

The log file contained a sequence of events as they occurred, but it was fairly complex to analyze the results.

Here’s a sample log file.

  6/10/05 10:48:15:541 PID=18916 tls= c0990 thr=22004 dllgetclassobject

  6/10/05 10:48:15:541 PID=18916 tls= c0990 thr=22004 CFAddRef 1

  6/10/05 10:48:15:588 PID=18916 tls= c0990 thr=11800 DllOleInit 0

  6/10/05 10:48:15:604 PID=18916 tls= d6c30 thr=11800 CFGTLSInitGlobals

  6/10/05 10:48:15:620 PID=18916 tls= d6c30 thr=11800 dlloleinit mode=0 plcs=cff3260 gcst=ca030

  6/10/05 10:48:15:620 PID=18916 tls= d6c30 thr=11800 dllgetclassobject

  6/10/05 10:48:15:620 PID=18916 tls= d6c30 thr=11800 CFAddRef 1

  6/10/05 10:48:15:620 PID=18916 tls= d6c30 thr=11800 CreateInst

  6/10/05 10:48:15:620 PID=18916 tls= 0 thr=13436 DllOleInit 0

  6/10/05 10:48:15:620 PID=18916 tls= d6c30 thr=11800 CstSrvInst Ctor 1b107d8 1b10720

Here’s some sample code to put the log file into a cursor, extract the Process and Thread IDs into their own columns, and do a few queries on the log. It shows the log sorted by process id and thread id. The last SQL select shows the # of threads per process. Using a Seq # is much more compact and easier to read than using a date/time stamp. Making a simple report that groups by process and thread easily shows that the sequence of events on each thread is valid.

CREATE CURSOR foo (line c(100),pid c(10),tid c(10),event c(30),seq i)

APPEND FROM c:\vfplog.txt sdf

UPDATE foo SET pid = SUBSTR(line,AT("PID=",line)+4,5),;

            tid =SUBSTR(line,AT("thr=",line)+4,5),;

            event=SUBSTR(line,55),;

            seq=RECNO()

INDEX on pid+tid TO t

REPORT FORM log NOEJECT NOCONSOLE PREVIEW nowait

SELECT COUNT(distinct pid) as cnt FROM foo INTO CURSOR temp

?"# of processes = ",cnt

SELECT COUNT(distinct tid) as cnt FROM foo INTO CURSOR temp

?"# of threads = ",cnt

SELECT pid,COUNT(distinct tid) as cnt FROM foo GROUP BY 1 INTO CURSOR temp

LIST off

66844

Comments