Skip to Content.
Sympa Menu

freetds - Re: stored procedure causes core dump

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Steven J. Backus" <backus AT math.utah.edu>
  • To: "TDS Development Group" <freetds AT franklin.oit.unc.edu>
  • Subject: Re: stored procedure causes core dump
  • Date: Sat, 17 Mar 2001 18:29:02 -0700 (MST)


> Looks like you are binding before the fist call to dbresults() or you are
> binding on a non-result query. can you post the snippet between dbfcmd
> and the dbbind()?

Not really. Here's what happens. First we create the table:

dbcmd(db, " create table #phenotype ");
dbcmd(db, " (id int, kid int, kindred smallint, parents int null, ");
dbcmd(db, " mother int null, father int null, study char(1) null, ");
dbcmd(db, " birth_date datetime null, sex char(1) null, age tinyint null,
");
dbcmd(db, " phen tinyint null, doctor_call tinyint null, flink tinyint
null, ");
dbcmd(db, " blood tinyint null) ");

Then the stored procedure:

dbfcmd(db, "exec lab_get_tmp %d, %c", *kind, study);

if(dbsqlexec(db) == FAIL || dbretstatus(db) != 0) {
fprintf(stderr, "Error in labGet for kindred %d\n", *kind);
error = 1;
}

Then we return and give the commands:

dbcmd(db1, "select gel, C.runid, control, allele1, allele2");
dbcmd(db1, " from control C, gel G where C.runid = G.runid and");
dbfcmd(db1, " marker = '%s' and ", mark[i]);
dbfcmd(db1, "G.study = '%c' order by control", study);
dbsqlexec(db1);

if (dbresults(db1) == SUCCEED) {
dbbind(db1, 1, STRINGBIND, (DBINT) 0, (BYTE *)gel); <----core dumbs
here
dbbind(db1, 2, INTBIND, (DBINT) 0, (BYTE *)&run);
dbbind(db1, 3, INTBIND, (DBINT) 0, (BYTE *)&lab);
dbbind(db1, 4, INTBIND, (DBINT) 0, (BYTE *)&all1);
dbbind(db1, 5, INTBIND, (DBINT) 0, (BYTE *)&all2);

call1 = call2 = done = clab = 0;

while (dbnextrow(db1) != NO_MORE_ROWS) {
...

Here's the stored procedure:

create proc lab_get_tmp @kind smallint, @study char(1) as
begin
/* Insert the ids to be in the kindred */

insert #phenotype (id, kindred, kid, study, phen, flink)
select id, kindred, kid, @study, 1, flink
from kindred
where kindred = @kind
/* and inflink = 1 and flink = 1 */

/* Get part of their data from the individual table */

update #phenotype set sex = I.sex, parents = I.parents, birth_date =
I.birth_date,
age = datediff(day, I.birth_date, getdate()) / 365
from #phenotype P, individual I
where P.id = I.id
and kindred = @kind
and study = @study

update #phenotype set age = datediff(day, P.birth_date, death_date) /
365
from #phenotype P, individual I
where P.id = I.id
and kindred = @kind
and study = @study
and dead = 'Y'
and death_date is not null

/* Get the mothers and fathers from the marriage table */

update #phenotype set mother = K1.kid, father = K2.kid
from marriage M, kindred K1,kindred K2, #phenotype P
where K1.kindred = @kind
and K2.kindred = @kind
and mid = parents
and K1.id = wife and K2.id = husband
and P.kindred = @kind and P.study = @study

/* And get the fathers*/
/*
update #phenotype set father = K.kid
from marriage M, kindred K, #phenotype P
where K.kindred = @kind
and mid = parents
and K.id = husband
and P.kindred = @kind
and P.study = @study
*/

/* Set those whose parents aren't inflinked */
/*
update #phenotype set mother = NULL, father = NULL
where mother not in
(select kid from #phenotype where kindred = @kind and study = @study)
*/

/* Get the age of those sampled */

update #phenotype set age = datediff(day, birth_date, collect_date) /
365
from sample S, #phenotype P
where S.id = P.id
and kindred = @kind
and study = @study
and collect_date is not null
and birth_date < collect_date

/* Set blood = "Y" for those that have a blood sample */

update #phenotype set blood = 1
from #phenotype p,sample s
where s.id = p.id
and kindred = @kind
and study = @study
and type = "B"

/* Set the phenotype of those affected (UNCONF means unknown
affected) */

if (@study = 'B')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id
and kindred = @kind
and study = @study
and (upper(cancer_name) like 'OVAR%' or upper(cancer_name)
like 'BREAST%')
and source = 'UNCONF'

update #phenotype set phen = 3
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'OVAR%'
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'BREAST%'
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id
and kindred = @kind
and study = @study
and birth_date < dx_date
and (upper(cancer_name) like 'OVAR%' or upper(cancer_name)
like 'BREAST%')
and dx_date is not null
end

else if (@study = 'A')
begin

update #phenotype set phen = 0
from aneu_track T, #phenotype P
where P.id = T.id and affected = 'U'
and kindred = @kind and study = @study

update #phenotype set phen = 1
from aneu_track T, #phenotype P
where P.id = T.id and affected = 'N'
and kindred = @kind and study = @study

update #phenotype set phen = 2
from aneu_track T, #phenotype P
where P.id = T.id and affected = 'Y'
and kindred = @kind and study = @study

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from aneu_track T, #phenotype p, aneu_phen a
where T.id = p.id
and a.id = p.id
and affected = 'Y'
and kindred = @kind and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'C')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id
and (upper(cancer_name) like 'COLON%' or upper(cancer_name)
like 'RECT%')
and kindred = @kind
and study = @study
and source = 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and (upper(cancer_name) like 'COLON%' or upper(cancer_name)
like 'RECT%')
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id
and (upper(cancer_name) like 'COLON%' or upper(cancer_name)
like 'RECT%')
and kindred = @kind
and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'D')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id and upper(cancer_name) like 'DEP%'
and kindred = @kind
and study = @study
and source = 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'DEP%'
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id and upper(cancer_name) like 'DEP%'
and kindred = @kind
and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'M')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id and upper(cancer_name) like 'MELA%'
and kindred = @kind
and study = @study
and source = 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'MELA%'
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'MELA%'
and kindred = @kind
and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'P')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'PROST%'
and kindred = @kind
and study = @study
and source = 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'PROST%'
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'PROST%'
and kindred = @kind and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'L')
begin

