自学教程：C++ sqlSafef函数代码示例

void raIntoCdwRepeatQa(char *fileName, struct sqlConnection *conn, long long fileId)/* Read in two column file and put it into cdwQaRepeat table. */{struct lineFile *lf = lineFileOpen(fileName, TRUE);char *row[2];while (lineFileRow(lf, row))    {    char *repeatClass = row[0];    double mapRatio = lineFileNeedDouble(lf, row, 1);    char query[512];    sqlSafef(query, sizeof(query), 	"insert into cdwQaRepeat (fileId,repeatClass,mapRatio) values (%lld, /"%s/", %g)",	fileId, repeatClass, mapRatio);    sqlUpdate(conn, query);    }lineFileClose(&lf);}

struct hash *getRefSeqSummary(struct sqlConnection *conn)/* Return hash keyed by refSeq NM_ id, with description values. */{struct hash *hash = hashNew(16);char query[256];sqlSafef(query, sizeof(query), "select mrnaAcc,summary from %s", summaryTable);struct sqlResult *sr = sqlGetResult(conn, query);char **row;while ((row = sqlNextRow(sr)) != NULL)    {    subChar(row[1], '/n', ' ');    hashAdd(hash, row[0], cloneString(row[1]));    }sqlFreeResult(&sr);verbose(1, "%d %s elements/n", hash->elCount, summaryTable);return hash;}

void edwMakeReplicateQa(int startId, int endId)/* edwMakeReplicateQa - Do qa level comparisons of replicates.. */{/* Make list with all files in ID range */struct sqlConnection *conn = sqlConnect(edwDatabase);char query[256];sqlSafef(query, sizeof(query),     "select * from edwFile where id>=%d and id<=%d and endUploadTime != 0 "    "and updateTime != 0 and deprecated = ''",     startId, endId);struct edwFile *ef, *efList = edwFileLoadByQuery(conn, query);for (ef = efList; ef != NULL; ef = ef->next)    {    doReplicateQa(conn, ef);    }}

char *queryCellVal(struct column *col, struct subjInfo *si,        struct sqlConnection *conn)/* return query lookup on subj id */{char query[256];char *answer;sqlSafef(query, sizeof(query), col->query, si->fields[0]);answer = sqlQuickString(conn, query);if (answer == NULL)    {    return(cloneString("-1"));    }else    {    return answer;    }}

static void processProtSeq(FILE *fh, struct sqlConnection *conn, struct refSeqVerInfo *rsvi, struct hash *doneProts)/* get an protein sequence, which already includes version in name. Don't duplicate NPs */{char query[128];sqlSafef(query, sizeof(query), "SELECT protAcc FROM refLink WHERE mrnaAcc = /"%s/"", rsvi->acc);char *protAcc = sqlNeedQuickString(conn, query);if (isNotEmpty(protAcc) && hashLookup(doneProts, protAcc) == NULL)    {    struct dnaSeq *seq = hGenBankGetPepC(conn, protAcc, NULL);    if (seq == NULL)        errAbort("failed to get %s from database", protAcc);    faWriteNext(fh, seq->name, seq->dna, seq->size);    dnaSeqFree(&seq);    hashAdd(doneProts, protAcc, NULL);    }freeMem(protAcc);}

static void visiGeneMatchSubmitId(struct visiSearcher *searcher, 	struct sqlConnection *conn, struct slName *wordList)/* Add images matching submitId  accessions to searcher. */{struct slName *word;int wordIx;for (word = wordList, wordIx=0; word != NULL; word = word->next, ++wordIx)    {    char query[512];    sqlSafef(query, sizeof(query),    	"select image.id from image,imageFile "	"where imageFile.submitId = /"%s/" "	"and imageFile.id = image.imageFile", word->name);    addImageListAndFree(searcher, sqlQuickNumList(conn, query),	wordIx, 1);    }}

