Pervasive SQL (PSQL) capture SQL statements

Is there a way to capture SQL statements sent to a PSQL 11 database? Can this be done in PCC?
PerfishentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mirtheilSoftware DeveloperCommented:
Check on the Query Plan option.  
It's fully documented at http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=ODBC/qpv.14.4.html


The easiest way is to use the SET QRYPLAN statement:

¿
To create a query plan
1
Execute SET QRYPLAN = on to turn on the creation of a query plan.
2
Execute the SET QRYPLANOUTPUT statement and specify the location and name of the query plan file.
See Query Plan Settings.
3
Execute an SQL SELECT, INSERT, UPDATE, or DELETE statement (which creates its corresponding query plan).
4
Execute SET QRYPLAN = off to turn off the creation of query plans.
0
PerfishentAuthor Commented:
Will this capture SQL statements coming from an application and not just from PCC itself?
0
mirtheilSoftware DeveloperCommented:
If the application issues a SET QRYPLAN, it'll capture them.  
There was a way to set a registry setting but I haven't tried it recently.  Here's the details:
The settings that affect whether the relational engine gathers query plan
information are QryPlan and QryPlanOutput.  These settings are associated with
the DSN, and the DSN information is saved in the registry, or in a file,
depending on the type of DSN.  I will only describe how to set these settings
for system and user DSNs, which are stored in the registry.  The process for
setting these for a file DSN is similar.

For a system DSN, look in the following section of the registry:
\\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

For a user DSN, look in the following section of the registry:
\\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI

In the appropriate section, find and select the name of  the DSN for which you
want to gather query plan information.  On the right side of the editor, you
will see all of the settings for the DSN, similar to the information in Figure
1.

Figure 1

Add two string values for the DSN as shown in Table 1.
Registry Setting Value
QryPlan 1
QryPlanOutput <directory and file name to place query plan information>
Table 1

Notice that the QryPlanOutput value is not the directory where you want the
information placed, but the directory and the file name.  You can use any
extension you want, but I suggest using an extension of "QPF", as this is the
default extension that the query plan viewer uses.

NOTE: You can disable query plan generation by setting QryPlan=0.
0
Bill BachPresident and Btrieve GuruCommented:
If you want to capture SQL statements coming from existing applications, then the query plan is probably the best option. However this can grab queries from all users at the same time, and the data must be parsed with either the included Query Plan Viewer (one at a time) or the third-party ParseQPF tool.

If you don't want to affect all users of this DSN, and if the queries are coming from remote workstations (not running locally), then another third-party tool called SQL Interceptor would work for you. It relies on the WinPcap library which must be installed separately, but it can quickly get you the statements from all users (if run on the server) or one user (if run on the workstation).

Beyond this, yopu can install the third-party WireShark, capture all network traffic to TCP port 1583 on the server, and parse the packets yourself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.