>>>>> "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> ;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> 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
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> 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.