#10 DB Connect Improvements

[Anonymous]

The idea is to have the server ping the database server
with a timeout prior to trying the reconnect, which
could take 30 seconds or longer to timeout, thus tying
up all of your agents and spewing out more YSOD.

Mario Paranhos writes:

Here is some beta code. It appears to be working well
on 9.1B on both win2K
and Linux. Assuming you use IP addresses rather then
names, connections
returned either successful or failed in under 5
seconds. I even tested
connecting to DB's across the internet over a VPN with
the same results.

Mario

/***************************************************************************
/
FUNCTION pingHost RETURNS LOGICAL (INPUT chrDBHost AS
CHARACTER):
DEFINE VARIABLE pingResponse AS CHARACTER NO-UNDO.
DEFINE VARIABLE pingValidResponseUNIX AS CHARACTER
NO-UNDO.
DEFINE VARIABLE pingValidResponseNT AS CHARACTER NO-UNDO.
DEFINE VARIABLE pingValidResponse AS CHARACTER NO-UNDO.
DEFINE VARIABLE pingUNIX AS CHARACTER NO-UNDO.
DEFINE VARIABLE pingNT AS CHARACTER NO-UNDO.
DEFINE VARIABLE pingCMD AS CHARACTER NO-UNDO.
DEFINE VARIABLE pingTimeOut AS INTEGER NO-UNDO.
DEFINE VARIABLE hostAlive AS LOGICAL NO-UNDO.

ASSIGN pingValidResponseUNIX = "9 bytes from"
pingValidResponseNT = "Reply from"
pingTimeOut = 50 /* Windows is in
milliseconds, UNIX seconds */
pingNT = "ping -l 1 -n 1 -w " +
STRING(pingTimeOut) + " " +
chrDBHost.
pingUNIX = "ping -s 1 -c 1 -w " +
STRING(pingTimeOut) + " " +
chrDBHost.
.

IF "{&opsys}" = "UNIX" THEN
ASSIGN pingCMD = pingUNIX
pingValidResponse = pingValidResponseUNIX.
ELSE
ASSIGN pingCMD = pingNT
pingValidResponse = pingValidResponseNT.

INPUT THROUGH VALUE(pingCMD).
pingLoop:
REPEAT:
IMPORT UNFORMATTED pingResponse.
IF pingResponse BEGINS pingValidResponse THEN
DO:
ASSIGN hostAlive = TRUE.
LEAVE pingLoop.
END.
END.
INPUT CLOSE.

RETURN hostAlive.
END FUNCTION. /* pingHost */

FUNCTION serverIsUp RETURN LOGICAL (INPUT pServer AS
CHARACTER,
INPUT pPort AS
CHARACTER):
DEFINE VARIABLE hSocket AS HANDLE NO-UNDO.
DEFINE VARIABLE lStatus AS LOGICAL NO-UNDO.

CREATE SOCKET hSocket.
ASSIGN lStatus = hSocket:CONNECT("-H " + pServer + "
-S " + pPort)
NO-ERROR.
hSocket:DISCONNECT().
DELETE OBJECT hSocket.
ASSIGN hSocket = ?.
RETURN lStatus.
END FUNCTION. /* serverIsUp */

FUNCTION connectDB RETURNS LOGICAL (INPUT chrDBName AS
CHARACTER,
INPUT chrDBHost AS
CHARACTER,
INPUT chrDBService
AS CHARACTER):
IF NOT pingHost(chrDBHost) THEN
RETURN FALSE.

IF NOT serverIsUp(chrDBHost,chrDBService) THEN
RETURN FALSE.

CONNECT VALUE(chrDBName) -H VALUE(chrDBHost) -S
VALUE(chrDBService) -N TCP
NO-ERROR.
IF ERROR-STATUS:ERROR THEN
RETURN FALSE.

RETURN TRUE.
END FUNCTION. /* connectDB */

MESSAGE string(connectDB("DBNAME","IP","PORT")) VIEW-AS
ALERT-BOX.

/***************************************************************************

[Steve Southwell]

Logged In: YES
user_id=52644

Also regarding a simplified way to connect DBs, Gregg
Creedon-Shirley writes:

Yes.. sent it a while ago. And have the dynamic DB's
working. the .ini has
the following entry:

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
\#   DATABASES
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
\# Specify database connection procedures for EACH DATABASE
\# one line for each database:  connection procedure,notification

procedure
# (Use the connectwebstate.p and notifywebstate.p procedures
as templates)
#
# Note: Will be changed soon to support connection
parameters specified here.
# Example:
# database.<dbname> = <hostname>,<connection type>,<service
name or
port #>

database.testdb = localhost,TCP,testdb

Attached are:

connectdb.p : which handles the connection based on the
parameters passed.
It could be modified pretty easily to do a shared memory
connect if you wanted.

notifydb.p : handles notification when a database is down

tt_ini.i : modification to ttDB to include the hostname,
servicename and connectiontype fields... again, could be
modified to have shared memory info
as well

robust.i : in ReadConfigOptions the read of the Database
info was modified for host, service and connection type;
could be tweaked to be this or shared
memory; in Handle-DB-Connect the logic was changed to only
call the one module connectdb.p or notifydb.p where
appropriate; in Check-Default-Databases added logic to bring
up ALL databases at startup if possible - even if not in the
default list

ffweb-disp.p : modified to use the check-default-databases
procedure when connecting during initial startup

Hope this helps....

[Steve Southwell]

connectdb.p = database connection program

[Steve Southwell]

[Steve Southwell]

[Steve Southwell]

[Steve Southwell]

[Nobody/Anonymous]

Logged In: NO

Suggestion on shared memory connections - the CONNECT may not even really be needed,
try proutil -busy and check the return code. A check through the process table for
_mprosrv might also be an alternative.

[Steve Southwell]

Logged In: YES
user_id=52644

For shared-memory database connects, we need to first run
proutil holder. It returns OS code 0, 14, or 16 if busy
Two secs on Unix, a few more on NT.

[Steve Southwell]

Logged In: YES
user_id=52644

Finally, this is done! Version 1.1 contains the best ideas
from all. Thanks to Gregg Creedon-Shirley, Mario Paranhos
and all the others who have suggested this from the start.

The new database connection methods allow for configura
tion of the connections in the .ini file by specifying
either filename, or host/service/network. Of course,
they're still backward compatible.

For sample connection syntax, download FFW 1.1 beta and have
a look at the .ini file, which explains it better.

Only issues remaining are whether to use the proutil check
before connecting shared-memory, and whether it's necessary
to use Mario's ping utility to see if the host is reachable
for network conections.