Results 1 to 11 of 11

Thread: Sql Server cannot retrieve data from select

  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Default Sql Server cannot retrieve data from select

    I establish the connection with Sql Server but after the query it's returning IsSelect = True and isActive=True, after executing .first is False and also isValid.
    ....
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("pippuzzo");
    db.setUserName("sa");
    db.setPassword("rama");
    if (db.open())
    {
    QSqlQuery psql("SELECT Nome FROM Anagrafica", db);

    rtn = psql.isSelect();
    rtn = psql.isActive();

    rtn = psql.first();
    rtn = psql.isValid();

    ...................................
    this is the result of tds.log
    pid: 9659:17:14:20.926238 Starting log file for FreeTDS 0.63
    on 2006-09-06 17:14:20 with debug level 99.
    pid: 9659:17:14:20.937529 names for ISO-8859-1: ISO-8859-1
    pid: 9659:17:14:20.937582 names for UTF-8: UTF-8
    pid: 9659:17:14:20.937614 names for UCS-2LE: UCS-2LE
    pid: 9659:17:14:20.937645 names for UCS-2BE: UCS-2BE
    pid: 9659:17:14:20.937675 iconv to convert client-side data to the "UTF-8" character set
    pid: 9659:17:14:20.938385 tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
    pid: 9659:17:14:20.938437 tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
    pid: 9659:17:14:20.938496 Connecting to 192.168.201.130 port 1027, TDS 7.0.
    pid: 9659:17:14:20.949260 tds_put_string converting 5 bytes of "nbmax"
    pid: 9659:17:14:20.949382 tds_put_string wrote 10 bytes
    pid: 9659:17:14:20.949415 tds_put_string converting 2 bytes of "sa"
    pid: 9659:17:14:20.949447 tds_put_string wrote 4 bytes
    pid: 9659:17:14:20.949479 tds_put_string wrote 0 bytes
    pid: 9659:17:14:20.949510 tds_put_string converting 4 bytes of "MSDE"
    pid: 9659:17:14:20.949541 tds_put_string wrote 8 bytes
    pid: 9659:17:14:20.949572 tds_put_string wrote 0 bytes
    pid: 9659:17:14:20.949603 tds_put_string converting 10 bytes of "us_english"
    pid: 9659:17:14:20.949634 tds_put_string wrote 20 bytes
    pid: 9659:17:14:20.949665 tds_put_string converting 5 bytes of "pippo"
    pid: 9659:17:14:20.949697 tds_put_string wrote 10 bytes
    pid: 9659:17:14:20.949777 tds_process_login_tokens()
    pid: 9659:17:14:21.010228 Received header
    .....
    and the result osf the select --->

    pid: 9659:17:14:21.016564 SQLGetFunc QSqlDatabase db = QSqlDatabase::addDatabase("QTDS7");
    db.setDatabaseName(database);
    db.setHostName(host);
    db.setUserName(user);
    db.setPassword(pass);

    setenv("TDSVER", "8.0", 1);
    setenv("TDSPORT", dbport, 1); //! setPort() does not work properlytions: fFunction is 63
    pid: 9659:17:14:21.016595 SQLGetFunctions: fFunction is 18
    pid: 9659:17:14:21.016625 SQLGetFunctions: fFunction is 48
    pid: 9659:17:14:21.016656 SQLGetFunctions: fFunction is 64
    pid: 9659:17:14:21.016686 SQLGetFunctions: fFunction is 19
    pid: 9659:17:14:21.016717 SQLGetFunctions: fFunction is 65
    pid: 9659:17:14:21.016747 SQLGetFunctions: fFunction is 66
    pid: 9659:17:14:21.016778 SQLGetFunctions: fFunction is 67
    pid: 9659:17:14:21.016809 SQLGetFunctions: fFunction is 49
    pid: 9659:17:14:21.016840 SQLGetFunctions: fFunction is 20
    pid: 9659:17:14:21.016870 SQLGetFunctions: fFunction is 1016
    pid: 9659:17:14:21.016901 SQLGetFunctions: fFunction is 50
    pid: 9659:17:14:21.016932 SQLGetFunctions: fFunction is 21
    pid: 9659:17:14:21.016962 SQLGetFunctions: fFunction is 1017
    pid: 9659:17:14:21.016993 SQLGetFunctions: fFunction is 1018
    pid: 9659:17:14:21.017024 SQLGetFunctions: fFunction is 1019
    pid: 9659:17:14:21.017071 SQLGetFunctions: fFunction is 22
    pid: 9659:17:14:21.017102 SQLGetFunctions: fFunction is 1020
    pid: 9659:17:14:21.017132 SQLGetFunctions: fFunction is 51
    pid: 9659:17:14:21.017163 SQLGetFunctions: fFunction is 52
    pid: 9659:17:14:21.017194 SQLGetFunctions: fFunction is 53
    pid: 9659:17:14:21.017224 SQLGetFunctions: fFunction is 70
    pid: 9659:17:14:21.017255 SQLGetFunctions: fFunction is 54
    pid: 9659:17:14:21.017286 SQLGetFunctions: fFunction is 23
    pid: 9659:17:14:21.017316 SQLGetFunctions: fFunction is 1011
    pid: 9659:17:14:21.017674 tds_free_all_results()
    pid: 9659:17:14:21.017714 tds_put_string converting 27 bytes of "SELECT Nome FROM Anagrafica"
    pid: 9659:17:14:21.017748 tds_put_string wrote 54 bytes
    pid: 9659:17:14:21.017779 Sending packet
    0000 01 01 00 3e 00 00 01 00-53 00 45 00 4c 00 45 00 |...>.... S.E.L.E.|
    0010 43 00 54 00 20 00 4e 00-6f 00 6d 00 65 00 20 00 |C.T. .N. o.m.e. .|
    0020 46 00 52 00 4f 00 4d 00-20 00 41 00 6e 00 61 00 |F.R.O.M. .A.n.a.|
    0030 67 00 72 00 61 00 66 00-69 00 63 00 61 00 |g.r.a.f. i.c.a.|

    pid: 9659:17:14:21.020459 Received header
    0000 04 01 00 d0 00 33 01 00- |.....3..|

    pid: 9659:17:14:21.020531 Received packet
    0000 81 01 00 00 00 09 00 ef-28 00 04 4e 00 6f 00 6d |........ (..N.o.m|
    0010 00 65 00 d1 28 00 4d 00-61 00 73 00 73 00 69 00 |.e..(.M. a.s.s.i.|
    0020 6d 00 69 00 6c 00 69 00-61 00 6e 00 6f 00 20 00 |m.i.l.i. a.n.o. .|
    0030 20 00 20 00 20 00 20 00-20 00 20 00 20 00 d1 28 | . . . . . . ..(|
    0040 00 4d 00 6f 00 72 00 65-00 6e 00 6f 00 20 00 20 |.M.o.r.e .n.o. . |
    0050 00 20 00 20 00 20 00 20-00 20 00 20 00 20 00 20 |. . . . . . . . |
    0060 00 20 00 20 00 20 00 20-00 d1 28 00 47 00 69 00 |. . . . ..(.G.i.|
    0070 61 00 6e 00 67 00 69 00-76 00 61 00 20 00 20 00 |a.n.g.i. v.a. . .|
    0080 20 00 20 00 20 00 20 00-20 00 20 00 20 00 20 00 | . . . . . . . .|
    0090 20 00 20 00 d1 28 00 70-00 69 00 70 00 70 00 6f | . ..(.p .i.p.p.o|
    00a0 00 20 00 20 00 20 00 20-00 20 00 20 00 20 00 20 |. . . . . . . . |
    00b0 00 20 00 20 00 20 00 20-00 20 00 20 00 20 00 fd |. . . . . . . ..|
    00c0 10 00 c1 00 04 00 00 00- |........|

    pid: 9659:17:14:21.020644 processing result tokens. marker is 81(TDS7_RESULT)
    pid: 9659:17:14:21.020680 tds_free_all_results()
    pid: 9659:17:14:21.020714 processing TDS7 result. set current_results to tds->res_info
    pid: 9659:17:14:21.020747 adjust_character_column_size:
    Server charset: UCS-2LE
    Server column_size: 40
    Client charset: UTF-8
    Client column_size: 80
    pid: 9659:17:14:21.020779 tds_get_string: reading 8 from wire to give 4 to client.
    pid: 9659:17:14:21.020815 tds7_get_data_info:1558:
    colname = Nome (4 bytes)
    type = 47 (char)
    server's type = 239 (x UCS-2 char)
    column_varint_size = 2
    column_size = 80 (40 on server)
    pid: 9659:17:14:21.020851 processing result tokens. marker is d1(ROW)
    pid: 9659:17:14:26.740946 SQLFreeHandle(3, 0x0x80d17f8)
    pid: 9659:17:14:26.741118 Sending packet
    0000 06 01 00 08 00 00 01 00- |........|

    pid: 9659:17:14:26.750381 tds_process_default_tokens() marker is d1(ROW)
    pid: 9659:17:14:26.750498 processing row. column is 0 varint size = 2
    pid: 9659:17:14:26.750560 processing row. column size is 40
    pid: 9659:17:14:26.750618 clearing column 0 NULL bit
    pid: 9659:17:14:26.750683 tds_process_default_tokens() marker is d1(ROW)
    pid: 9659:17:14:26.750741 processing row. column is 0 varint size = 2
    pid: 9659:17:14:26.750799 processing row. column size is 40
    pid: 9659:17:14:26.750855 clearing column 0 NULL bit
    pid: 9659:17:14:26.750915 tds_process_default_tokens() marker is d1(ROW)
    pid: 9659:17:14:26.750972 processing row. column is 0 varint size = 2
    pid: 9659:17:14:26.751029 processing row. column size is 40
    pid: 9659:17:14:26.751086 clearing column 0 NULL bit
    pid: 9659:17:14:26.751146 tds_process_default_tokens() marker is d1(ROW)
    pid: 9659:17:14:26.751203 processing row. column is 0 varint size = 2
    pid: 9659:17:14:26.751260 processing row. column size is 40
    pid: 9659:17:14:26.751316 clearing column 0 NULL bit
    pid: 9659:17:14:26.751376 tds_process_end: more_results = 0
    was_cancelled = 0
    error = 0
    done_count_valid = 1
    pid: 9659:17:14:26.751435 tds_process_end() state set to TDS_IDLE
    pid: 9659:17:14:26.751491 rows_affected = 4
    pid: 9659:17:14:26.751570 Received header
    0000 04 01 00 11 00 33 01 00- |.....3..|

    pid: 9659:17:14:26.751656 Received packet
    0000 fd 20 00 fd 00 00 00 00-00 |. ...... .|

    pid: 9659:17:14:26.751724 tds_process_end: more_results = 0
    was_cancelled = 1
    error = 0
    done_count_valid = 0
    pid: 9659:17:14:26.751782 tds_process_end() state set to TDS_IDLE

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql Server cannot retrieve data from select

    Quote Originally Posted by Atomino
    after executing .first is False and also isValid.
    The query object initially points to a place before the first record --- you have to invoke QSqlQuery::next() to fetch the data.

    Qt Code:
    1. QSqlQuery psql("SELECT Nome FROM Anagrafica", db);
    2. if( psql.next() ) {
    3. // do something with psql.value( 0 )
    4. }
    5. else {
    6. // empty result set
    7. }
    To copy to clipboard, switch view to plain text mode 

  3. The following user says thank you to jacek for this useful post:

    fnmblot (6th September 2006)

  4. #3
    Join Date
    Sep 2006
    Posts
    10

    Default Re: Sql Server cannot retrieve data from select

    I have tried with....

    if( psql.next() )
    {
    // do something with psql.value( 0 )
    nomeCol = psql.record().indexOf("Nome");
    }
    else
    {
    // empty result set
    }
    .. but always skip to empty result.

  5. #4
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql Server cannot retrieve data from select

    Does that query return any records when you run it from a SQL console as the same user?

  6. #5
    Join Date
    Sep 2006
    Posts
    10

    Default Re: Sql Server cannot retrieve data from select

    I use isql to establish a connection (same user and password) and after the same select and it returned n.4 items...it's working

  7. #6
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql Server cannot retrieve data from select

    What does this output to the console?
    Qt Code:
    1. QSqlQuery psql("SELECT Nome FROM Anagrafica", db);
    2. qDebug( "size=%d", psql.size() );
    To copy to clipboard, switch view to plain text mode 

  8. #7
    Join Date
    Sep 2006
    Posts
    10

    Default Re: Sql Server cannot retrieve data from select

    The console it's returning :

    size=-1

  9. #8
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql Server cannot retrieve data from select

    Are there any error messages on the console? This might a problem with FreeTDS <-> ODBC connectivity. Check with some tool that uses ODBC whether you can retrieve query results.
    Last edited by jacek; 7th September 2006 at 15:56.

  10. #9
    Join Date
    Sep 2006
    Posts
    10

    Default Re: Sql Server cannot retrieve data from select

    This the message :
    Qt: gdb: -nograb added to command-line options.
    Use the -dograb options to enforce grabbing.
    size=-1

    .....no other message.
    If I go to see the logfile from Tds I Can see the result of the select, what I am thinking is that some poiter is not matching and no data returns.
    I am testing evalutions version Qt 4.1.4 that will expired in 30 days...
    do you know if this version can support ODBC connection to Sql Server ?

  11. #10
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Sql Server cannot retrieve data from select

    Quote Originally Posted by Atomino
    If I go to see the logfile from Tds I Can see the result of the select, what I am thinking is that some poiter is not matching and no data returns.
    This means that FreeTDS can talk to your database and that the problem lies between Qt and FreeTDS. If you use unixODBC, there should be a program called DataManager --- check if it can execute queries through ODBC.

    Quote Originally Posted by Atomino
    I am testing evalutions version Qt 4.1.4 that will expired in 30 days...
    do you know if this version can support ODBC connection to Sql Server ?
    I don't use SQL Server, better contact Trolltech for authoritative answer.

  12. #11
    Join Date
    Sep 2006
    Posts
    10

    Default Re: Sql Server cannot retrieve data from select

    DataManager is working fine, I try to contact Trolltech.

    Thank you very much for your attention.

    Greetings.

Similar Threads

  1. Sql Server and PDF files
    By kroenecker in forum General Programming
    Replies: 4
    Last Post: 26th June 2007, 10:50
  2. speed of setdata - lots of items in treeview
    By Big Duck in forum Qt Programming
    Replies: 4
    Last Post: 6th July 2006, 12:53
  3. Replies: 16
    Last Post: 7th March 2006, 15:57
  4. connect to sql server
    By raphaelf in forum Qt Programming
    Replies: 15
    Last Post: 27th February 2006, 18:06
  5. synching client readings to server output
    By OnionRingOfDoom in forum Qt Programming
    Replies: 14
    Last Post: 28th January 2006, 18:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.