Menu

#95 Using odbc interface getting strange answers

Performance problem
closed-out-of-date
nobody
None
5
2013-08-07
2004-07-28
Anonymous
No

email : ttr0ot@aber.ac.uk

I have managed to install xsb with its odbc interface
on linux but strange stuff happen when i use it ...
(I have a db on mysql 3.23.54a with serveral tables i
am rtunning xsb with unixodbc 2.2.3 and MyODBC 3.51.x)
the program is at the end of the mail
@littlewolf xsb]$ xsb
[xsb_configuration loaded]
[sysinitrc loaded]
[packaging loaded]
[siteinitrc loaded]

XSB Version 2.6 (Duff) of June 24, 2003
[i686-pc-linux-gnu; mode: optimal; engine: slg-wam; gc:
indirection; scheduling: local]

| ?- [petittest].
[petittest loaded]

yes
| ?- function('YIL001w',Y),chromosome('YIL001w',Z).
SELECT rel1.orf , rel1.function FROM Orf_Function rel1
WHERE rel1.orf = ?;
SELECT rel1.name , rel1.chromosome FROM Orf rel1 WHERE
rel1.name = ?;

Y = mfc99
Z = 9

yes
| ?- findall([X,Y],(function(Z,Y),chromosome(Z,X)),List).
SELECT rel1.orf , rel1.function FROM Orf_Function rel1;

X = _h69
Y = _h83
Z = _h113
List =
[[17,mfc99],[17,mfc02_13],[17,mfc11_04_03_01],[17,mfc38],[17,mfc01_03_16],[17,mfc11_04_03_01],[17,mfc38],[17,mfc01_03_16],[17,mfc11_04_03_01],[17,mfc11_04_03_01],[17,mfc11_04_03_01],[17,mfc02_13],[17,mfc20_03_22],[17,mfc20_09_04],[17,mfc34_01_01_03],[17,mfc02_13],[17,mfc20_03_22],[17,mfc20_09_04],[17,mfc34_01_01_03],[17,mfc02_13],[17,mfc11_04_03_01],[17,mfc11_04_03_01],[17,mfc11_04_03_01],[17,mfc02_13],[17,mfc20_03_22],[17,mfc20_09_04],[17,mfc34_01_01_03],[17,mfc12_01],[17,mfc42_16],[17,mfc11_04_03_01],[17,mfc02_13],[17,mfc11_04_03_01],[17,mfc42_16],[17,mfc02_13]]?

no
| ?-

petittest.P
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
:- import odbc_open/3 from odbc_call.
:- odbc_open('Myblid','******','******').
:- import odbc_import/2 from odbc_call.
:- odbc_import('Orf'('name','chromosome'),
odbc_chromosome).
:- odbc_import('Orf_Function'('orf','function'), function).
atom_number(T, V) :- atom(T), atom_codes(T,C),
number_codes(V,C).
chromosome(ORF, CHR) :-
odbc_chromosome(ORF, Chromo),atom_number(Chromo,CHR).
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
I this kind of strange stuff only appear when using
variable that are bounded in other predicate like in :
function(Z,Y),chromosome(Z,X) %query [A]
because when just asking for
findall(F,function(X,F),L).
I have a very long list (and it was the kind of stuff
awaited)
Do you have any advice clue to be able to ask the query
[A] ?

Thanks in advance
Thomas

