PDA

View Full Version : SQL Table Open Problem



Peter.Tawse
27-Oct-2005, 11:42 PM
Hi All

Let's see if I can explain my problem clearly.


Environment W2K Pro Sp4 _or_ XP Pro SP2
Vdf11.0, with the CK that came with the Dev System

My Application _mainly_ uses dataflex embedded files,
but also connects to 2 MS-SQL Tables on another Windows
2003 Server

The application is a message transmit and/or receive system
and makes use of the Internet OR an "always-on" dedicated PSTN
link to another service provider (rather costly, too) and the uses
content of these 2 SQL tables for Internet messaging purposes
only.

If the internet is DOWN, an alternative SMS Messaging system
comes into use, using Embedded Dataflex files, and I have
successfully implemented this option.

Of course, my App needs to run to determine the current
internet -connection state, and does so as long as I can
connect to the SQL Server

If the SQL server is DOWN for some reason, then I still need my
App to run, but it will use the alternative SMS messaging system

My problem is this:

If the SQK Server is down for some reason, I cannot run my App
as it crashes as soon as it tries to connect to the non-operative
SQL table. This actually occurs on the Open statement within
the SQL-FILE_DD.

Can anyone suggest a way I can TEST if the SQL files can be
opened (and trap the error if it occurs), and if not successfully
opened I can then set some internal program flags to ONLY
use the alternative SMS message system, and totally ignore
the SQL related code and options.

TIA


Regards

Peter Tawse
Microbase Software
Melbourne, Australia
Ph: +61 3 9563-9100
Fax: +61 3 9563-9477
ptawse@internode.on.net

Ben Weijers
30-Oct-2005, 03:12 PM
Peter,

