int main(int argc, char **argv)
{
void *value = NULL;
ajulong length = 0;
ajulong i = 0;
ajuint j = 0;
AjEnum client = ajESqlClientMySQL;
AjPFile outf = NULL;
AjPStr user = NULL;
AjPStr password = NULL;
AjPStr host = NULL;
AjPStr port = NULL;
AjPStr socket = NULL;
AjPStr dbname = NULL;
AjPStr statement = NULL;
AjPStr result = NULL;
AjPSqlconnection sqlc = NULL;
AjPSqlstatement sqls = NULL;
AjISqlrow sqli = NULL;
AjPSqlrow row = NULL;
embInit("sqltest", argc, argv);
user = ajStrNewC("anonymous");
password = ajStrNew();
host = ajStrNewC("ensembldb.ensembl.org");
port = ajStrNewC("3306");
socket = ajStrNew();
dbname = ajStrNew();
statement = ajStrNewC("SHOW PROCESSLIST");
result = ajStrNew();
outf = ajAcdGetOutfile("outfile");
if(!ajSqlInit())
ajFatal("Library initialisation failed.");
sqlc =
ajSqlconnectionNewData(client, user, password, host, port, socket,
dbname);
if(!sqlc)
ajFatal("Could not connect as user '%S' to server '%S' at port '%S' "
"to database '%S'", user, host, port, dbname);
ajFmtPrintF(outf, "Connection to client %d\n",
ajSqlconnectionGetClient(sqlc));
sqls = ajSqlstatementNewRun(sqlc, statement);
if(!sqls)
ajFatal("SQL Statement did not complete: %S", statement);
ajFmtPrintF(outf,
"SQL Statement: %S\n",
statement);
ajFmtPrintF(outf,
"Number of selected rows: %Lu\n",
ajSqlstatementGetSelectedrows(sqls));
ajFmtPrintF(outf,
"Number of columns: %u\n",
ajSqlstatementGetColumns(sqls));
sqli = ajSqlrowiterNew(sqls);
if(!sqli)
ajFatal("Query Iterator not defined.");
while(!ajSqlrowiterDone(sqli))
{
row = ajSqlrowiterGet(sqli);
if(row)
{
ajDebug("main SQL Row %Lu Columns %u Current %u\n",
i, row->Columns, row->Current);
for(j = 0; j < ajSqlstatementGetColumns(sqls); j++)
{
ajStrAssignClear(&result);
if(ajSqlcolumnNumberGetValue(row, j, &value, &length))
ajDebug("main SQL Row %Lu Column %u Value %p Length %d\n",
i, j, value, length);
ajSqlcolumnNumberToStr(row, j, &result);
ajDebug("main SQL Row %Lu Column %u String '%S'\n",
i, j, result);
ajFmtPrintF(outf, "Row: %Lu Column: %u String: '%S'\n", i, j,
result);
}
}
else
ajDebug("main No row returned!\n");
i++;
}
ajSqlrowiterDel(&sqli);
ajSqlstatementDel(&sqls);
ajSqlconnectionDel(&sqlc);
ajFileClose(&outf);
ajStrDel(&user);
ajStrDel(&password);
ajStrDel(&host);
ajStrDel(&port);
ajStrDel(&socket);
ajStrDel(&dbname);
ajStrDel(&statement);
ajStrDel(&result);
embExit();
return 0;
}
static AjBool karyotypeBandadaptorFetchAllBySQL(EnsPDatabaseadaptor dba,
const AjPStr statement,
EnsPAssemblymapper am,
EnsPSlice slice,
AjPList kblist)
{
ajuint identifier = 0;
ajuint srid = 0;
ajuint srstart = 0;
ajuint srend = 0;
AjPStr name = NULL;
AjPStr stain = NULL;
AjPSqlstatement sqls = NULL;
AjISqlrow sqli = NULL;
AjPSqlrow sqlr = NULL;
EnsPCoordsystemadaptor csa = NULL;
EnsPFeature feature = NULL;
EnsPKaryotypeband kb = NULL;
EnsPKaryotypebandadaptor kba = NULL;
EnsPSlice srslice = NULL;
EnsPSliceadaptor sa = NULL;
if(!dba)
return ajFalse;
if(!statement)
return ajFalse;
(void) am;
(void) slice;
if(!kblist)
return ajFalse;
csa = ensRegistryGetCoordsystemadaptor(dba);
/*
** AJB: The variable kba, set below, is not used elsewhere
** in the function. Needs fixing appropriately.
*/
kba = ensRegistryGetKaryotypebandadaptor(dba);
sa = ensRegistryGetSliceadaptor(dba);
sqls = ensDatabaseadaptorSqlstatementNew(dba, statement);
sqli = ajSqlrowiterNew(sqls);
while(!ajSqlrowiterDone(sqli))
{
identifier = 0;
srid = 0;
srstart = 0;
srend = 0;
name = ajStrNew();
stain = ajStrNew();
sqlr = ajSqlrowiterGet(sqli);
ajSqlcolumnToUint(sqlr, &identifier);
ajSqlcolumnToUint(sqlr, &srid);
ajSqlcolumnToUint(sqlr, &srstart);
ajSqlcolumnToUint(sqlr, &srend);
ajSqlcolumnToStr(sqlr, &name);
ajSqlcolumnToStr(sqlr, &stain);
/* Need to get the internal Ensembl Sequence Region identifier. */
srid = ensCoordsystemadaptorGetInternalSeqregionIdentifier(csa, srid);
/*
** FIXME: karyotype.seq_region_start and karyotype.seq_region_end are
** defined as signed in the tables.sql file.
** All other features use unsigned start and end coordinates.
*/
ensSliceadaptorFetchBySeqregionIdentifier(sa, srid, 0, 0, 0, &srslice);
feature = ensFeatureNewS((EnsPAnalysis) NULL,
srslice,
srstart,
srend,
1);
kb = ensKaryotypebandNew(identifier, feature, name, stain);
ajListPushAppend(kblist, (void *) kb);
ensFeatureDel(&feature);
ensSliceDel(&srslice);
ajStrDel(&name);
ajStrDel(&stain);
}
ajSqlrowiterDel(&sqli);
ajSqlstatementDel(&sqls);
return ajTrue;
}
static AjBool assemblyexceptionadaptorFetchAllBySQL(
EnsPAssemblyexceptionadaptor aea,
const AjPStr statement,
AjPList aes)
{
ajint ori = 0;
ajuint identifier = 0;
ajuint erid = 0;
ajuint srid = 0;
ajuint erstart = 0;
ajuint srstart = 0;
ajuint erend = 0;
ajuint srend = 0;
AjPSqlstatement sqls = NULL;
AjISqlrow sqli = NULL;
AjPSqlrow sqlr = NULL;
AjPStr typestr = NULL;
EnsEAssemblyexceptionType type = ensEAssemblyexceptionTypeNULL;
EnsPAssemblyexception ae = NULL;
if(!aea)
return ajFalse;
if(!statement)
return ajFalse;
if(!aes)
return ajFalse;
sqls = ensDatabaseadaptorSqlstatementNew(aea->Adaptor, statement);
sqli = ajSqlrowiterNew(sqls);
while(!ajSqlrowiterDone(sqli))
{
identifier = 0;
srid = 0;
srstart = 0;
srend = 0;
typestr = ajStrNew();
erid = 0;
erstart = 0;
erend = 0;
ori = 0;
type = ensEAssemblyexceptionTypeNULL;
sqlr = ajSqlrowiterGet(sqli);
ajSqlcolumnToUint(sqlr, &identifier);
ajSqlcolumnToUint(sqlr, &srid);
ajSqlcolumnToUint(sqlr, &srstart);
ajSqlcolumnToUint(sqlr, &srend);
ajSqlcolumnToStr(sqlr, &typestr);
ajSqlcolumnToUint(sqlr, &erid);
ajSqlcolumnToUint(sqlr, &erstart);
ajSqlcolumnToUint(sqlr, &erend);
ajSqlcolumnToInt(sqlr, &ori);
/* Set the Assembly Exception type. */
type = ensAssemblyexceptionTypeFromStr(typestr);
if(!type)
ajFatal("assemblyexceptionadaptorFetchAllBySQL "
"got unexpected Assembly Exception type '%S' "
"from database.\n",
typestr);
ae = ensAssemblyexceptionNew(aea,
identifier,
srid,
srstart,
srend,
erid,
erstart,
erend,
ori,
type);
ajListPushAppend(aes, (void *) ae);
ajStrDel(&typestr);
}
ajSqlrowiterDel(&sqli);
ensDatabaseadaptorSqlstatementDel(aea->Adaptor, &sqls);
return ajTrue;
}