void outputAlignmentForStan(struct sqlConnection *conn, struct stanMad *sm, struct hash *iHash, FILE *out){struct psl *pslList, *bestPsl = NULL;char buff[1024];int i;struct imageClone *ic = NULL;sprintf(buff, "%d", sm->clid);printf("Looking for %s/n", buff);ic = hashFindVal(iHash, buff);if(ic != NULL)     {    /* first try looking for the image clones themselves... */    for(i=0; i<ic->numGenbank; i++) 	{	char query[1024];	sqlSafef(query, sizeof query, "select * from all_est where qName='%s'", ic->genbankIds[i]);	pslList = pslLoadByQuery(conn, buff);	if(pslList != NULL) 	    {	    slSort(&pslList, pslCmpScore);		    if(bestPsl == NULL || (pslScore(pslList) > pslScore(bestPsl)))		pslFree(&bestPsl);		bestPsl = copyPsl(pslList);	    }		pslFreeList(&pslList);	}    if(bestPsl != NULL)	{    	freez(&bestPsl->qName);	sprintf(buff, "%d", sm->clid);	bestPsl->qName = cloneString(buff);	pslTabOut(bestPsl,out);	}    else 	{	fprintf(out, "%d/talignment unknown/n", sm->clid);	}        }else     {    fprintf(out, "%d/tunknown/n", sm->clid);    }}

struct hash *makeProtHash(struct sqlConnection *conn, 	char *linkTable, char *geneField, char *protField)/* Make hash that goes from gene to prot. */{char query[256];struct sqlResult *sr;char **row;struct hash *hash = newHash(18);printf("looking up proteins/n");sqlSafef(query, sizeof(query),    "select %s,%s from %s", geneField, protField, linkTable);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    hashAdd(hash, row[0], cloneString(row[1]));sqlFreeResult(&sr);return hash;}

double gtexMaxMedianScore(char *version)/* Retrieve max median score for latest (or named) version */{char query[1024];struct sqlConnection *conn = hAllocConn("hgFixed");if (!conn)    return 0;// TODO: trackDB setting for thisif (!version || sameString(version, ""))    version = GTEX_DEFAULT_VERSION;sqlSafef(query, sizeof query, "select maxMedianScore from gtexInfo where version='%s'", version);double score = sqlQuickDouble(conn, query);if (score == 0.0)    errAbort("Internal error: GTEx version /"%s/" not found in gtexInfo table", version);hFreeConn(&conn);return score;}

struct edwQaContamTarget *getContamTargets(struct sqlConnection *conn,     struct edwFile *ef, struct edwValidFile *vf)/* Get list of contamination targets for file - basically all targets that aren't in same * taxon as self. */{assert(vf->ucscDb != NULL);struct edwAssembly *origAsm = edwAssemblyForUcscDb(conn, vf->ucscDb);assert(origAsm != NULL);char query[256];sqlSafef(query, sizeof(query),     "select edwQaContamTarget.* from edwQaContamTarget,edwAssembly "    "where edwQaContamTarget.assemblyId = edwAssembly.id "         " and edwAssembly.taxon != %d", origAsm->taxon);struct edwQaContamTarget *targetList  = edwQaContamTargetLoadByQuery(conn, query);edwAssemblyFree(&origAsm);return targetList;}

static char *getCcdsGeneSymbol(struct sqlConnection *conn, struct ccdsInfo *rsCcds)/* get the gene name for a CCDS */{struct ccdsInfo *ci;char accBuf[GENBANK_ACC_BUFSZ], query[256];char *geneSym = NULL;for (ci = rsCcds; ci != NULL; ci = ci->next)    {    sqlSafef(query, sizeof(query), "select name from %s where mrnaAcc='%s'",          refLinkTable, genbankDropVer(accBuf, ci->mrnaAcc));    geneSym = sqlQuickString(conn, query);    if (geneSym != NULL)        return geneSym;    }return NULL;}

static void getRepeatsTable(struct sqlConnection *conn, char *table,    char *chrom, struct rbTree **retAllRepeats,	struct rbTree **retNewRepeats)/* Return a tree of ranges for sequence gaps in chromosome from *	specified table */{struct sqlResult *sr;char **row;struct rbTree *allTree = rbTreeNew(simpleRangeCmp);struct rbTree *newTree = rbTreeNew(simpleRangeCmp);char query[256];struct simpleRange *prevRange = NULL, *prevNewRange = NULL;sqlSafef(query, ArraySize(query), "select chromStart,chromEnd from %s "	    "where chrom = /"%s/"", table, chrom);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    struct simpleRange *range;    lmAllocVar(allTree->lm, range);    range->start = sqlUnsigned(row[0]);    range->end = sqlUnsigned(row[1]);    if (prevRange == NULL)	prevRange = range;    else if (overlap(range, prevRange))	{	/* merge r into prevR & discard; prevR gets passed forward. */	if (range->end > prevRange->end)	    prevRange->end = range->end;	if (range->start < prevRange->start)	    prevRange->start = range->start;	}    else	{	rbTreeAdd(allTree, prevRange);	prevRange = range;	}    }if (prevRange != NULL)    rbTreeAdd(allTree, prevRange);if (prevNewRange != NULL)    rbTreeAdd(newTree, prevNewRange);sqlFreeResult(&sr);*retAllRepeats = allTree;*retNewRepeats = newTree;}	/*	static void getRepeatsTable()	*/

