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

void showSeqFromTable(struct sqlConnection *conn, char *geneId,	char *geneName, char *table)/* Show some sequence from given table. */{char query[512];struct sqlResult *sr;char **row;hPrintf("<TT><PRE>");safef(query, sizeof(query),     "select seq from %s where name = '%s'", table, geneId);sr = sqlGetResult(conn, query);if ((row = sqlNextRow(sr)) != NULL)    {    char *seq = row[0];    hPrintf(">%s (%s) length=%d/n", geneId, geneName, (seq!=NULL) ? (int)strlen(seq): 0);    writeSeqWithBreaks(stdout, seq, strlen(seq), 60);    }sqlFreeResult(&sr);hPrintf("</PRE></TT>");}

struct refLink *loadRefLink(char *database, struct hash *hash)/* Load refLink into hash keyed by mrna accession and * return list too. */{struct sqlConnection *conn = hAllocConn(database);struct sqlResult *sr;char **row;struct refLink *list = NULL, *el;sr = sqlGetResult(conn, "select * from refLink");while ((row = sqlNextRow(sr)) != NULL)    {    el = refLinkLoad(row);    hashAdd(hash, el->mrnaAcc, el);    slAddHead(&list, el);    }sqlFreeResult(&sr);hFreeConn(&conn);slReverse(&list);return list;}

void addSizes(char *database, struct hash *eiHash, struct hash *dateHash)/* Add and date info to ests in eiHash. Date strings are stored in dateHash */{struct sqlConnection *conn = sqlConnect(database);struct sqlResult *sr = NULL;char **row;sr = sqlGetResult(conn, "NOSQLINJ select acc,size,gb_date from seq");while ((row = sqlNextRow(sr)) != NULL)    {    char *acc = row[0];    struct estInfo *ei = hashFindVal(eiHash, acc);    if (ei != NULL)        {	ei->size = sqlUnsigned(row[1]);	ei->date = hashStoreName(dateHash, row[2]);	}    }sqlFreeResult(&sr);sqlDisconnect(&conn);}

struct hash *posHashFromTable(struct sqlConnection *conn, char *table)/* Store name, chrom, chromStart in a hash of slPairs. (cheap) */{    struct hash *posHash = newHash(24);    struct sqlResult *sr = NULL;    char query[256];    char **row;    sqlSafef(query, sizeof(query), "select name,chrom,chromStart from %s", table);    sr = sqlGetResult(conn, query);    while ((row = sqlNextRow(sr)) != NULL)    {        int chromStart = (int)sqlUnsigned(row[2]);        struct slPair *pair;        AllocVar(pair);        pair->name = cloneString(row[1]);        pair->val = intToPt(chromStart);        hashAdd(posHash, row[0], pair);    }    sqlFreeResult(&sr);    return posHash;}

struct rbTree *getSeqGaps(struct sqlConnection *conn, char *chrom)/* Return a tree of ranges for sequence gaps in chromosome */{struct rbTree *tree = rbTreeNew(simpleRangeCmp);int rowOffset;struct sqlResult *sr = hChromQuery(conn, "gap", chrom, NULL, &rowOffset);char **row;while ((row = sqlNextRow(sr)) != NULL)    {    struct agpGap gap;    struct simpleRange *range;    agpGapStaticLoad(row+rowOffset, &gap);    lmAllocVar(tree->lm, range);    range->start = gap.chromStart;    range->end = gap.chromEnd;    rbTreeAdd(tree, range);    }sqlFreeResult(&sr);return tree;}

static struct genbankCds getCds(struct sqlConnection *conn, struct mappingInfo *mi)/* Get CDS, return empty genebankCds if not found or can't parse  */{char query[256];struct sqlResult *sr;struct genbankCds cds;char **row;sqlSafef(query, sizeof(query),      "select cds.name "      "from %s.gbCdnaInfo, %s.cds "      "where gbCdnaInfo.acc=/"%s/" and gbCdnaInfo.cds=cds.id",      database, database, mi->gbAcc);sr = sqlMustGetResult(conn, query);row = sqlNextRow(sr);if ((row == NULL) || !genbankCdsParse(row[0], &cds))    ZeroVar(&cds);  /* can't get or parse cds */sqlFreeResult(&sr);return cds;}

