FreeTDS Development Group

["Steven J. Backus" <backus AT math.utah.edu>]

> 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