export query log

Post by **mark** » Tue Oct 05, 2004 5:49 pm

After that SQL statement $String will contain the name of the active database for the specified profile, "test" in the example. It's not good to hard-code the database as it will change if/when you perform a new walk.

<$mydb=$String>
<sql db=$mydb "select ... from querylog where id>=$mydate">

KMandalia · Post by **KMandalia** » Wed Oct 06, 2004 12:05 pm

It gives me error (texis usage....) when I give the mydate from command line.

Post by **mark** » Wed Oct 06, 2004 12:54 pm

Did you quote the value? On windows you probably need " insatead of '. If that's not it please show exactly what command line you tried.

KMandalia · Post by **KMandalia** » Wed Oct 06, 2004 1:23 pm

yes, thank you.

Double quotes did the job.

patel1 · Post by **patel1** » Wed Oct 06, 2004 3:10 pm

Hello,
In what file do I put the script so that it will output the query log to a .csv file? How do I actually get to interact with the database?

-Jay
(patel@cs.utk.edu)

Post by **mark** » Wed Oct 06, 2004 3:51 pm

I believe KMandalia is writing a new script to do this task. Interact with the database using <sql>.

patel1 · Post by **patel1** » Wed Oct 06, 2004 3:57 pm

Mark,
I have the code below in a file called "test.script":
<SCRIPT LANGUAGE=vortex>
<WRITE somefile.csv>
<SQL ROW "select from querylog where id>'2004-10-04 15:00:00">
<fmt %at "%Y-%m-%d %H:%M:%S" $id>,$Client,$Info,$Query
</SQL>
</WRITE>
</SCRIPT>

I am using the following command to execute it:
texis -f "test.script"

but I get the following error:

015 test.script:2: No HTML/text allowed outside functions

What am I doing wrong?

-Jay
(patel@cs.utk.edu)

Post by **mark** » Wed Oct 06, 2004 4:06 pm

You're missing a main function. And you need to specify a database either at the top using a <db=> directive or in the sql with a db= parameter.

<script language=vortex>
<a name=main>
<sql row db=yourdatabase "...">
</sql>
</a>
</script>

http://www.thunderstone.com/texis/site/ ... hello.html

KMandalia · Post by **KMandalia** » Wed Oct 06, 2004 5:14 pm

For patel1:

Since I am not planning on deleting the Querylog table frequently, I wrote the following to get what I wanted on a daily/weekly/monthly basis:

<script language=vortex>
<a name=main public mydb start end>
<WRITE somefile.csv>
<SQL "select String from options where Profile='myprofile' and Name='SS_db'"></sql>
<$mydb=$String>
<sql db=$mydb "select distinct id,Client,Query from querylog where id>=$start and id<$end">
<fmt %at "%Y-%m-%d %H:%M:%S" $id>,$Client,$Query
</SQL>
</WRITE>
</a>
</script>

You can save this script anywhere in your INSTALLDIR but make sure texis.exe is also available in the same folder (If you want, you can copy texis.exe to anywhere you want to keep your script. SAVE YOUR SCRIPT WITHOUT ANY EXTENSIONS)

Then from the command line:

C:/INSTALLDIR/texis start="YYYY-MM-DD HH:MM:SS" end="YYYY-MM-DD HH:MM:SS" myscriptname/main.txt

That's what I have done.

Post by **mark** » Wed Oct 06, 2004 6:06 pm

Better to only have one copy of texis.exe so you don't have a mess at upgrade time. If texis.exe is in the path or you refer to it by path you'll be fine. The script can be anywhere. If it's in your current directory you don't need a path on it. If it's not you need a path.

C:/INSTALLDIR/texis start="YYYY-MM-DD HH:MM:SS" end="YYYY-MM-DD HH:MM:SS" myscriptname/main.txt

C:/INSTALLDIR/texis start="YYYY-MM-DD HH:MM:SS" end="YYYY-MM-DD HH:MM:SS" c:/some/place/myscriptname/main.txt

Also, depending on your application for the resulting csv file, you may not want the "distinct" in the sql.