I think you will get an error 75 (Cannot open table) which is considered
fatal by VDF. As soon as this error occurs the program will be aborted (not
crash, there's a difference). What you need to do is create an error handler
that will not consider error 75 as fatal and use that.

Regards,

Ben Weijers
Data Access Worldwide

Peter.Tawse
30-Oct-2005, 06:04 PM
Hi Ben

>I think you will get an error 75 (Cannot open table) which is considered
>fatal by VDF. As soon as this error occurs the program will be aborted (not
>crash, there's a difference). What you need to do is create an error handler
>that will not consider error 75 as fatal and use that.
>
>Regards,
>
> Ben Weijers
> Data Access Worldwide

I understand about that error-handler, and your comments
about the aborting, BUT ...

Since I'm using DD.s, the table is opened within the
..DD file, and - as I understand them - DD's cannot
be edited, except within the scope of DbBldr

Are you suggesting I create an error-handler within
the DD ?

What other alternatives - if any - can you suggest ?

TIA



>

Regards

Peter Tawse
Microbase Software
Melbourne, Australia
Ph: +61 3 9563-9100
Fax: +61 3 9563-9477
ptawse@internode.on.net

Ben Weijers
31-Oct-2005, 05:29 AM
Peter,

You can write your own error handler. It does not need to be part of the DD.
The runtime will send a standard message, Error_Report, to the object which
handle is stored in the Error_Object_ID global variable. To write a custom
error handler that handles error 75 you could use a class like the one
below. Creating an object of this class will make the custom error handler
active, destroying it will restore your original error handler.

Use Errornum.inc

Class cMyCustomErrorHandler Is A cObject

Procedure Construct_Object
Forward Send Construct_Object

//*** Store original error handler
Property Handle phoOrgErrorHandler Error_Object_ID

Move Self To Error_Object_ID
End_Procedure // Construct_Object



Procedure Error_Report Integer iErrNum Integer iErrLine String sErrMsg
If (iErrNum = DFERR_CANT_OPEN_DATA_FILE) Begin
//*** Adjust this to your needs
Send Stop_Box "Could not open the $&^$^$&^ table"
End
Else ;
Send Error_Report Of (phoOrgErrorHandler(Self)) iErrNum iErrLine
sErrMsg
End_Procedure // Error_Report



Procedure Destroy
Move (phoOrgErrorHandler(Self)) To Error_Object_ID
Forward Send Destroy
End_procedure // Destroy

End_Class // cMyCustomerErrorHandler


Regards,

Ben Weijers
Data Access Worldwide

Peter.Tawse
31-Oct-2005, 06:08 AM
Ben

Many thanks - I was investigating where I should start,
but you've given me the "whole box of dice" - almost.

I'd be surprised if I can't handle it from here - but how about
logging a suggestion that the CK should be able to handle
my scenario "out of the box" ? - after all, Vdf does need to
play TOTALLY in this bigger DBMS area, and I must admit
that I've been pleasantly surprised so far - and then I run
into a 'gotcha' like I did.

Again - thanks. You're a mine of info

Regards
Peter

>You can write your own error handler. It does not need to be part of the DD.
>The runtime will send a standard message, Error_Report, to the object which
>handle is stored in the Error_Object_ID global variable. To write a custom
>error handler that handles error 75 you could use a class like the one
>below. Creating an object of this class will make the custom error handler
>active, destroying it will restore your original error handler.
>
>Use Errornum.inc
>
>Class cMyCustomErrorHandler Is A cObject
>
> Procedure Construct_Object
> Forward Send Construct_Object
>
> //*** Store original error handler
> Property Handle phoOrgErrorHandler Error_Object_ID
>
> Move Self To Error_Object_ID
> End_Procedure // Construct_Object
>
>
>
> Procedure Error_Report Integer iErrNum Integer iErrLine String sErrMsg
> If (iErrNum = DFERR_CANT_OPEN_DATA_FILE) Begin
> //*** Adjust this to your needs
> Send Stop_Box "Could not open the $&^$^$&^ table"
> End
> Else ;
> Send Error_Report Of (phoOrgErrorHandler(Self)) iErrNum iErrLine
>sErrMsg
> End_Procedure // Error_Report
>
>
>
> Procedure Destroy
> Move (phoOrgErrorHandler(Self)) To Error_Object_ID
> Forward Send Destroy
> End_procedure // Destroy
>
>End_Class // cMyCustomerErrorHandler
>
>
>Regards,
>
> Ben Weijers
> Data Access Worldwide
>

Regards

Peter Tawse
Microbase Software
Melbourne, Australia
Ph: +61 3 9563-9100
Fax: +61 3 9563-9477
ptawse@internode.on.net

Marco
31-Oct-2005, 06:12 PM
Peter,

Something you can't do (well I can't that is), is to recover from an
error 75. We have several programs that runs 24*7. Offcourse we need to
do maintenance on tables sometimes. As these are always in the middle of
the night, we are doing this on remote. It is not always the easiest to
remote desktop to all these machines to stop the 24*7 apps.

Therefore we researched the option for just closing the connection to
the DBMS Server (db2stop force all), and find a way that the 24*7 apps
would detect the closed file, wait about 5 minuts and then reopen the file.

We found that this is not possible within one app. When an app gets the
error 75, too much is closed in the CK, for a successfull reopen.

We solved it now, by indeed trapping error 75 as Ben suggested. Then we
chain to a small VDF programm called 'CallMeIn5.exe' the program name
that caused the error 75 is forwarded on the command line.

So, a 24*7 app eg EAFS.exe, detects the status 75, does a
chain "CalMeIn5.exe" "EAFS.exe"
abort

The CallMeIn5, displays a count down clock, with a button for 'Now'.
When it is time, it calls EAFS.exe and aborts itself. If the file still
cannot be opened, the same thing happens again, and another 5 minutes
later it will try again.

If you want I can demonstrate this at the Corroboree 2006.

Cheers,
Marco Kuipers
Adelaide, South Australia


Ben Weijers wrote:
> Peter,
>
> You can write your own error handler. It does not need to be part of the DD.
> The runtime will send a standard message, Error_Report, to the object which
> handle is stored in the Error_Object_ID global variable. To write a custom
> error handler that handles error 75 you could use a class like the one
> below. Creating an object of this class will make the custom error handler
> active, destroying it will restore your original error handler.
>
> Use Errornum.inc
>
> Class cMyCustomErrorHandler Is A cObject
>
> Procedure Construct_Object
> Forward Send Construct_Object
>
> //*** Store original error handler
> Property Handle phoOrgErrorHandler Error_Object_ID
>
> Move Self To Error_Object_ID
> End_Procedure // Construct_Object
>
>
>
> Procedure Error_Report Integer iErrNum Integer iErrLine String sErrMsg
> If (iErrNum = DFERR_CANT_OPEN_DATA_FILE) Begin
> //*** Adjust this to your needs
> Send Stop_Box "Could not open the $&^$^$&^ table"
> End
> Else ;
> Send Error_Report Of (phoOrgErrorHandler(Self)) iErrNum iErrLine
> sErrMsg
> End_Procedure // Error_Report
>
>
>
> Procedure Destroy
> Move (phoOrgErrorHandler(Self)) To Error_Object_ID
> Forward Send Destroy
> End_procedure // Destroy
>
> End_Class // cMyCustomerErrorHandler
>
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide
>
>

Peter.Tawse
31-Oct-2005, 08:44 PM
Hi Marco

Thanks for the info.

My problem is somewhat different - at the moment if the SQL server
is NOT up, my program refuses to run - and about 1 min later throws
up an error dialog re the SQL error, and then aborts - so NO messages
are sent at all - no GPRS messages and no alternative SMS messages

The behavior I need is for a flag to be set within the program,
which effectively by-passes any SQL file access code, and thus allows
the program to continue running, but using a different methodology
to send SMS messages instead of the usual GPRS-based messages.

I think with Ben's code I should be able to achieve exactly what I need.

This system, too - could be of interest at the Corroboree

BFN

Peter

>Something you can't do (well I can't that is), is to recover from an
>error 75. We have several programs that runs 24*7. Offcourse we need to
>do maintenance on tables sometimes. As these are always in the middle of
>the night, we are doing this on remote. It is not always the easiest to
>remote desktop to all these machines to stop the 24*7 apps.
>
>Therefore we researched the option for just closing the connection to
>the DBMS Server (db2stop force all), and find a way that the 24*7 apps
>would detect the closed file, wait about 5 minuts and then reopen the file.
>
>We found that this is not possible within one app. When an app gets the
>error 75, too much is closed in the CK, for a successfull reopen.
>
>We solved it now, by indeed trapping error 75 as Ben suggested. Then we
>chain to a small VDF programm called 'CallMeIn5.exe' the program name
>that caused the error 75 is forwarded on the command line.
>
>So, a 24*7 app eg EAFS.exe, detects the status 75, does a
> chain "CalMeIn5.exe" "EAFS.exe"
> abort
>
>The CallMeIn5, displays a count down clock, with a button for 'Now'.
>When it is time, it calls EAFS.exe and aborts itself. If the file still
>cannot be opened, the same thing happens again, and another 5 minutes
>later it will try again.
>
>If you want I can demonstrate this at the Corroboree 2006.
>
>Cheers,
>Marco Kuipers
>Adelaide, South Australia
>
>
>Ben Weijers wrote:
>> Peter,
>>
>> You can write your own error handler. It does not need to be part of the DD.
>> The runtime will send a standard message, Error_Report, to the object which
>> handle is stored in the Error_Object_ID global variable. To write a custom
>> error handler that handles error 75 you could use a class like the one
>> below. Creating an object of this class will make the custom error handler
>> active, destroying it will restore your original error handler.
>>
>> Use Errornum.inc
>>
>> Class cMyCustomErrorHandler Is A cObject
>>
>> Procedure Construct_Object
>> Forward Send Construct_Object
>>
>> //*** Store original error handler
>> Property Handle phoOrgErrorHandler Error_Object_ID
>>
>> Move Self To Error_Object_ID
>> End_Procedure // Construct_Object
>>
>>
>>
>> Procedure Error_Report Integer iErrNum Integer iErrLine String sErrMsg
>> If (iErrNum = DFERR_CANT_OPEN_DATA_FILE) Begin
>> //*** Adjust this to your needs
>> Send Stop_Box "Could not open the $&^$^$&^ table"
>> End
>> Else ;
>> Send Error_Report Of (phoOrgErrorHandler(Self)) iErrNum iErrLine
>> sErrMsg
>> End_Procedure // Error_Report
>>
>>
>>
>> Procedure Destroy
>> Move (phoOrgErrorHandler(Self)) To Error_Object_ID
>> Forward Send Destroy
>> End_procedure // Destroy
>>
>> End_Class // cMyCustomerErrorHandler
>>
>>
>> Regards,
>>
>> Ben Weijers
>> Data Access Worldwide
>>
>>

