1. Prasad Neelapala
  2. PowerBuilder
  3. Wednesday, 8 March 2023 12:42 PM UTC

 

Please advice how to fix this or let us know if we can edit the code to work with two servers PROD and COB servers when we switch server on frontend

 

CODE:::::://**********************************************************************
// Event - clicked for cb_ok::u_cb
//
// Purpose - Logon to database
//
// Logic -
//
// Parameters - NONE
//
// Returns - (0) OK
//
// Date Who What
// --------- ---------- ----------------------------------------------

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

long lhandle

SetPointer(HourGlass!) // look busy

lHandle = Handle(this)

// Local Variables

String This_Event, ls_errormsg
String S1, ls_appinifile
String win_sqls, regmain_sqls
string userid
string password
string server
string server_ptr
Integer Num
Integer I1
Long wait_counter, ll_errorcode

st_general_std logon_parms
n_cst_platform lnv_platform

This_Event = "Clicked(" + this.className() + ")"

// Determine frame
iw_Frame = gnv_app.of_GetFrame()

// *******************************************************************************
// tell the user what is going on.....
// *******************************************************************************
iw_Frame.SetMicroHelp("Logon Started, Please Wait...")

// Setup local variables
ls_AppIniFile = gnv_app.of_GetAppIniFile()

//*****************************************************************
// Retrieve Values
//*****************************************************************
dw_logon.AcceptText()
userid = dw_logon.GetItemString(1,'slogonid')
password = dw_logon.GetItemString(1, 'spassword')

IF Len(userid) > 0 AND Len(password) > 0 THEN

ELSE
SetForegroundWindow(lHandle)
gnv_app.inv_error.of_Message("Login Error", 'Please enter a logon id and a password', Stopsign!)
Return
END IF

server = dw_logon.GetItemString(1,'sServer')
server_ptr = server

//*****************************************************************
// setup database connection parameters
//*****************************************************************
// Encrypt the userid. The default is application id + user id (unless userprefix
// is stated). This allows the application to "hide" the prefix from the user,
// in case they go hacking into the ini file. Override this by overriding the
// of_encrypt function in descendant app. Note NEW APPS should use encryption

SQLCA.Logid = of_encrypt(userid)
SQLCA.LogPass = password
//SQLCA.servername =server ----------------(if we use this we are getting named pipe provider error so we have commented it )
SQLCA.servername ="mss-rd-p001-023.eur.nsroot.net,2431"  ------------- SO we have hardcoded the listener here to make it work - temporary fix
SQLCA.DBMS = lower(trim(ProfileString(ls_AppIniFile, server_ptr, "dbms","")))

// If no DBMS set then generate an error and exit
IF len(SQLCA.DBMS) = 0 THEN
SetForegroundWindow(lHandle)
gnv_app.inv_error.of_message("INI File Error", "Could not find a DBMS value for:" + server + &
"~r~n" +&
"Please check your INI file (" + ls_AppIniFile + ")"&
,StopSign!)
iw_Frame.SetMicroHelp(gnv_app.iapp_object.microhelpdefault)
Return
END IF

// Set the sybase environment variables
IF ib_SetSybaseEnv THEN
f_SetPlatform(lnv_platform, TRUE)
lnv_platform.of_SetSybaseEnv(SQLCA.DBMS, server)
f_SetPlatform(lnv_platform, FALSE)
END IF

SQLCA.DataBase = ProfileString(ls_AppIniFile, server_ptr, "database","")
SQLCA.Lock = ProfileString(ls_AppIniFile, server_ptr, "lock","")

//*****************************************************************
// Determine auto-commit - check user preferences first in case of override
//*****************************************************************
IF NOT gnv_app.of_GetValue(server_ptr, "autocommit", S1 ) THEN
S1 = Upper(ProfileString(ls_AppIniFile, server_ptr, "autocommit",""))
END IF
Choose Case S1
Case "", "FALSE", "0", "NO"
SQLCA.AutoCommit = False
Case "TRUE", "1", "YES"
SQLCA.AutoCommit = True
End

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 8 March 2023 13:45 PM UTC
  2. PowerBuilder
  3. # 1

Hi Prasad;

  Normally, this is handled by your Networking & DBA teams to facilitate this. Done properly, it should be transparent to the PB App (s).

Regards ... Chris 

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 8 March 2023 15:48 PM UTC
Hi Prasad;

This is actually super simple to implement in a PB App. For example....

SQLCA.DBParm="SvrFailover='Yes'" // Oracle or ASE DBMS

SQLCA.dbparm="FailoverPartner='<MirrorServerName>'" // SQL Server (SNC or MSO drivers)

Food for thought. HTH

Regards ... Chris
  1. Helpful 1
  1. Olan Knight
  2. Thursday, 9 March 2023 00:27 AM UTC
Another approach is to use an IF statement, and set the SERVER value to either a value read in from the INI file OR a hardcoded value.

// Pseudo code

boolean connected = FALSE

int count = 0



DO WHILE (count < 5 AND NOT (connected))

count = count + 1

Test server 1

If server 1 is working then

connect to server 1

if success then connected = TRUE

else

Test server 2

if server 2 is working then

connect to server 2

if success then connected = TRUE

end if

end if



if NOT (connected)) then SLEEP (5)

NEXT
  1. Helpful
  1. Olan Knight
  2. Thursday, 9 March 2023 00:28 AM UTC
And why are my indentations being removed from the post?!?!? :O
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 8 March 2023 13:55 PM UTC
  2. PowerBuilder
  3. # 2

Since you are already using an INI file, you could get the server from an entry there, instead of hardcoding it.

regards.

Comment
There are no comments made yet.
Prasad Neelapala Accepted Answer Pending Moderation
  1. Wednesday, 8 March 2023 14:49 PM UTC
  2. PowerBuilder
  3. # 3

Named pipes provider- could not open a connection to sql - connection string issue

but when i hardcode the listerner it is working

 

 

I am getting this issue  If i use SQLCA.servername =server 

 

// Profile PRC PROD
Database Profile:
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = <********>
SQLCA.ServerName = "mss-em-0333-aol.eur.nsroot.net,2431"
SQLCA.LogId = "prcmsprd"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='PrcDb',Provider='SQLNCLI11'"
 
 
 
App.ini

[PRCLPRODMSSQL1_DS]
;Autocommit defaults to false if no entry here - can be overridden in users registry
Name=ProductionMSSQL
AutoCommit=true
DBMS = "SNC SQL Native Client(OLE DB)"
DBPARM = "DataBase='PrcDb',PROVIDER='SQLNCLI11'"
ServerName = "mss-em-0333-aol.eur.nsroot.net,2431 "
LogId = "prcmsprd"
LogPass = "*******"
Lock="RR"
Attachments (1)
Comment
  1. Miguel Leeuwe
  2. Wednesday, 8 March 2023 15:56 PM UTC
Hi Prasad,

I just explained it: remove all the double quote (NOT the single quotes) from you APP.INI file and don't put any spaces after the = sign before the value.

Then in your code you do this:

SQLCA.Server = lower(trim(ProfileString(ls_AppIniFile, server_ptr, "ServerName","")))

  1. Helpful 3
  1. Andreas Mykonios
  2. Thursday, 9 March 2023 07:26 AM UTC
Hi Miguel.

This by the way should fix variable usage issue mentioned in original question.

Andreas.
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 9 March 2023 08:03 AM UTC
Hi, yes, that was my intention

:)
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.