Discussion

  • David S. Warren

    David S. Warren - 2004-07-28

    Logged In: YES
    user_id=13069

    So the problem is that the first call to your query returned
    the answer pair <9,mfc99>. Then the second call, which
    was more general (in the findall), didn't include that answer,
    but had <17,mfc99>. Notice that you have a cut (at top
    level, introduced by the interpreter when you hit <return>
    and not ; <return>) after the first query. The odbc interface
    tries to handle cuts over calls to the database, but it may not
    do it right. The first thing I'd try is to but the first call in a
    findall as well. Then the query would be completely
    evaluated and all answers returned, and there wouldn't be a
    cut over an uncompleted odbc call.

    The other thing that looks strange, but I don't know your
    data so can't really tell, is that the X value in all pairs
    returned by the second call is the same: 17. I haven't seen
    that kind of "stuck" error before (assuming there should be
    more different X's.) Also I notice that th SELECT query
    generated for the second query's call to chromosome is not
    printed out. That suggests it had been generated before, or
    the system is using one gererated before that is incorrect.
    But I don't see how/why.

    We use the ODBC interface (somewhat modified since the
    last release, admittedly), but we tend to use it at a lower
    level. That is we use odbc_sql/3 (or 4) calls. Then you have
    to know the mode of the call (what's bound) and write your
    sql query yourself. We haven't had problems with the
    interface at that level.

    -David

     
  • Nobody/Anonymous

    Logged In: NO

    ttr0ot@aber.ac.uk
    I have found a better example to show you the problem (i
    think it is linked to unification or something like that )
    I have a predicate seq that represents a table (name,seq)
    with name the primary key of the table
    if i call this query:
    ?- seq(X,Y),seq(X,Z).
    the answer should be something following the pattern
    X= sthg
    Y=seq1
    Z=seq1
    i have checked that name is really the primary key and run
    this query
    in the table i have somthing like :
    Q0010 |
    MYYIMFLYNMLLIIILIFYSIVGVPIIIFNNNYYWDPDIFLFIIYYFIKFIIIFNLYLYYMINYIVYTPSGSPPGRGTYILLYNMLYSYNMFIDYVMKFITCVTYMYLMFWLLSPTPSPYYVSEVPVS

    Q0045 |
    MVQRWLYSTNAKDIAVLYFMLAIFSGMAGTAMSLIIRLELAAPGSQYLHGNSQLFNVLVVGHAVLMIFFLVMPALIGGFGNYLLPLMIGATDTAFPRINNIAFWVLPMGLVCLVTSTLVESGAGTGWTVYPPLSSIQAHSGPSVDLAIFALHLTSISSLLGAINFIVTTLNMRTNGMTMHKLPLFVWSIFITAFLLLLSLPVLSAGITMLLLDRNFNTSFFEVSGGGDPILYEHLFWFFGHPEVYILIIPGFGIISHVVSTYSKKPVFGEISMVYAMASIGLLGFLVWSHHMYIVGLDADTRAYFTSATMIIAIPTGIKIFSWLATIHGGSIRLATPMLYAIAFLFLFTMGGLTGVALANASLDVAFHDTYYVVGHFHYVLSMGAIFSLFAGYYYWSPQILGLNYNEKLAQIQFWLIFIGANVIFFPMHFLGINGMPRRIPDYPDAFAGWNYVASIGSFIATLSLFLFIYILYDQLVNGLNNKVNNKSVIYNKAPDFVESNTIFNLNTVKSSSIEFLLTSPPAVHSFNTPAVQS
    ...........
    Q0080 | MPQLVPFYFMNQLTYGFLLMITLLILFSQFFLPMILRLYVSRLFISKL
    and the answer is:
    ?- seq(X,L1),seq(X,L2).

    X = Q0010
    L1 =
    MYYIMFLYNMLLIIILIFYSIVGVPIIIFNNNYYWDPDIFLFIIYYFIKFIIIFNLYLYYMINYIVYTPSGSPPGRGTYILLYNMLYSYNMFIDYVMKFITCVTYMYLMFWLLSPTPSPYYVSEVPVS
    L2 =
    MYYIMFLYNMLLIIILIFYSIVGVPIIIFNNNYYWDPDIFLFIIYYFIKFIIIFNLYLYYMINYIVYTPSGSPPGRGTYILLYNMLYSYNMFIDYVMKFITCVTYMYLMFWLLSPTPSPYYVSEVPVS?
    /**here it is ok**/
    X = Q0045
    L1 =
    MVQRWLYSTNAKDIAVLYFMLAIFSGMAGTAMSLIIRLELAAPGSQYLHGNSQLFNVLVVGHAVLMIFFLVMPALIGGFGNYLLPLMIGATDTAFPRINNIAFWVLPMGLVCLVTSTLVESGAGTGWTVYPPLSSIQAHSGPSVDLAIFALHLTSISSLLGAINFIVTTLNMRTNGMTMHKLPLFVWSIFITAFLLLLSLPVLSAGITMLLLDRNFNTSFFEVSGGGDPILYEHLFWFFGHPEVYILIIPGFGIISHVVSTYSKKPVFGEISMVYAMASIGLLGFLVWSHHMYIVGLDADTRAYFTSATMIIAIPTGIKIFSWLATIHGGSIRLATPMLYAIAFLFLFTMGGLTGVALANASLDVAFHDTYYVVGHFHYVLSMGAIFSLFAGYYYWSPQILGLNYNEKLAQIQFWLIFIGANVIFFPMHFLGINGMPRRIPDYPDAFAGWNYVASIGSFIATLSLFLFIYILYDQLVNGLNNKVNNKSVIYNKAPDFVESNTIFNLNTVKSSSIEFLLTSPPAVHSFNTPAVQS
    L2 =
    MVQRWLYSTNAKDIAVLYFMLAIFSGMAGTAMSLIIRLELAAPGSQYLHGNSQLFNVLVVGHAVLMIFFLVMPALIGGFGNYLLPLMIGATDTAFPRINNIAFWVLPMGLVCLVTSTLVESGAGTGWT?
    /**from here the second one is always a subsequence of the
    first one of size the size of the first try (i have tried to
    delete the first entry size 128 ) and then the cut happen
    at 534th char size of the new first entry...)/

    X = Q0050
    L1 =
    MVQRWLYSTNAKDIAVLYFMLAIFSGMAGTAMSLIIRLELAAPGSQYLHGNSQLFNGAPTSAYISLMRTALVLWIINRYLKHMTNSVGANFTGTMACHKTPMISVGGVKCYMVRLTNFLQVFIRITISSYHLDMVKQVWLFYVEVIRLWFIVLDSTGSVKKMKDTNNTKGNTKSEGSTERGNSGVDRGMVVPNTQMKMRFLNQVRYYSVNNNLKMGKDTNIELSKDTSTSDLLEFEKLVMDNMNEENMNNNLLSIMKNVDMLMLAYNRIKSKPGNMTPGTTLETLDGMNMMYLNKLSNELGTGKFKFKPMRMVNIPKPKGGMRPLSVGNPRDKIVQEVMRMILDTIFDKKMSTHSHGFRKNMSCQTAIWEVRNMFGGSNWFIEVDLKKCFDTISHDLIIKELKRYISDKGFIDLVYKLLRAGYIDEKGTYHKPMLGLPQGSLISPILCNIVMTLVDNWLEDYINLYNKGKVKKQHPTYKKLSRMIAKAKMFSTRLKLHKERAKGPTFIYNDPNFKRMKYVRYADDILIGVLGSKNDCKMIKRDLNNFLNSLGLTMNEEKTLITCATETPARFLGYNISITPLKRMPTVTKTIRGKTIRSRNTTRPIINAPIRDIINKLATNGYCKHNKNGRMGVPTRVGRWTYEEPRTIINNYKALGRGILNYYKLATNYKRLRERIYYVLYYSCVLTLASKYRLKTMSKTIKKFGYNLNIIENDKLIANFPRNTFDNIKKIENHGMFMYMSEAKVTDPFEYIDSIKYMLPTAKANFNKPCSICNSTIDVEMHHVKQLHRGMLKATKDYITGRMITMNRKQIPLCKQCHIKTHKNKFKNMGPGM
    L2 =
    MVQRWLYSTNAKDIAVLYFMLAIFSGMAGTAMSLIIRLELAAPGSQYLHGNSQLFNGAPTSAYISLMRTALVLWIINRYLKHMTNSVGANFTGTMACHKTPMISVGGVKCYMVRLTNFLQVFIRITIS?
    /** but for q0080 it is ok **/
    So it is more something like size problem when reading data
    the type of the field in mysql is blob

    for the moment it is my first problem
    and sorry to bother you again

     
  • David S. Warren

    David S. Warren - 2004-07-28

    Logged In: YES
    user_id=13069

    So it looks like it is evaluating a query of the form:
    select X from T where Y=?
    multiple times (this is the form of the query for the second
    call to seq(X,L2).) And it looks like it is keeping the buffer
    size from the first time it is called. This is causing it to
    truncate the answers returned from the subsequent calls. I'll
    look to see how the length of that buffer is obtained on the
    subsequent calls and see if anything looks suspicious.
    -David

     
  • Michael Kifer

    Michael Kifer - 2013-08-07
    • status: open --> closed-out-of-date
    • Group: --> Performance problem
     

Log in to post a comment.