List:MySQL ODBC« Previous MessageNext Message »
From:Michael Widenius Date:May 11 1999 7:17am
Subject:client gets complete result-set after SQLExecDirect
View as plain text  
>>>>> "Paul" == Paul Schwarzl <schwarzl@stripped> writes:

Paul> Hi there!
Paul> I do an db-application with VB6 which works ok right now.
Paul> I use the odbc-api to connect to mysql.
Paul> Now I wanted to optimise the the connection between my app an mysqld.

Paul> Everything is very fast except the following:

Paul> ----cut-start------
Paul> ;The connect stuff is alredy done, now I want to make a select on the table

Paul> SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE, 1)
Paul> SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, SQL_CURSOR_STATIC)
Paul> SQLSetStmtOption(hstmt, SQL_CONCURRENCY, SQL_SCCO_READ_ONLY)
Paul> SQLSetStmtOption(hstmt, SQL_MAX_ROWS, 0)
Paul> SQLExecDirect(hstmt, "select * from bigtable", SQL_NTS)

Paul> ;After this the odbc-driver gets the complete result-set over my slow
Paul> network connection
Paul> ;and that, if the result-set is big, takes a long time.



Paul> Call SQLRowCount(gHstmt, RowCount)
Paul> ;Here the app tells the user how many rows are available
Paul> ;(On some other db-systems I have to do an extra select count(*))


Paul> SQLExtendedFetch(gHstmt, SQL_FETCH_FIRST, 1, 1, 0) <> SQL_NO_DATA_FOUND
Paul> ;Here the app-user fetches as many rows as he wants to see. (maybe only 10,
Paul> maybe 100.000...)
Paul> ----cut-end------


Paul> Are there any options (SQLSetStmtOption) that odbc-SQLExtendedFetch fetches
Paul> row by row over the network? (Only those the user need)

Paul> (My app does(should) work with some other db's (oracle,ms-sqlserver,access)
Paul> so I don't wanna use  "limit" in the select statement.)

Paul> thank you
Paul> Paul

Why not use:

SQLSetStmtOption(hstmt, SQL_MAX_ROWS, as-many-rows-as-the-user-needs)

MyODBC (and most other SQL servers) will only fetch the above amount
of rows from the server.

You can also get a little more speed by specifying the 'compress' option
in the MyODBC connect screen!

Paul> P.S:
Paul> Is it possible that in the last releases of myodbc somthing with
Paul> SQLExecDirect has changend?

Could you be more explicit?  I am not aware of any new bugs in the
newest MyODBC release.

Regards,
Monty
Thread
client gets complete result-set after SQLExecDirectPaul Schwarzl6 May
  • client gets complete result-set after SQLExecDirectMichael Widenius11 May
Re: client gets complete result-set after SQLExecDirectCalistra Research Labs9 May