void hgRatioMicroarray(char *absTable, char *relTable)/* hgRatioMicroarray - Create a ratio form of microarray. */{struct maMedSpec *clumpList = NULL;struct sqlConnection *conn = sqlConnect(database);struct sqlResult *sr;char **row;char query[512];struct expData *ex;struct expData *expList = NULL;FILE *f = hgCreateTabFile(tabDir, relTable);int rowCount = 0;if (clump != NULL)    clumpList = maMedSpecReadAll(clump);sqlSafef(query, sizeof(query),	"select * from %s", absTable);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    ex = expDataLoad(row);    slAddHead(&expList, ex);    if (limit != 0 && rowCount >= limit)        break;    }sqlFreeResult(&sr);slReverse(&expList);maExpDataClipMin(expList, minAbsVal, minAbsVal * 0.5);maExpDataAddConstant(expList, c);if (transpose)    maExpDataDoLogRatioTranspose(expList, doAverage);else    maExpDataDoLogRatioGivenMedSpec(expList, clumpList, (doAverage) ? useMean : useMedian);for (ex = expList; ex != NULL; ex = ex->next)    expDataTabOut(ex, f);if (doLoad)    {    expDataCreateTable(conn, relTable);    hgLoadTabFile(conn, tabDir, relTable, &f);    hgRemoveTabFile(tabDir, relTable);    }expDataFreeList(&expList);sqlDisconnect(&conn);}

