Odbc_fetch row number

Help!
1

I’m using: SWI-Prolog version 7.6.4

I want the code to: return row number of SQL query

But what I’m getting is: the length of first records in the row

My code looks like this:

pre_year_exist(Count,DataExist):-
    swritef(S,'SELECT * FROM inventory JOIN pre_felling ON inventory.inv_id = pre_felling.inv_id JOIN input ON inventory.year = input.year WHERE input.prepost LIKE \"pre%\"'),
    prep_check(S,[],row(Count),[],DataExist).

prep_check(SQL, Type, Row,Values,Data):-
    odbc_prepare(myDB, SQL, Type,Q,[fetch(fetch)]),
    odbc_execute(Q,Values,Row),
    check_data(Q,Data).
check_data(Q,L):-
        odbc_fetch(Q, Row, []),
        (   Row == end_of_file
        ->  true
        ;
            Row =..[row |Data],
            length(Data, L),
            (
              L >= 1
              ->
                 writeln(L),
                 writeln(' rows in set')
              ;
              L =:= 0
              ->
                 writeln('Empty set')

            )

       ).
2

odbc_execute/2 is a non-deterministic predicate, returning one row of the result set each time on backtracking. If you want to have a number 1… along with these rows, you can use call_nth/2 as in

   call_nth(odbc_execute(Q,Values,Row), RowNumber),
3

thanks a lot for the reply,
I tried so many times with this

   call_nth(odbc_execute(Q,Values,Row), RowNumber),

I do not know how to implement this …

finally … I changed the SQL - add on COUNT

pre_year_exist(Count,DataExist):-
    swritef(S,'SELECT COUNT(*) FROM inventory JOIN pre_felling ON inventory.inv_id = pre_felling.inv_id JOIN input ON inventory.year = input.year WHERE input.prepost LIKE \"post%\"'),
    prep_check(S,[],row(Count),[],DataExist).

prep_check(SQL, Type, Row,Values,Data):-
    odbc_prepare(myDB, SQL, Type,Q,[fetch(fetch)]),
    odbc_execute(Q,Values,Row),
    check_data(Q,Data).

check_data(Q,Data):-
        odbc_fetch(Q, Row, []),
        (   Row == end_of_file
        ->  true
        ;
            Row =..[row |Records],
            Records = [Data],
            (
              Data >= 1
              ->
                 writeln(Data),
                 writeln(" rows in set")
              ;
              Data =:= 0
              ->
                 writeln('Empty set')

            )

       ).
4

First of all, if you want the number of rows, indeed you should use COUNT. That avoids a lot of work in the database, interface and Prolog.

But, I don’t think you understand the idea of non-determinism, i.e., a predicate call returning multiple answers on backtracking. The simplest example is

?- member(X, [a,b,c]).
X = a ;
X = b ;
X = c.

odbc_execute works like member/2. If you want to count its solutions, use

?- aggregate_all(count, odbc_execute(...), Count).