Asynchronous Execution in ODBC

[Mike Jones]

Has anyone managed to adapt the VO SQL classes to handle asynchronous
execution?

[Laszlo Istvan [nws]]

Hello,

There are two sides of the ODBC-related problems:
The first is the rewritting of a class to support asyncronous
executios. The SDK-sources being available (VO 2.6) this is possible
but needs a lot of work.

The second and the greatest problem is the wide range of ODBCs
available on the market. Some of them do not support asyncronous
execution. This can seriously limit your success but if you decide to
use only ODBCs supporting asyncronous execution, it is ok.

My solution (you can see Visual Query Tool on www.vqtool.com) is and -
I suggest you too - the implementation of a pseudo-asyncronous
execution: I modified the genuine SQL classes to be bi-threadings.
This mean, that I start the query through ODBC in a second thread. The
main thread does his work (and the user interface remains active) and
the results will be available when the second thread will be
completed.

All the best,
László István.

[Eckhard Sallermann]

Hi Laszlo,

seems great, could you show us the code ?
I also tried to execute the statement as a thread, but i will not be able to
get it run

Ecki

"Laszlo Istvan [nws]" <laszlo...@gmx.net> schrieb im Newsbeitrag
news:4ed6cfc9.03041...@posting.google.com...

[Laszlo Istvan [nws]]

Hi Ecki

Yes and No.
Yes, the code and the principles are available; In VQT the adopted
solution is a 'particular solution', not a general multithreading ODBC
wrapper. (Please feel you free and test-it: in the meantime of a SQL
query execution the avi are running, the progressbar is signaling the
elapsed time and the user interface is partially working = the
application is not dead.)
In this idea I can offer some samples and some guidelines but you must
complete your work alone. Please give me a half of day to collect all
information.

No, you can not run ODBC functions as VO-level methods directly in
another thread, because they are dynamic-data intensive. They only
partially can be transferred in a separated thread. You will see the
samples soon.

Thanks four your patient,
Laszlo.

[Stefano Bonacina]

The solution is: use ClassMate.
Ciao
Stefano Bonacina

"Mike Jones" <mi...@richsoftware.com> ha scritto nel messaggio
news:CGUma.1774$f34.3...@news20.bellglobal.com...

[Mike Jones]

Ditto.

Mike

"Eckhard Sallermann" <eckhard.s...@bur-kg.de> wrote in message
news:b7iv57$18jko$1...@ID-104793.news.dfncis.de...

[Mike Jones]

Stefano,

> The solution is: use ClassMate.

I'm sure it would be if we didn't have a very large software system written
using DataServers to maintain.

Mike

[Laszlo Istvan [nws]]

Hi,

THIS IS A SAMPLE, NOT A FULL SOLUTION. IS SERVES ONLY A GUIDE!
The goal is to weak up the application from sleep in the meantime of
an ODBC Exec. To complete the goal, the following cooking-book is
available

1. Define a structure for inter-thread communication.

structure _sqlExecStru
member hstmt as ptr
member szSqlStr as psz
member cbSqlStr as long
member nResult as short
member lCompleted as logic

2. Define a low-level ODBC-wrapper function capable of
interthread-communication.

function ThreadSQLExecDirect( lpExecStru as _sqlExecStru ) as dword
pascal
lpExecStru.nResult := SQLExecDirect( ;
lpExecStru.hstmt, ;
lpExecStru.szSqlStr, ;
lpExecStru.cbSqlStr ;
)
lpExecStru.lCompleted := true
ExitVOThread(0)

return 0

3. Create a high-level function capable to manage the application and
the parallel thread too.

function MySQLExecDirect(hstmt as ptr, cSqlStr as string, ;
cbSqlStr as long) as shortint pascal
local static lInExec as logic
local lpExecStru as _sqlExecStru
local osps as string
local nResult as short
local hThread as ptr
local nID as dword
local tstart as real4
local estimated as real4
local oShell as VQTShellWindow
local prog as dword

if lInExec
return SQL_ERROR // this is not reentrant
else
lInExec := true
endif

osps := DynToOldSpace( cSqlStr )

lpExecStru := MemAlloc( _sizeof(_sqlExecStru) )
lpExecStru.hstmt := hstmt
lpExecStru.szSqlStr := psz(_cast, osps)
lpExecStru.cbSqlStr := cbSqlStr
lpExecStru.lCompleted := false