static void displayRetroDetails(struct sqlConnection *conn, struct mappingInfo *mi)/* display information from a retroXXXInfo table */{struct ucscRetroInfo *pg = mi->pg;char query[256];char orthoTable[128];char orgDb[128];char *org;if (mi->suffix != NULL && strlen(mi->suffix) > 0)    safef(orthoTable, sizeof(orthoTable), "%s%sOrtho%s",             mi->tblPre, mi->geneSet, mi->suffix);else    safef(orthoTable, sizeof(orthoTable), "%s%sOrtho",             mi->tblPre, mi->geneSet);printf("<TABLE class=/"transMap/">/n");printf("<H3><A HREF=/"#orthology/">Break in Orthology:</A></H3>/n");printf("<THEAD>/n");printf("<TR><TH>Organism<TH>Score</TR>/n");printf("</THEAD><TBODY>/n");if (hTableExists(database, orthoTable))    {    struct sqlResult *sr;    char **row;    sqlSafef(query, sizeof(query), "select * from %s where name = '%s' ",             orthoTable, pg->name);    sr = sqlGetResult(conn, query);    while ((row = sqlNextRow(sr)) != NULL)        {        struct ucscRetroOrtho *puro = ucscRetroOrthoLoad(row);        /* get substring after "net" prefix and convert first char to lower           case then get organism name */        safecpy(orgDb, sizeof(orgDb), puro->db+3);        orgDb[0] = tolower(orgDb[0]);        org = hOrganism(orgDb);        printf("<TR><TH>%s (%s) ", org, orgDb);        printf("<TD>%d</TR>/n", puro->overlap);        }    sqlFreeResult(&sr);    }else    printf("<TR><TH>table %s not found </TR>", orthoTable);printf("</TBODY></TABLE>/n");}

static struct slName *expTissuesForProbeInImage(struct sqlConnection *conn, 	int imageId,	int probeId)/* Get list of tissue where we have expression info in gene. * Put + or - depending on expression level. */{struct dyString *dy = dyStringNew(0);struct slName *tissueList = NULL, *tissue;char query[512], **row;struct sqlResult *sr;sqlSafef(query, sizeof(query),   "select bodyPart.name,expressionLevel.level,expressionPattern.description "   "from expressionLevel join bodyPart join imageProbe "   "left join expressionPattern on expressionLevel.expressionPattern = expressionPattern.id "   "where imageProbe.image = %d "   "and imageProbe.probe = %d "   "and imageProbe.id = expressionLevel.imageProbe "   "and expressionLevel.bodyPart = bodyPart.id "   "order by bodyPart.name"   , imageId, probeId);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    double level = atof(row[1]);    char *pattern = row[2];    if (pattern)    	tolowers(pattern);    dyStringClear(dy);    dyStringAppend(dy, row[0]);    if (level == 1.0)       dyStringAppend(dy, "(+)");    else if (level == 0.0)       dyStringAppend(dy, "(-)");    else        dyStringPrintf(dy, "(%.2f)",level);    if (pattern && !sameWord(pattern,"Not Applicable") && !sameWord(pattern,"Not Specified"))    	dyStringPrintf(dy, " %s",pattern);    tissue = slNameNew(dy->string);    slAddHead(&tissueList, tissue);    }sqlFreeResult(&sr);slReverse(&tissueList);dyStringFree(&dy);return tissueList;}

/* check if a locusID points to a KG mRNA */boolean checkMrna(char *locusID){    struct sqlConnection *conn, *conn2;    char query2[256];    struct sqlResult *sr2;    char **row2;    boolean result;    char cond_str[256];    char *chp;    char *gbAC;    char *gbID;    char *knownGeneID;    conn   = hAllocConn();    conn2  = hAllocConn();    result = FALSE;    sqlSafef(query2, sizeof query2, "select gbAC from %s.locus2Acc0 where locusID=%s and seqType='m';",             tempDbName, locusID);    sr2 = sqlMustGetResult(conn2, query2);    row2 = sqlNextRow(sr2);    while (row2 != NULL)    {        gbAC  	= row2[0];        gbID = strdup(gbAC);        chp = strstr(gbID, ".");        if (chp != NULL) *chp = '/0';        sqlSafefFrag(cond_str, sizeof cond_str, "name = '%s';", gbID);        knownGeneID = sqlGetField(dbName, "knownGene", "name", cond_str);        if (knownGeneID != NULL)        {            result=TRUE;            break;        }        row2 = sqlNextRow(sr2);    }    hFreeConn(&conn);    hFreeConn(&conn2);    sqlFreeResult(&sr2);    return(result);}

void mapAltGraphXFile(struct sqlConnection *conn, char *db, char *orthoDb, char *chrom,		      char *netTable, char *altGraphXFileName, char *altGraphXTableName,		      FILE *agxOut, FILE *selectedOut, int *foundCount, int *notFoundCount)/* Map over altGraphX Structures from one organism toanother. Basically create a mapping for the vertices and then reversethem if on '-' strand.*/{int count =0;struct bed *bed = NULL;struct altGraphX *agList = NULL, *ag = NULL, *agNew = NULL;if(altGraphXFileName != NULL)    {    warn("Loading altGraphX Records from file %s.", altGraphXFileName);    agList = altGraphXLoadAll(altGraphXFileName);    }else if(altGraphXTableName != NULL)    {    char query[256];    warn("Reading altGraphX Records from table %s.", altGraphXTableName);    sqlSafef(query, sizeof(query), "select * from %s where tName like '%s'", altGraphXTableName, chrom);    agList = altGraphXLoadByQuery(conn, query);    }else    errAbort("orthoMap::mapAlGraphXFile() - Need a table name or file name to load altGraphX records");warn("Mapping altGraphX records.");for(ag = agList; ag != NULL; ag = ag->next)    {    if(differentString(ag->tName, chrom))	continue;    occassionalDot();    agNew = mapAltGraphX(ag, conn, db, netTable);    if(agNew == NULL)	(*notFoundCount)++;    else	{	(*foundCount)++;	altGraphXTabOut(agNew, agxOut);	altGraphXFree(&agNew);        if (selectedOut != NULL)            altGraphXTabOut(ag, selectedOut);	}    count++;    }	}

struct microData *lookupGenes(struct sqlConnection *conn, char *table, struct microData *oldList)/* Use gene list to lookup */{struct microData *newList = NULL, *gene, *geneCopy, *next;struct hash *hash = newHash(0);struct sqlResult *sr;char **row;char query[256];/* Load up hash from lookup table.  We are doing inverse lookup on it * actually. */sqlSafef(query, sizeof(query), "select name,value from %s", table);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    char *geneName = row[0];    char *expName = row[1];    hashAdd(hash, expName, cloneString(geneName));    }/* Move genes in oldList that hit hash to newList.  * If more than one new gene hits then make a (shallow) * dupe of it and put it on newList too.  This would * be a nightmare if we were actually going to free this * memory, but as a simple file filter there's no need. */for (gene = oldList; gene != NULL; gene = next)    {    struct hashEl *hel;    next = gene->next;    hel = hashLookup(hash, gene->name);    if (hel != NULL)        {	gene->name = hel->val;	slAddHead(&newList, gene);	while ((hel = hashLookupNext(hel)) != NULL)	    {	    geneCopy = CloneVar(gene);	    geneCopy->name = hel->val;	    slAddHead(&newList, geneCopy);	    }	}    }slReverse(&newList);return newList;}