Regards

Peter Tawse
Microbase Software
Melbourne, Australia
Ph: +61 3 9563-9100
Fax: +61 3 9563-9477
ptawse@internode.on.net

Ben Weijers
1-Nov-2005, 03:37 AM
Peter,

Keep in mind that the CK is a low level thing. If it is unable to open a
table (for whatever reason) I really do not see how it could do anything
else then throw an error. It is up to your application to handle the error.

Regards,

Ben Weijers
Data Access Worldwide

Baz
1-Nov-2005, 05:01 PM
Why don't you run a small dg in your Main area with a function or procedure
that calls say, a version number or something, using an SQL stored procedure
that returns null or zero, zilch, nada, whatever - then decides what to do
with future processing?

If your app is completely based in native Dataflex, should be a nobrainer if
all you wanna do is make SQL calls from it - when the SQL server is
responding.
The DDB has SQL DMO that now lets it see what's out there, like a server &
db to select from, it's methods could help.
No multi-threading in VDF, so I guess the user would need to exit yer app,
re-enter it to retry for connection to the SQL server.
Thinking harder, I reckon you mebbe could use the Get SQLFileConnect Of
hoSQLMngr Something.File_number To hdbc style to see if it's out there,
catch it's value for decisions.

I'm fascinated how your assuming the SQL server won't be available when your
app needs it . . . !?
These days, I could talk to my fridge at home for just an extra $100 per
month.
I've seen everything but coffee making done with Dataflex over these long
years, but why design an app like this?
Baz.