//
hThread := CreateVOThread( ;
0, ;
0, ;
@ThreadSQLExecDirect(), ;
lpExecStru, THREAD_TERMINATE, @nID)
if hThread == NULL_PTR
// Error
else
hActiveThread := hThread
oShell := Shell() // returns the ShellWindow object
estimated := oShell:nEstimatedtime
tstart := oShell:tStart
Sleep( 50 ) // do not enter the do-while only if it worths:
do while !lpExecStru.lCompleted
// if the process is short, lCompleted in te meantime was set to
true and
// the do-while is skipped
if estimated != 0
prog := dword( (real4(Seconds()) - tstart)*100 / estimated )
SetProgressBar( prog )
endif
_ExecWhileEvent() // give a time-slice to user
Sleep( 300 ) // free processor
enddo

CloseHandle( hThread )
hActiveThread := NULL_PTR
oShell:nEstimatedtime ;
:= iif( (tstart:=estimated-(Seconds()-tstart))>0, tstart, 0 )
endif

nResult := lpExecStru.nResult
MemFree( lpExecStru )
OldSpaceFree( osps )

_CollectForced()

lInExec := false

return nResult

4. Modify the Execute method of SQLStatement class to work with the
new function

class VQTStatement inherit SQLStatement

method Execute(uParm ) class VQTStatement
local nCount as dword
local aArg as array
local aStatArg as array
local pNullInd as ptr
local nRetCode as int
local l as long
local lRet as logic
local lNewParams as logic

.......
if !self:lPrepFlag
if !self:__SetDefaultStatementOptions()
if !( self:oErrInfo:SQLState = __CavoStr(65450+20) )
self:oConn:ScrollCsr := false
endif
endif

l := long( SLen(self:cStatement) )
if l > 0
nRetCode := MySQLExecDirect( self:hStmt, self:cStatement, l )
endif

else
nRetCode := SQLExecute( self:hStmt )

endif
................

return lRet

5. For a full implementation ALL time-consuming ODBC operations must
be wrapped, for example SQLExtendedFetch and the record-numbering
mechanism too.

If anyone need supplemental help, I will happy to offer directly, by
email. There is not space for detailed discussions.

Laszlo.

[Mike Jones]

Laszlo,

Many thanks for posting this.

Mike

[Mike Jones]

Laszlo,

Thanks for your code fragment, which works well.

I do have one small suggestion: if you replace the calls to Sleep(n) with
calls to WaitForSingleObject(hThread, n), you need not worry that your
Sleeps will show down processing, since WaitForSingleObject will return as
soon as the thread terminates. Also, you no longer need the lCompleted flag
as WaitForSingleObject returns WAIT_TIMEOUT if the thread is still
processing and WAIT_OBJECT_0 if the thread has terminated.

Regards,
Mike

"Laszlo Istvan [nws]" <laszlo...@gmx.net> wrote in message
news:4ed6cfc9.03041...@posting.google.com...

[Laszlo Istvan [nws]]

Hi, Mike

> I do have one small suggestion: if you replace the calls to Sleep(n) with
> calls to WaitForSingleObject(hThread, n), you need not worry that your
> Sleeps will show down processing, since WaitForSingleObject will return as
> soon as the thread terminates. Also, you no longer need the lCompleted flag
> as WaitForSingleObject returns WAIT_TIMEOUT if the thread is still
> processing and WAIT_OBJECT_0 if the thread has terminated.

hank you very much for your interest and suggestion too.

The adopted solution is dependent of your goal.
According to Win32 SDK,

Sleep
"...The Sleep function suspends the execution of the current thread
for a specified interval. ..."

WaitForSingleObject
"...If the object&#8217;s state is nonsignaled, the calling thread
enters an efficient wait state. ..."

Both functions SUSPEND the execution of CURRENT (ODBC related) THREAD.
Sleep() does nothing for the specified time but WaitForSingleObject()
checks the state of the object. => The first does not use CPU time(!),
the second returns as soon as(!) the object enters signaled state.
As a conclusion you can use both solutions. I will use Sleep(),
because this do not use any CPU time and the exact process terminating
in "Visual Query Tool" context does not have importance.

Laszlo.

P.S. Do you know how can I reduce the delay between posting a reply
and the displaying of the reply?

[Mike Jones]

Laszlo,

Fair enough. My priority was that the main process should break out of the
loop as soon as the secondary process terminates, which WaitForSingleObject
will do, and since according to the documentation, it uses an "efficient"
wait state, I don't think much processor time will be taken away from other
threads/processes by using it.

As regards the propagation of your posts, I think that's in the hands of
your service provider or whoever's responsible for your nntp server.

Regards,
Mike