void loadBed6(struct track *tg)/* Load the items in one custom track - just move beds in * window... */{struct bed *bed, *list = NULL;struct sqlConnection *conn = hAllocConn(database);struct sqlResult *sr;char **row;int rowOffset;sr = hRangeQuery(conn, tg->table, chromName, winStart, winEnd, NULL, &rowOffset);while ((row = sqlNextRow(sr)) != NULL)    {    bed = bedLoadN(row+rowOffset, 6);    slAddHead(&list, bed);    }sqlFreeResult(&sr);hFreeConn(&conn);slReverse(&list);tg->items = list;}

static void altGraphXLoadItemsPack(struct track *tg)/* load the altGraphX data to a track and caclulate full height. */{struct sqlConnection *conn = hAllocConn(database);int rowOffSet;char **row;struct altGraphX *ag=NULL, *agList=NULL;struct sqlResult *sr = hRangeQuery(conn, tg->table, chromName,				   winStart, winEnd, NULL, &rowOffSet);while((row = sqlNextRow(sr)) != NULL)    {    ag = altGraphXLoad(row + rowOffSet);    slAddHead(&agList, ag);    }slReverse(&agList);tg->items = agList;altGraphXCalcHeight(tg, tg->visibility);sqlFreeResult(&sr);hFreeConn(&conn);tg->items = agList;}

struct gvLink *gvLinkLoadByQuery(struct sqlConnection *conn, char *query)/* Load all gvLink from table that satisfy the query given.   * Where query is of the form 'select * from example where something=something' * or 'select example.* from example, anotherTable where example.something =  * anotherTable.something'. * Dispose of this with gvLinkFreeList(). */{struct gvLink *list = NULL, *el;struct sqlResult *sr;char **row;sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    el = gvLinkLoad(row);    slAddHead(&list, el);    }slReverse(&list);sqlFreeResult(&sr);return list;}

static void bufferRowsFromSqlQuery(struct annoStreamDb *self, char *query, int queryMaxItems)/* Store all rows from query in rowBuf. */{struct sqlResult *sr = sqlGetResult(self->conn, query);struct rowBuf *rowBuf = &(self->rowBuf);rowBufInit(rowBuf, ASD_CHUNK_SIZE);struct annoStreamer *sSelf = &(self->streamer);char **row = NULL;int ix = 0;while ((row = sqlNextRow(sr)) != NULL)    {    if (ix >= rowBuf->size)	errAbort("annoStreamDb %s: rowBuf overflow, got more than %d rows",		 sSelf->name, rowBuf->size);    rowBuf->buf[ix++] = lmCloneRow(rowBuf->lm, row, sSelf->numCols+self->omitBin);    }// Set rowBuf->size to the number of rows we actually stored.rowBuf->size = ix;sqlFreeResult(&sr);updateNextChunkState(self, queryMaxItems);}

struct slName *getChromList(char *db)/* Get list of all chromosomes. */{struct sqlConnection *conn  = hAllocConn(db);struct sqlResult     *sr    = NULL;char                **row   = NULL;struct slName        *list  = NULL;struct slName        *el    = NULL;char                 *query = NOSQLINJ "select chrom from chromInfo";sr = sqlGetResult(conn, query);while ((row=sqlNextRow(sr))!=NULL)    {    el = newSlName(row[0]);    slAddHead(&list, el);    }slReverse(&list);sqlFreeResult(&sr);hFreeConn(&conn);return list;}

void dropDownAdvFilterControls(struct column *col, struct sqlConnection *conn)/* Print out controls for dropdown list filter. */{struct sqlResult *sr;char **row;char query[256];struct slName *list=NULL, *el;sqlSafef(query, sizeof(query),    "select distinct %s from gisaidSubjInfo", col->name);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    char *val = row[0];    if (col->remap)    	val = hashFindVal(col->remap,val);    slNameAddHead(&list, val);    }sqlFreeResult(&sr);slNameSort(&list);hPrintf("choose: ");hPrintf("<SELECT NAME=/"%s/"", "countVarName");hPrintf(">/n");hPrintf("<OPTION VALUE=/"/">");for (el = list; el; el = el->next)    {    hPrintf("<OPTION VALUE=/"%s/"", el->name);    if (sameString(el->name, "displayCountString"))	hPrintf(" SELECTED");    hPrintf(">%s/n", el->name);    }hPrintf("</SELECT>/n");slFreeList(&list);}