static int vgForwardedImage(struct sqlConnection *conn, int image)/* Return id of image with better caption information if available. * Otherwise return zero.  We are working in image ids instead of  * imageFile ids only because much of the software works off image ids.  */{int imageFile = visiGeneImageFile(conn, image);int forwardedImage = 0;int forwardedImageFile = visiGeneForwardedImageFile(conn, imageFile);if (forwardedImageFile)     {     char query[256];     sqlSafef(query, sizeof(query),      	"select id from image where imageFile=%d",        forwardedImageFile);     forwardedImage = sqlQuickNum(conn, query);     }return forwardedImage;}

static void processMetaData(FILE *fh, struct sqlConnection *conn, struct sqlConnection *conn2, struct refSeqVerInfo *rsvi)/* get meta data for an accession */{boolean isCoding = genbankIsRefSeqCodingMRnaAcc(rsvi->acc);char query[256];sqlSafef(query, sizeof(query), "SELECT rl.name,rl.product,rl.protAcc,rl.locusLinkId,rs.status FROM refLink rl, refSeqStatus rs WHERE (rl.mrnaAcc = /"%s/") and (rs.mrnaAcc = rl.mrnaAcc)", rsvi->acc);struct sqlResult *sr = sqlGetResult(conn, query);char **row = sqlNextRow(sr);if (row == NULL)    errAbort("no RefLink entry for %s", rsvi->acc);char buf[32];char *protAccVer = getProtAccVerIf(conn2, rsvi->acc, row[2], buf, sizeof(buf));char *cds = isCoding ? getCds(conn2, rsvi->acc) : "";fprintf(fh, "%s.%d/t%s/t%s/t%s/t%s/t%s/t%s/n", rsvi->acc, rsvi->ver, protAccVer, row[0], row[3], cds, row[1], row[4]);sqlFreeResult(&sr);if (isCoding)    freeMem(cds);}

struct hash *loadNameTable(struct sqlConnection *conn,     char *tableName, int hashSize)/* Create a hash and load it up from table. */{char query[128];struct sqlResult *sr;char **row;struct hash *hash = newHash(hashSize);sqlSafef(query, sizeof query, "select id,name from %s", tableName);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    hashAdd(hash, row[1], intToPt(sqlUnsigned(row[0])));    }sqlFreeResult(&sr);return hash;}

