Thunderstone Support Forums

I've trying to execute a SQL script using exec and it doesn't seem to be working. I'm not sure what I'm missing. The content of the string $command is this:

/opt/morph3/bin/tsql < /opt/data1/texisdb/indexScripts/create_PATN_PATN_TTL_MI.sql > /opt/data1/texisdb/indexScripts/indexProgress.out

I'm executing the command with this:

<exec $command></exec>

But it never seems to even start. I've tried also preceeding the command with "nohup" but that didn't help. (I'm by no means a linux expert, so I'm not even sure if it made sense to do that!)

BTW, the content of the sql file is statements which drop and recreate one of our metamorph indexes.

Any help would be appreciated

You're using shell redirection operators.
Try <exec "/bin/sh" "-c" $command></exec>

But more significantly why exec? Why not just run the update directly in vortex?

If you're using a version 5+ Texis you can use the FROMFILE and/or TOFILE args to <exec> to do file redirection:

<exec FROMFILE=/opt/data1/texisdb/indexScripts/create_PATN_PATN_TTL_MI.sql TOFILE=/opt/data1/texisdb/indexScripts/indexProgress.out /opt/morph3/bin/tsql>
</exec>

though I'm also curious why you're not just using <sql> in Vortex.

Some of our indexes take upwards of 6 or 7 hours to build. If I use <sql> in Vortex, doesn't the code hang on that statement until the command completes? And if the browser session behind which the <sql> statement originated is closed, isn't the command aborted? If neither of these is true, then yes... I will just switch over to using <sql>.

(In other words, this must be done asynchronously or it is of no use to us.)

If I use <exec "/bin/sh" "-c" $command></exec> then the string in the variable $command must also include "tsql" correct?

(Sorry. Dumb question! It already does! Nevermind!)

Yes, sql will wait until finished and be subject to client disconnection, as will exec unless you use bkgnd.

You can use "connreset" to avoid death on disconnect.
http://thunderstone.master.com/texis/ma ... reset&s=.2

Hmm. None of this seems to be working. I've created a function that's being called but Texis is not starting the creation of the index. Here is the entire script:

<a name="RebuildIndex" PRIVATE indexScript>
<local scriptFile scriptOutputFile errorFile prev>
<strfmt "%s/%s" $indexScriptDir $indexScript>
<$scriptFile=$ret>
<strfmt "%s/%s" $indexScriptDir $progessFile>
<$scriptOutputFile=$ret>
<strfmt "%s/%s" $indexScriptDir "createError.out">
<$errorFile=$ret>
<vxcp connreset on>
<$prev=$ret>
<exec FROMFILE=$scriptFile TOFILE=$scriptOutputFile STDERRTOFILE=$errorFile BKGND $tsql></exec>
<vxcp connreset $prev>
</a>

$indexScriptDir and $tsql are global variables. When I display these, and the locally created variables, from within the function they look correct. I've tried this with and without the use of connreset in <vxcp ...>

I've double checked all the paths, and they're correct. Here is the content of the script file that I want tsql to read from:

DROP INDEX PATN_PATN_TTL_MI;
set delexp=0;
set addexp = '\alnum{1,99}';
set keepnoise=1;
set indexmeter=1;
create METAMORPH INVERTED index PATN_PATN_TTL_MI on PATN(PATN_TTL,PATN_ISD);

When I use a command prompt (e.g., from PuTTY) to run "tsql < <scriptpath> > <outputpath>" using the same paths everything works fine.

I feel like I'm getting close, but I'm still doing something wrong.

Check your vortex.log or raw script output to see if there are any errors during the exec.

When you run from the command line are you logged in as the texis user or some other user? If not texis maybe the texis user can't write the specified output files or read the specified input.

Since you're using bkgnd you don't need connreset at all. If you were to use connreset to prevent death on disconnect you would want a value of 39, not "on". "On" is the default and will kill your app on disconnect.

Why are you dropping and recreating instead of just updating?

After I got the syntax and structure of the code worked out, it turned out that there *was* a permissions problem as well. That has been resolved, and now everything seems to work great!

Thanks Mark and Kai for your help. As usual, I really appreciate it!