void cloneAliPosTab(char *fileName, struct hash *cloneHash)/* Write out clonePos.tab. */{char query[256];struct sqlResult *sr;char **row;struct clonePos *posList = NULL, *pos;struct cloneInfo *info;struct sqlConnection *conn = hAllocConn();sprintf(query, "select * from chr18_frags");sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    struct psl *psl = pslLoad(row);    fragNameToCloneName(psl->qName);    info = findClone(cloneHash, psl->qName);    if ((pos = info->aliPos) == NULL)	{	AllocVar(pos);	pos->info = info;	info->aliPos = pos;	pos->start = psl->tStart;	pos->end = psl->tEnd;	slAddHead(&posList, pos);	}    else	{	if (pos->start > psl->tStart)	    pos->start = psl->tStart;	if (pos->end < psl->tEnd)	    pos->end = psl->tEnd;	}    pslFree(&psl);    }sqlFreeResult(&sr);hFreeConn(&conn);slSort(&posList, cmpClonePos);writePosList(fileName, posList, "chr18");}

void clonePosTab(char *fileName, struct hash *cloneHash)/* Write out clonePos.tab. */{char query[256];struct sqlResult *sr;char **row;struct clonePos *posList = NULL, *pos;struct gl gl;struct cloneInfo *info;struct sqlConnection *conn = hAllocConn();sprintf(query, "select * from chr18_gl");sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    glStaticLoad(row, &gl);    fragNameToCloneName(gl.frag);    info = findClone(cloneHash, gl.frag);    if ((pos = info->pos) == NULL)	{	AllocVar(pos);	pos->info = info;	info->pos = pos;	pos->start = gl.start;	pos->end = gl.end;	slAddHead(&posList, pos);	}    else	{	if (pos->start > gl.start)	    pos->start = gl.start;	if (pos->end < gl.end)	    pos->end = gl.end;	}    }sqlFreeResult(&sr);hFreeConn(&conn);slSort(&posList, cmpClonePos);writePosList(fileName, posList, "chr18");}

static void printPeakClusterInfo(struct trackDb *tdb, struct cart *cart,                                struct sqlConnection *conn, char *inputTrackTable,                                 struct slName *fieldList, struct bed *cluster)/* Print an HTML table showing sources with hits in the cluster, along with signal.   If cluster is NULL, show all sources assayed */{/* Make the SQL query to get the table and all other fields we want to show * from inputTrackTable. */struct dyString *query = dyStringNew(0);queryInputTrackTable(query, inputTrackTable, fieldList);char *vocabFile = NULL;struct hash *vocabHash = NULL;getVocab(tdb, cart, &vocabFile, &vocabHash);int displayNo = 0;int fieldCount = slCount(fieldList);struct sqlResult *sr = sqlGetResult(conn, query->string);char **row;while ((row = sqlNextRow(sr)) != NULL)    {    double signal = 0;    if (cluster != NULL)        {        char *table = row[0];        signal = getSignalAt(table, cluster);        if (signal == 0)            continue;        }    printf("</TR><TR>/n");    webPrintIntCell(++displayNo);    if (signal != 0)	webPrintDoubleCell(signal);    printControlledVocabFields(row+1, fieldCount, fieldList, vocabFile, vocabHash);    printMetadataForTable(row[0]);    }sqlFreeResult(&sr);freez(&vocabFile);dyStringFree(&query);}

void txCdsGoodBed(char *database, char *outBed, char *outCds)/* txCdsGoodBed - Create positive example training set for SVM. This is based on * the refSeq reviewed genes, but we fragment a certain percentage of them so as  * not to end up with a SVM that *requires* a complete transcript. */{struct sqlConnection *conn = sqlConnect(database);char *refTrack = "refGene";char *statusTable = "refSeqStatus";if (!sqlTableExists(conn, refTrack))    errAbort("table %s doesn't exist in %s", refTrack, database);if (!sqlTableExists(conn, statusTable))    errAbort("table %s doesn't exist in %s", statusTable, database);FILE *fBed = mustOpen(outBed, "w");FILE *fCds = mustOpen(outCds, "w");char *query =   "NOSQLINJ select name,chrom,strand,txStart,txEnd,cdsStart,cdsEnd,exonCount,exonStarts,exonEnds "   "from refGene r,refSeqStatus s where r.name=s.mrnaAcc and s.status='Reviewed'";struct sqlResult *sr = sqlGetResult(conn, query);char **row;double randScale = 1.0/RAND_MAX;int id = 0;while ((row = sqlNextRow(sr)) != NULL)    {    struct genePred *gp = genePredLoad(row);    int start = gp->txStart, end = gp->txEnd;    char *type = "refReviewed";    if (rand()*randScale < frag)        {	double midRatio = rand()*randScale;	if (midRatio > 0.5)	     gpFragLimits(gp, 0, midRatio, &start, &end);	else	     gpFragLimits(gp, midRatio, 1.0, &start, &end);	type = "refFrag";	}    gpPartOutAsBed(gp, start, end, fBed, type, ++id, 0);    gpPartOutAsCds(gp, start, end, fCds, type, id);    }carefulClose(&fBed);}