unsigned findNewId(struct sqlConnection *conn, struct edwAnalysisRun *oldRun, unsigned oldId)/* Given old ID, find corresponding new one. */{char query[256];sqlSafef(query, sizeof(query),     "select * from edwAnalysisRun where firstInputId = %u and analysisStep = '%s'"    " and id != %u "    "order by id desc"    , oldRun->firstInputId, oldRun->analysisStep, oldRun->id);struct edwAnalysisRun *newRun = edwAnalysisRunLoadByQuery(conn, query);if (newRun == NULL)    errAbort("NULL result from %s/n", query);if (newRun->createStatus <= 0)     return 0;if (isEmpty(newRun->createFileIds))    errAbort("Strange no createFileIds result from %s", query);return newRun->createFileIds[0];}

void syncSoftware(struct sqlConnection *conn)/* Sync the eapSoftware table */{char query[256];sqlSafef(query, sizeof(query), "select * from eapSoftware where metaUuid=''");struct eapSoftware *sw, *swList = eapSoftwareLoadByQuery(conn, query);for (sw = swList; sw != NULL; sw = sw->next)    {    if (isupper(sw->name[0]))        {	warn("Skipping %s until they handle upper case right", sw->name);	continue;	}    struct jsonWrite *json = jsonForSoftware(sw);    syncOneRecord(conn, "software", json, "eapSoftware", sw->id);    jsonWriteFree(&json);    }}

static void printValueRange(char *db, char *table, char *field)/* Print min/max/mean. */{struct sqlConnection *conn = hAllocConn(db);struct sqlResult *sr;char **row;char query[256];sqlSafef(query, sizeof(query),   "select min(%s), max(%s), avg(%s) from %s", field, field, field, table);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    hPrintf("<B>min:</B> %s <B>max:</B> %s <B>average:</B> %s/n",    	row[0], row[1], row[2]);    }hFreeConn(&conn);}

struct edwScriptRegistry *edwScriptRegistryFromCgi()/* Get script registery from cgi variables.  Does authentication too. */{struct sqlConnection *conn = edwConnect();char *user = sqlEscapeString(cgiString("user"));char *password = sqlEscapeString(cgiString("password"));char query[256];sqlSafef(query, sizeof(query), "select * from edwScriptRegistry where name='%s'", user);struct edwScriptRegistry *reg = edwScriptRegistryLoadByQuery(conn, query);if (reg == NULL)    accessDenied();char key[EDW_SID_SIZE];edwMakeSid(password, key);if (!sameString(reg->secretHash, key))    accessDenied();sqlDisconnect(&conn);return reg;}

struct agpFrag *loadChromAgp(struct sqlConnection *conn, char *chrom)/* Load all AGP fragments for chromosome. */{char query[256];struct sqlResult *sr;char **row;struct agpFrag *fragList = NULL, *frag;sqlSafef(query, sizeof query, "select * from %s_gold", chrom);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    frag = agpFragLoad(row);    slAddHead(&fragList, frag);    }slReverse(&fragList);return fragList;}

static void geneReviewsPrint(struct section *section, 	struct sqlConnection *conn, char *itemName)/* print GeneReviews short label associated to this refGene item */{char query[256];char * geneSymbol;if (sqlTableExists(conn, "geneReviewsDetail"))    {    sqlSafef(query, sizeof(query), "select geneSymbol from kgXref where kgId = '%s'", itemName);    geneSymbol = sqlQuickString(conn, query);    if (geneSymbol != NULL)        {           prGRShortKg(conn,geneSymbol);        } else {           hPrintf("<B>No GeneReview for this gene </B><BR>" );    }  }}

static char *genomeDbForImage(struct sqlConnection *conn, int imageId)/* Return the genome database to associate with image or NULL if none. */{char *db;char *scientificName = visiGeneOrganism(conn, imageId);struct sqlConnection *cConn = hConnectCentral();char query[256];sqlSafef(query, sizeof(query), 	"select defaultDb.name from defaultDb,dbDb where "	"defaultDb.genome = dbDb.organism and "	"dbDb.active = 1 and "	"dbDb.scientificName = '%s'" 	, scientificName);db = sqlQuickString(cConn, query);hDisconnectCentral(&cConn);return db;}