thor_search.pl
{
/* ______________________________________________________________________________
Copyright (C) DataAspects, Corp. 1998
thor_search.pl
Objective: To search foreign Thor databases and return
the SMILES and TDT with the WRNO, MF, MW and CAS
if the SMILES is also registered in the CIS.
Actions:
1. Client thor_search.pl connects to server chemcop.pl
running on ThorServer & passes it three parameters:
parm.0 db_exec -executes a SQL statement (see trim.h).
parm.1 'foreign_thor', a flag instructing chemcop.pl to run
thorserver/usr1/daylight/etcis_progs/search_foreign.c.
parm.2 list (foreign_thor_list)with one row and three strings:
1. 'dbname' (i.e. 'nci96')
2. 'datatype' (i.e. '$NSC')
3. 'search_value' (i.e. '84241')
Examples of formatted queries follow:
'nci96' '$NSC' '84241'
'nci96' '$CAS' '81-30-1'
'nci96' '$SMI' 'O=C1OC(=O)c2ccc3C(=O)OC(=O)c4ccc1c2c43'
2. Execute chemcop.pl on the server and pass it the foreign_thor_list.
3. Chemcop.pl executes search_foreign.c, a second server program.
4. search_foreign.c searches the specified Thor database
in the specified field for the search value. If the search value
was found, a second search is performed for the same value in
the cis in order to get the WRNO, MF, MW, and CAS. The following
values and lists are returned to the client.
(a) an integer code with the following definitions:
0 - smiles found in CIS database
1 - input value not found in selected database
2 - can't find smiles associated with input value
3 - smiles found in foreign database but not in cis database
4 - invalid smiles or parameters passed by calling program
5 - can't opens files for saving TDTs
6 - failed to connect to Thor server
7 - failed to open cis database
8 - failed to open selected input database
9 - TDT from foreign database does not have smiles
10 - failed to generate unique smiles from input smiles
Note the difference between 1 and 9:
A return value of 1 means there is no TDT in the selected database
corresponding to the search value. An error message is written to
an ASCII file: "ERROR:: X-REF xxxx not found in database"
A return value of 9 means there is a TDT in the database with the
search value, but the TDT does not have a SMILES. A number of the
commercial databases contain TDTs that are not SMILES rooted. In
this scenario, minimer_gen.c does not create an ASCII file with
an error message.
(b) If the search value was found in both Thor databases,
an ascii file is created on ThorServer with the
SMILES in the first line and WRNO, MF, MW, and CAS on the second.
If the search value was found only in the foreign db,
the ascii file contains only the SMILES.
(c) If the search value was found in the foreign database, the
TDT is written to an ascii file called foreign_tdt.out. If
the search value was not found foreign_tdt.out is still created
but it will be empty. Note that foreign_tdt.out is created
in the directory in which the vtxnetd daemon was started. Currently
this file is written to ThorServer/usr1/daylight/foreign_tdt.out.
Finally, chemcop opens this file and returns it to the client.
6. Notes:
(a) In Unix, the values for SMILES, WRNO and CAS must be delimited
with single quotes and search_foreign.pl does this.
(b) search_foreign.c can be run from the command line:
NCI # 84241 has a record in both nci96 and cis
etchem% search_foreign 'nci96' '$NSC' '84241'
O=C1OC(=O)c2ccc3C(=O)OC(=O)c4ccc1c2c43
015912,C14H4O6,268.18,81-30-1
(2 lines returned; TDT in filename foreign_tdt.out created)
NCI # 622776 exists only in nci96
etchem% search_foreign 'nci96' '$NSC' '622776'
COc1nc(N)c(NC(=O)C)c(=O)n1C
(1 line returned; TDT in filename foreign_tdt.out created)
NCI # 640038 returns no smiles.
etchem% search_foreign 'nci96' '$NSC' '640038'
etchem%
(No lines returned. Empty TDT in filename foreign_tdt.out created)
...Pat McGreevy, 18 Feb 99
______________________________________________________________________________ */
int c[7]; /* store position of comma delimiters */
int i; /* counter */
int rc; /* store return code fm server chemcopl.pl */
char cisserver[20]; /* ip address for CisServer */
char db[20]; /* Thor db selected by user */
char fld[20]; /* search field selected by user */
char value[2000]; /* search value */
char thorserver[20]; /* ip address for ThorServer */
list fld_list; /* searchable fields in thor db */
list foreign_thor_list; /* open Thor DBs */
list tdt_list; /* tdt returned fro ThorServer */
list temp_list; /* files returned from chemcopo */
cisserver = "xxx.xxx.xx.xx";
thorserver= "xxx.xxx.xx.xx";
printf("Wait...Connecting to Chemcop on ThorServer");
if (trap({
connect(0,"net:chemcop@"^^thorserver^^"!vtxhost.trm");
status("Connected to Chemcop");
})){
bell();
status("Failed to connect to Chemcop");
break;
}
/* ___________ Get Names of Thor DBs Currently Online using list_thor ________ */
if (!exec_sql("thor_open")){ /* RPC Call */
temp_list= list_open("select results",20, "Select Thor DB (i.e. nci96");
if (list_rows(temp_list)){ /* display open Thor DBs */
db= list_view3(temp_list,-2,-2,-1,key_f3,opt_highlight,0);
db= translate(db," ",""); /* remove spaces */
}
}
/* __________________ Get Query Fields for Selected Thor DB __________________ */
printf("Wait...Connecting to CIS Server");
if (trap({
connect(1,"net:sql$database@1958:"^^cisserver^^"(user/pass)!ljk");
status(" Connected to CISServer");
})){
bell();
status(" Failed to connect to CIS Server");
break;
}
fld_list= list_open("select field_name from thor "
"where dbname= &db ",
20,"Select Query Field:");
if (list_rows(fld_list)){
fld= list_view3(fld_list,-2,-2,-1,key_f3,opt_highlight,0);
fld= translate(fld," ",""); /* remove spaces */
}
else {
printf(" "^^db^^" not found in Thor table");
break;
}
/* ___________________ Get Search Value for Selected Field ___________________ */
value= prompt2(-2,-2,
"Enter Search Value. For example: ",
"'nci96' '$NSC' '84241'",
"'nci96' '$CAS' '81-30-1'",
"'nci96' '$SMI' 'O=C1OC(=O)c2ccc3C(=O)OC(=O)c4ccc1c2c43'",
"",100,vis_normal,1);
if (value)
translate(value," ",""); /* remove spaces */
/* _________________________ Format & Execute Query __________________________ */
foreign_thor_list= list_open("200",1,"'"^^db^^"' '"^^fld^^"' 'search value'");
list_mod(foreign_thor_list,1,"'"^^db^^"' '"^^fld^^"' '"^^value^^"'");
list_view3(foreign_thor_list,-2,-2,-1,key_f3,opt_browse,0);
connect(0);
rc= (exec_sql("thor_search",foreign_thor_list)); /* TRIMrpc call */
printf("rc returned from ChemCop is "^^rc);
printf(decode(rc,
0," rc=0: String found in "^^db^^" & WR Thor",
256," rc=1: String not found in "^^db,
512," rc=2: No SMILES for string in "^^db,
768," rc=3: String found in "^^db^^" but not WR Thor",
1024," rc=4: invalid SMILES or string",
1280," rc=5: can't open file for saving TDT",
1536," rc=6: can't connect to Thor server",
1792," rc=7: can't open WR Thor",
2048," rc=8: can't open "^^db^^" Thor",
2304," rc=9: TDT in "^^db^^" without a SMILES",
2560," rc=10: CANSMI failed to create USMILES",
" Can't interpret '"^^rc^^"' from ChemCop"));
if (rc == 0 || rc == 768){ /* if TDT created */
temp_list= list_open("select results",1000,"Returned from search_foreign.c");
if (list_rows(temp_list)){ /*list opened successfully*/
list_view3(temp_list,-2,-2,-1,key_f3,opt_highlight,0);
printf(list_curr(temp_list,0)); /* print SMILES */
if (list_rows(temp_list) == 2){ /* SMILES in foreign & CIS */
list_next(temp_list); /* move to data row */
for(i=1;i<=4;i++) /* find pos of commas */
c[i]= instr(list_curr(temp_list,0),",",1,i);
printf(substr(list_curr(temp_list,0),1,c[1]-1)); /* parse WRNO*/
printf(substr(list_curr(temp_list,0),c[1]+1, c[2]-c[1]-1)); /* parse MF */
printf(substr(list_curr(temp_list,0),c[2]+1, c[3]-c[2]-1)); /* parse MW */
printf(substr(list_curr(temp_list,0),c[3]+1)); /* parse CAS */
} /* end if (list_rows(temp_list)==2){ */
} /* end if (list_rows(temp_list)){ */
temp_list= list_open("select tdt",1000,"TDT from "^^db);
if (list_rows(temp_list)) /*list opened successfully*/
list_view3(temp_list,-2,-2,-1,key_f3,opt_browse,0);
} /* end if (rc == 0 || rc == 768 */
}