struct targetPcrServer *getTargetServerList(char *db, char *name)/* Get list of available non-genomic-assembly target pcr servers associated * with db (and name, if not NULL).  There may be none -- that's fine. */{    struct targetPcrServer *serverList = NULL, *server;    struct sqlConnection *conn = hConnectCentral();    struct sqlConnection *conn2 = hAllocConn(db);    struct sqlResult *sr;    char **row;    struct dyString *dy = dyStringNew(0);    sqlDyStringPrintf(dy,                      "select b.host, b.port, t.* from targetDb as t, blatServers as b "                      "where b.db = t.name and t.db = '%s' and b.canPcr = 1 ",                      db);    if (isNotEmpty(name))        sqlDyStringPrintf(dy, "and t.name = '%s' ", name);    dyStringAppend(dy, "order by t.priority");    sr = sqlGetResult(conn, dy->string);    while ((row = sqlNextRow(sr)) != NULL)    {        /* Keep this server only if its timestamp is newer than the tables         * and file on which it depends. */        struct targetDb *target = targetDbMaybeLoad(conn2, row+2);        if (target != NULL)        {            AllocVar(server);            server->host = cloneString(row[0]);            server->port = cloneString(row[1]);            server->targetDb = target;            slAddHead(&serverList, server);        }    }    dyStringFree(&dy);    sqlFreeResult(&sr);    hDisconnectCentral(&conn);    hFreeConn(&conn2);    slReverse(&serverList);    return serverList;}

struct rbTree *getTrf(struct sqlConnection *conn, char *chrom)/* Return a tree of ranges for simple repeats in chromosome. */{struct rbTree *tree = rbTreeNew(simpleRangeCmp);struct simpleRange *range, *prevRange = NULL;char query[256];struct sqlResult *sr;char **row;sqlSafef(query, sizeof query, "select chromStart,chromEnd from simpleRepeat "               "where chrom = '%s'",	       chrom);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    lmAllocVar(tree->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(tree, prevRange);	prevRange = range;	}    }if (prevRange != NULL)    rbTreeAdd(tree, prevRange);sqlFreeResult(&sr);return tree;}

void getChroms(struct sqlConnection *conn, struct hash **retHash,	       struct chrom **retList)/* Get hash of chromosomes from database. */{struct sqlResult *sr;char **row;struct chrom *chromList = NULL, *chrom;struct hash *hash = hashNew(8);sr = sqlGetResult(conn, "NOSQLINJ select chrom,size from chromInfo");while ((row = sqlNextRow(sr)) != NULL)    {    AllocVar(chrom);    hashAddSaveName(hash, row[0], chrom, &chrom->name);    chrom->size = atoi(row[1]);    slAddHead(&chromList, chrom);    }sqlFreeResult(&sr);slReverse(&chromList);*retHash = hash;*retList = chromList;}

struct slName *readSnps(char *chromName, int start, int end)/* change to save coords of snps */{char query[512];struct sqlConnection *conn = hAllocConn();struct sqlResult *sr;char **row;struct slName *el = NULL;struct slName *ret = NULL;sqlSafef(query, sizeof(query), "select name from snp125 where chrom = '%s' and chromStart >= %d and chromEnd <= %d",                              chromName, start, end);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    el = slNameNew(row[0]);    slAddHead(&ret, el);    }sqlFreeResult(&sr);hFreeConn(&conn);return ret;}

static void getGenePosition(struct sqlConnection *conn)/* Get gene position from database. */{char *table = genomeSetting("knownGene");char query[256];struct sqlResult *sr;char **row;sqlSafef(query, sizeof(query),    "select chrom,txStart,txEnd from %s where name = '%s'"    , table, curGeneId);sr = sqlGetResult(conn, query);row = sqlNextRow(sr);if (row != NULL)    {    curGeneChrom = cloneString(row[0]);    curGeneStart = atoi(row[1]);    curGeneEnd = atoi(row[2]);    }else    hUserAbort("Couldn't find %s in %s.%s", curGeneId, database, table);sqlFreeResult(&sr);}