Peter.Tawse
2-Nov-2005, 09:16 PM
Ben

Thanks for your assist so far, and I did as you suggested
in your last msg in the Visual-Dataflex NG.

However, whilst I can successfully test if the SQL server
IS active (by attempting to connect to one table on it), I seem
to be unable to trap an UN-successful connect attempt

The MSSQL Connect-failed msg dialog comes up (and I do
NOT want to see it) before I see the custom "Connection-failed"
message.

I've stripped the code back to 1 .Src file, plus any support pkgs
needed to compile.

Can I zip this up and send it to you, and ask if you can see
where the problem lies.

If I may, I'll send the zip file after I get your response, and please
advise if you'd prefer that I send it via email direct, rather than
thru the NG




Regards

Peter Tawse
Microbase Software
Melbourne, Australia
Ph: +61 3 9563-9100
Fax: +61 3 9563-9477
ptawse@internode.on.net

Ben Weijers
3-Nov-2005, 07:20 AM
Peter,

In the API we use there are two ways to make a connection.
1) Try to connect, if it does not succeed fail.
2) Try to conect if it does not succeed ask for missing information.

The CK used the second way. IOW, you will get the dialog always and there is
(currently) no way around it.

Regards,

Ben Weijers
Data Access Worldwide

Peter.Tawse
3-Nov-2005, 05:49 PM
Ben

Thanks for the extra advice, altho it was'nt what I wanted to hear !

>In the API we use there are two ways to make a connection.
>1) Try to connect, if it does not succeed fail.
>2) Try to conect if it does not succeed ask for missing information.
>
>The CK used the second way. IOW, you will get the dialog always and there is
>(currently) no way around it.

The "Connect-Failed" dialogs REALLY get in the road of a
clean "Connect-Test" routine. I understand that Mertech's
drivers do address this problem, and I note you use of
"currently" above - does this mean DAW are presently
addressing this scenario ?

or is this just wishful thinking on my part ?

If you are doing so - any estimated ETA ?


Regards

Peter Tawse
Microbase Software
Melbourne, Australia
Ph: +61 3 9563-9100
Fax: +61 3 9563-9477
ptawse@internode.on.net