Help using Open Process

Eggplant Functional
1

I’ve been using Shell scripts to run some sql queries using sqlplus so far. This has worked out fine because the data was a bit too large to handle easily from within Eggplant.

But I’m starting to find a need to run a sqlplus query on the fly and use the results in some eggplant scripts. I’m sure “open process” can do this, but I need some help with the syntax.

here’s what my shell scripts look like:


#! /bin/bash
DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/Applications/instantclient 
export DYLD_LIBRARY_PATH
TNS_ADMIN=/Applications/instantclient 
export TNS_ADMIN 
PATH=$PATH:/Applications/instantclient 
export PATH

sql=$(cat /EggplantShared/Lib/env) -- we have 3 different database connections depending on the testing phase, I use this to determine which one to use. 

sqlplus User_Name/User_password@$sql @/Path_to_script.sql

exit

(The Path information in the above script is actually part of my .bash_profile , so I don’t know if I’m actually going to need that in here. I did for the shell scripts, but don’t if I just use terminal. )

The above example kicks off a sql query script. But i want to just run a query at that point.

One of my attempts to get this work looked like this:



set bash to "/bin/bash"
open process bash

write {{
"DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/Applications/instantclient 
export DYLD_LIBRARY_PATH
TNS_ADMIN=/Applications/instantclient 
export TNS_ADMIN 
PATH=$PATH:/Applications/instantclient 
export PATH"
}} & return to process bash
write "sqlplus username/userpassword@test" & return to process bash
write "sql query goes here';" & return to process bash
write "exit" & return to process bash
read from process bash until end
put it 
close process bash

when I run this, it seems to run for about 20 seconds and does not produce any results. Any ideas?
Does anyone else use sqlplus for database queries on-the-fly?

Thanks in advance.

2

I don’t think this works because when you run the sqlplus command, you’re actually going into a subprocess of the bash process. So you keep writing to the bash process, but that’s not the same effect as continuing to type in the terminal would be, where the commands are going to the sqlplus subprocess. I’ll try to get some confirmation of that theory from the developers when I can.

You might want to consider setting up a merge template to run your sql query, similar to the way you’re using the shell script now. A merge template includes variables that get dereferenced and even code that can be executed when the template is parsed. Here are a couple of examples from the documentation:

put "Jorge" into name 
put merge("Hello [[name]], how are you?") -- displays "Hello Jorge, how are you?" 
put merge of "[[repeat with n=0 to 99]][[n]],[[end repeat]]" 
into numbersList 
put merge(template) into file "/Library/WebServer/Documents/report.html

So what I’m suggesting is that you could keep most of your original (i.e., non-process) code as-is, but turn your shell script into a template, put your query elements into variables in your script, then write the template out as a shell script and execute it. I did something similar years ago to run queries against a mysql database and it worked well (I’d post the code if I could find it).