void check(struct sqlConnection *conn, char *table)/* Check it's as planned. */{char query[256], **row;struct sqlResult *sr;int lastEnd = -1, lastStart = -1, start, end;sqlSafef(query, sizeof query, "select chromStart,chromEnd from %s", table);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    start = atoi(row[0]);    end = atoi(row[1]);    if (start < lastStart)        fprintf(stderr,"Out of order: %d,%d/n", lastStart, start);    if (rangeIntersection(lastStart, lastEnd, start-1, end) > 0)        fprintf(stderr,"Overlapping: (%d %d) (%d %d)/n", lastStart, lastEnd, start, end);    lastStart = start;    lastEnd = end;    }sqlFreeResult(&sr);errAbort("All for now");}

/* Copied from hgLoadWiggle. */static struct hash *loadAllChromInfo()/* Load up all chromosome infos. */{struct chromInfo *el;struct sqlConnection *conn = sqlConnect(database);struct sqlResult *sr = NULL;struct hash *ret;char **row;ret = newHash(0);sr = sqlGetResult(conn, "NOSQLINJ select * from chromInfo");while ((row = sqlNextRow(sr)) != NULL)    {    el = chromInfoLoad(row);    verbose(4, "Add hash %s value %u (%#lx)/n", el->chrom, el->size, (unsigned long)&el->size);    hashAdd(ret, el->chrom, (void *)(& el->size));    }sqlFreeResult(&sr);sqlDisconnect(&conn);return ret;}

void addSimple(char *id, char *table, char *idField, char *valField, 	struct sqlConnection *conn, struct hash *uniqHash, FILE *f)/* Append all values associated with ID in table to line of file. */{char query[256], **row;struct sqlResult *sr;fprintf(f, "/t");sqlSafef(query, sizeof(query), "select %s from %s where %s='%s'",     valField, table, idField, id);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    char *val = row[0];    if (!hashLookup(uniqHash, val))        {	hashAdd(uniqHash, val, NULL);	fprintf(f, "%s ", val);	}    }sqlFreeResult(&sr);}

static struct genePred *queryNext(struct genePredReader* gpr)/* read the next record from a query */{int iFld, iCol;char **row = sqlNextRow(gpr->sr);char *reorderedRow[GENEPREDX_NUM_COLS];if (row == NULL)    return NULL;/* fill in row defaults */for (iFld = 0; iFld < GENEPREDX_NUM_COLS; iFld++)    reorderedRow[iFld] = fieldTbl[iFld].defaultVal;/* reorder row */for (iCol = 0; iCol < gpr->queryCols; iCol++)    {    iFld = gpr->queryToFldMap[iCol];    if (iFld >= 0)        reorderedRow[iFld] = row[iCol];    }return genePredExtLoad(reorderedRow, gpr->numFields);}

struct chromPos *snpIndexLookup(struct sqlConnection *conn, char *table, char *query, char *name)/* Lookup the marker name in the snp table using the name index. * As the snp table grew beyond 150 million rows,  * loading the entire table into a hash from the database became too slow. */{static struct chromPos pos;char fullQuery[256];sqlSafef(fullQuery, sizeof fullQuery, query, table);sqlSafefAppend(fullQuery, sizeof fullQuery, " where name='%s'", name);struct sqlResult *sr;char **row;sr = sqlGetResult(conn, fullQuery);if ((row = sqlNextRow(sr)) != NULL)    {    pos.chrom = cloneString(row[0]);    pos.pos = sqlUnsigned(row[1]);    }sqlFreeResult(&sr);if (!row)    return NULL;return &pos;}

int tetCountInChrom(struct sqlConnection *conn, char *chrom)/* Number of tets in chromosome. */{char query[512];int tetCount = 0;int lastPos = -1000000;struct sqlResult *sr;char **row;int chromPos;sqlSafef(query, sizeof query, "select chromStart from %s_tet_waba", chrom);sr = sqlGetResult(conn, query);while ((row = sqlNextRow(sr)) != NULL)    {    chromPos = sqlUnsigned(row[0]);    if (chromPos - lastPos > 1000)        ++tetCount;    lastPos = chromPos;    }sqlFreeResult(&sr);return tetCount;}

struct hash *makeOtherHash(char *database, char *table)/* Make otherSeq valued hash of other sequences */{    char query[256];    struct hash *hash = newHash(7);    struct sqlConnection *conn = hAllocConn(database);    struct sqlResult *sr;    char **row;    struct otherSeq *os;    sprintf(query, "select chrom,fileName from %s", table);    sr = sqlGetResult(conn, query);    while ((row = sqlNextRow(sr)) != NULL)    {        AllocVar(os);        hashAddSaveName(hash, row[0], os, &os->name);        os->nibFile = cloneString(row[1]);        nibOpenVerify(os->nibFile, &os->f, &os->chromSize);    }    hFreeConn(&conn);    return hash;}