update #phenotype set phen = 0

update #phenotype set phen = 1
from doc_eval_copd d, #phenotype p
where d.id = p.id
and (upper(copdf) = 'N' or upper(copd1) = 'N' or
upper(copd2) = 'N' or upper(copd3) = 'N')
and kindred = @kind
and study = @study

update #phenotype set phen = 2
from doc_eval_copd d, #phenotype p
where d.id = p.id
and (upper(copdf) = 'Y' or upper(copd1) = 'Y' or
upper(copd2) = 'Y' or upper(copd3) = 'Y' )
and kindred = @kind
and study = @study

update #phenotype set phen = 2
from hist_copd h, #phenotype p
where h.id = p.id
and source = "DEATH CERT"
and dx_type = "COPD"

update #phenotype set age = datediff(day, birth_date,
firstdate) / 365
from doc_eval_copd d, #phenotype p
where d.id = p.id
and upper(copdf) = 'Y'
and kindred = @kind
and study = @study
and birth_date < firstdate
end

else if (@study = 'K')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'LEUK%'
and kindred = @kind
and study = @study
and source = 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'LEUK%'
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id
and upper(cancer_name) like 'LEUK%'
and kindred = @kind
and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'N')
begin
update #phenotype set phen = 0
from cancer C, #phenotype P
where P.id = C.id
and (upper(cancer_name) like 'NON%' or upper(cancer_name)
like 'LYMPHOMA%')
and kindred = @kind
and study = @study
and source = 'UNCONF'

update #phenotype set phen = 2
from cancer C, #phenotype P
where P.id = C.id
and (upper(cancer_name) like 'NON%' or upper(cancer_name)
like 'LYMPHOMA%')
and kindred = @kind
and study = @study
and source != 'UNCONF'

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from cancer C, #phenotype P
where P.id = C.id
and (upper(cancer_name) like 'NON%' or upper(cancer_name)
like 'LYMPHOMA%')
and kindred = @kind and study = @study
and dx_date is not null
and birth_date < dx_date
end

else if (@study = 'G')
begin
update #phenotype set phen = 0
from #phenotype P
where kindred = @kind
and study = @study
and id not in (select id from gse_info)

update #phenotype set doctor_call = 1, phen = 2
from gse_info g,#phenotype p
where p.id = g.id
and dx_category = "BX+SERUM"
and g.gse = 'Y'
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 2, phen = 2
from gse_info g,#phenotype p
where p.id = g.id
and dx_category = "BX"
and g.gse = 'Y'
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 3, phen = 2
from gse_info g,#phenotype p
where p.id = g.id
and dx_category = "BX SKIN"
and g.gse = 'Y'
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 4, phen = 2
from gse_info g,#phenotype p
where p.id = g.id
and dx_category = "SERUM"
and g.gse = 'Y'
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 5, phen = 2
from gse_info g,#phenotype p
where p.id = g.id
and dx_category = "GFDHLA"
and (g.gse = 'N' or g.gse is null)
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 6, phen = 1
from gse_info G, #phenotype P
where P.id = G.id
and G.gse = 'N'
and dx_category in ('BX+SERUM', 'BX', 'BX SKIN', 'SERUM')
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 6, phen = 0
from gse_info g,#phenotype p
where p.id = g.id
and g.gse is null
and dx_category is null
and kindred = @kind
and study = @study

update #phenotype set doctor_call = 7, phen = 2
from gse_info g,#phenotype p
where p.id = g.id
and dx_category = "GFD"
and (g.gse = 'N' or g.gse is NULL)
and kindred = @kind
and study = @study

update #phenotype set age = datediff(day, birth_date,
dx_date) / 365
from gse_info G, #phenotype P
where P.id = G.id
and gse = "U"
and kindred = @kind
and study = @study
and dx_date is not null
and birth_date < dx_date
end

/* Really old people are also unknown affected status */

update #phenotype set phen = 0
where birth_date < 'Jan 1 1825' and phen = 1
end
---
Thanks for your time!

Steve




Archive powered by MHonArc 2.6.24.

Top of Page