Hi Arcadio,
you can't have a specific connexion per CAD and user because of the many-to-many relation between users and logical servers: a given user can be served by up to 32 different FoxinCloud servers running on the same Windows Server.
I understand, would it be impossible or difficult to implement this? I found that if I changed the handle in xxxserver.prg in the wUserSet method this would solve my problem, but in the tests I did not work. But in logic it should work since every request to the server is always passed by this method.
maybe you can try with xxxFrm.wUserSet()
anyhow I don't see how you can be sure to have the same connection handle in different logical servers, and why you absolutely need to assign a connection to a user
The idea is that each user can be from a different customer. And even though the database can be anywhere. I would create a domain control and would look for which connection the user belongs to. As I use postgresql, this is a very common scenario for me.
OK, got it
Even if CAD.dataSource is "Read/write at design time and run time", changing it once the cursor is built requires some testing.
If this works, you can set xxxCAD.useDEdataSource=.T.
and Form's dataEnvironment.dataSource
to a connect handle specific to the customer using xxxFrm.wUserSet()
.
It even works, but I have to call the form twice, the first one is not right for some reason. Keep the data from the previous handle.
Unclear… what means “call the form twice”?
I have to open the form, make the release and open again.
Did you understand the problem?
thisForm.wUserSet()
(where I advised you set dataEnvironment.dataSource
) executes before restoring the form's dataSession so it should be fine.
I'll try and if it does not work I'll record a video to show you
please do some debugging beforehand to make sure how the connection handle changes
I wrote this code in xxxserver.prg and in the tests it looks like it worked. Can I keep like this? I'm in the right way?
PROTECTED PROCEDURE wUserSet && Sets current user
&& executed before each request against the application
LPARAMETERS ;
twUser,; && User ID for application (=this.wUserAnonymous if anonymous)
tlTemp && [.F.] This user ID is impersonated (see awServer.wlUserTemp)
IF !EMPTY(NVL(twUser,""))
_screen.opera = VAL(EVL(NVL(GETWORDNUM(twUser,1,"|"),""),""))
__DOMINIO = GETWORDNUM(twUser,2,"|")
IF VAL(EVL(NVL(GETWORDNUM(twUser,3,"|"),""),"")) > 0
_screen.handle = VAL(EVL(NVL(GETWORDNUM(twUser,3,"|"),""),""))
FOR IFORMS = 1 TO _screen.FormCount
TRY
_screen.Forms(IFORMS).de_padrao.datasource = _screen.handle
CATCH
ENDTRY
ENDFOR
ENDIF
ENDIF
I would rather code this in xxxFrm.wUSerSet()
and set .dataSource
only in forms sharing thisForm.dataSessionID
You also need to set .dataSource
in form.Init()
, in the final code block, before .cursorRefresh()
It did not work on xxxFrm.wUSerSet (), I called the form using thisForm.wForm ("form \ formname.scx", "wFormCallBack1"). I have to make release of the screen to work. Already by xxxserver.prg always works. For some reason xxxfrm is not getting the correct handle. And I'm also set handle in init before the cursorrefresh.
OK, additional debugging would help understand why it did not work…
Just keep in mind that connection handles will differ across servers, and a user can be served by any server. You'll probably need to open all necessary connections (one per possible customer if I understood well) in xxxSets.Init()
and store the relation between customer and connection handle in some array (in _screen or public) or file.
If this file is a cursor, make sure to create it in the server's dataSession (dataSessionID
1) where xxxProcess.wUserSet()
operates, different from the application's dataSession where xxxSets.Init()
runs (dataSessionID
2).
I understand, how do I create the cursor in a datasession?
Hi Arcadio,
IMO the best solution would be:
addproperty(_Screen, 'oCustConnHandles', create('Collection'))
and use the customer ID as key to this collection- to save startup time, open connections whenever a user needs it and not yet open:
procedure wUserSet
…
customerID = …
…
local success, liHandle
liHandle = _Screen.oCustConnHandles.getKey(cast(customerID as M))
if empty(m.liHandle)
liHandle = sqlStringConnect(…)
if m.liHandle > 0
_Screen.oCustConnHandles.add(m.liHandle, cast(customerID as M))
success = .T.
endif
else
liHandle = _Screen.oCustConnHandles.item(m.liHandle)
success = m.liHandle > 0
endif
if m.success
_screen.iHandle = m.liHandle
else
&& process error
endif
Of course you don't need to save this collection as it's shared by all users (add to xxxServer._ScreenPropertiesSaveNot
).
However you can create a cursor in another dataSession like this:
local liDS
liDS = set('dataSession')
set dataSession to 1
create cursor…
set dataSession to m.liDS
I understood your idea. I'll see if it works out the way I need it. Thank you!
Thierry,
It worked perfectly as I expected !! Thank you very much. A doubt, in case you lose the connection. How do I renew the handle?
You would probably add another case in the .wUserSet()
code to get a new handle
If the property is informed in xxxServer._ScreenPropertiesSaveNot, will the new handle be available on all servers?
each server runs in a separate process and needs its own connection handles; IOW you won't share the connection handles across logical servers
What would be the idea, then? If you lose the connection to the same server only to the logical server that detected the problem?
exact, each server runs its own .wUSerSet()
I'm following this tip and it worked perfectly.
local liDS
liDS = set('dataSession')
set dataSession to 1
create cursor…
set dataSession to m.liDS
Now to assign the handle in init does not really work. Only in this way below does it work.
PROTECTED PROCEDURE wUserSet && Sets current user
&& executed before each request against the application
LPARAMETERS ;
twUser,; && User ID for application (=this.wUserAnonymous if anonymous)
tlTemp && [.F.] This user ID is impersonated (see awServer.wlUserTemp)
IF !EMPTY(NVL(twUser,""))
_screen.opera = VAL(EVL(NVL(GETWORDNUM(twUser,1,"|"),""),""))
__DOMINIO = GETWORDNUM(twUser,2,"|")
IF VAL(EVL(NVL(GETWORDNUM(twUser,3,"|"),""),"")) > 0
_screen.handle = VAL(EVL(NVL(GETWORDNUM(twUser,3,"|"),""),""))
FOR IFORMS = 1 TO _screen.FormCount
TRY
_screen.Forms(IFORMS).de_padrao.datasource = _screen.handle
CATCH
ENDTRY
ENDFOR
ENDIF
ENDIF
As I don't understand how you get the handle from the user ID, I can't say whether it'll work in a multi-server scenario or not.
I am storing a string in wuserlogin, the user id, domain name and domain id, between pipe. Example 1|DOMAIN|10. With this information I fill in a cursor in datassesion 1, and using a table in a database available to all users I get the login data from the user's domain that was logged in.
Follow my wUserSet
PROTECTED PROCEDURE wUserSet && Sets current user
&& executed before each request against the application
LPARAMETERS ;
twUser,; && User ID for application (=this.wUserAnonymous if anonymous)
tlTemp && [.F.] This user ID is impersonated (see awServer.wlUserTemp)
local liDS, __DOMINIO, __DOMINIOID
liDS = set('dataSession')
set dataSession to 1
IF !USED("HANDLES")
CREATE CURSOR HANDLES (DOMINIO_ID I NULL, DOMINIO C(20) NULL, HANDLE I NULL)
INDEX ON DOMINIO_ID TAG DOMINIO_ID
ELSE
SELECT HANDLES
ENDIF
IF !EMPTY(NVL(twUser,""))
_screen.opera = VAL(EVL(NVL(GETWORDNUM(NVL(twUser,""),1,"|"),""),""))
__DOMINIO = GETWORDNUM(NVL(twUser,""),2,"|")
__DOMINIOID = GETWORDNUM(NVL(twUser,""),3,"|")
IF VAL(__DOMINIOID) > 0 &&!EMPTY(NVL(__DOMINIO,""))
customerID = CAST(__DOMINIOID AS I) &&NVL(CUR_DOMINIO.ID,0)
IF customerID > 0
IF !SEEK(customerID,"HANDLES","DOMINIO_ID") &&empty(m.liHandle)
USE IN SELECT("CURCON")
USE IN SELECT("CUR_DOMINIO")
LOCAL M.ERROCON
M.ERROCON = .F.
TRY
IF SQLEXEC(_screen.handle_dominio,[SELECT ID FROM DOMINIO LIMIT 1],[CURCON]) < 1
M.ERROCON = .T.
ENDIF
CATCH
M.ERROCON = .T.
ENDTRY
IF M.ERROCON = .T.
IF NOT FILE("CONFSQL_DOMINIO.INI")
INKEY(2)
ELSE
__Cmd = "SQLSTRINGCONNECT('driver="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),1)+";server="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),2)+";database="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),3)+";uid="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),4)+";pwd="+IIF(VAL(MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),9)) = 1,DESCONVERTE(MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),5)),MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),5))+";port="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),7)+"')"
_screen.handle_dominio = &__cmd.
_screen.cmdatu = __Cmd
_screen.cmdatu_dominio = __Cmd
ENDIF
ENDIF
M.FEZ = SQLEXEC(_screen.handle_dominio,"SELECT ID, STRINGCMD FROM DOMINIO WHERE ID = ?NVL(customerID,0) ","CUR_DOMINIO")
TRY
SQLDISCONNECT(_screen.handle_dominio)
CATCH
ENDTRY
__Cmd = "SQLSTRINGCONNECT('"+ALLTRIM(NVL(CUR_DOMINIO.STRINGCMD,""))+"')"
liHandle = &__cmd.
IF m.liHandle > 0
APPEND BLANK IN HANDLES
REPLACE ;
DOMINIO_ID WITH customerID,;
DOMINIO WITH ALLTRIM(NVL(__DOMINIO,'')),;
HANDLE WITH m.liHandle;
IN HANDLES
success = .T.
ENDIF
ELSE
liHandle = NVL(HANDLES.HANDLE,0) &&_Screen.oCustConnHandles.item(m.liHandle)
success = m.liHandle > 0
TRY
USE IN SELECT("CURCON")
IF SQLEXEC(liHandle,[SELECT ID FROM OPERADOR LIMIT 1],[CURCON]) < 1
success = .F.
ENDIF
CATCH
success = .F.
ENDTRY
IF success = .T.
success = m.liHandle > 0
ELSE
USE IN SELECT("CURCON")
USE IN SELECT("CUR_DOMINIO")
LOCAL M.ERROCON
M.ERROCON = .F.
TRY
IF SQLEXEC(_screen.handle_dominio,[SELECT ID FROM DOMINIO LIMIT 1],[CURCON]) < 1
M.ERROCON = .T.
ENDIF
CATCH
M.ERROCON = .T.
ENDTRY
IF M.ERROCON = .T.
IF NOT FILE("CONFSQL_DOMINIO.INI")
INKEY(2)
ELSE
__Cmd = "SQLSTRINGCONNECT('driver="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),1)+";server="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),2)+";database="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),3)+";uid="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),4)+";pwd="+IIF(VAL(MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),9)) = 1,DESCONVERTE(MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),5)),MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),5))+";port="+MLINE(ALLTRIM(FILETOSTR("CONFSQL_DOMINIO.INI")),7)+"')"
_screen.handle_dominio = &__cmd.
_screen.cmdatu = __Cmd
_screen.cmdatu_dominio = __Cmd
ENDIF
ENDIF
M.FEZ = SQLEXEC(_screen.handle_dominio,"SELECT ID, STRINGCMD FROM DOMINIO WHERE ID = ?NVL(customerID,0) ","CUR_DOMINIO")
TRY
SQLDISCONNECT(_screen.handle_dominio)
CATCH
ENDTRY
__Cmd = "SQLSTRINGCONNECT('"+ALLTRIM(NVL(CUR_DOMINIO.STRINGCMD,""))+"')"
liHandle = &__cmd.
if m.liHandle > 0
REPLACE ;
HANDLE WITH m.liHandle;
IN HANDLES
success = .T.
ELSE
success = .F.
endif
ENDIF
endif
if m.success
_screen.iHandle = m.liHandle
_screen.Handle = m.liHandle
else
_screen.iHandle = 0
_screen.handle = 0
&& process error
ENDIF
ENDIF
ENDIF
ENDIF
IF !EMPTY(NVL(twUser,""))
IF _screen.handle > 0
_screen.opera = VAL(EVL(NVL(GETWORDNUM(twUser,1,"|"),""),""))
_screen.handle = _screen.iHandle
IF SEEK(VAL(GETWORDNUM(twUser,3,"|")),"HANDLES","DOMINIO_ID") &&empty(m.liHandle)
__DOMINIO = ALLTRIM(NVL(HANDLES.DOMINIO,""))
ENDIF
ENDIF
ENDIF
IF _screen.handle > 0 && ERRO TIMEOUT
TRY
M.FEZ = SQLEXEC(_screen.handle,[SET DATESTYLE TO EUROPEAN])
CATCH
_screen.handle = 0
ENDTRY
ENDIF
set dataSession to m.liDS
IF _screen.handle > 0
FOR IFORMS = 1 TO _screen.FormCount
TRY
_screen.Forms(IFORMS).de_padrao.datasource = _screen.handle
CATCH
ENDTRY
TRY
_screen.Forms(IFORMS).de_padrao1.datasource = _screen.handle
CATCH
ENDTRY
ENDFOR
ENDIF
Follow my load form code
SET DATE BRITISH
SET DELETED ON
wcPropSaveEdit(this, '')
thisform.apaga_busca()
LOCAL twUser
local liDS, __DOMINIO, __DOMINIOID
set dataSession to 1
__DOMINIO = ALLTRIM(NVL(_screen.dominio,""))
__DOMINIOID = CAST(NVL(_screen.id_dominio,0) AS I)
IF USED("HANDLES")
IF __DOMINIOID > 0
customerID = CAST(__DOMINIOID AS I)
IF customerID > 0
IF SEEK(customerID,"HANDLES","DOMINIO_ID")
_SCREEN.Handle = NVL(HANDLES.HANDLE,0)
ENDIF
ENDIF
ENDIF
ENDIF
set dataSession to m.liDS
thisform.de_padrao.datasource = _screen.handle &&M.OBJHANDLE.HANDLE
thisform.de_padrao.caparam.CursorFill()
IF USED("CRPARAM") && ERRO TIMEOUT
GO TOP IN CRPARAM
ENDIF
thisform.de_padrao.camotina.selectcmd = thisform.de_padrao.camotina.selectcmd + " WHERE (COALESCE(STATUS,0) BETWEEN ?thisform.bstatus_ini AND ?thisform.bstatus_fin) AND "
thisform.de_padrao.camotina.selectcmd = thisform.de_padrao.camotina.selectcmd + " ((COALESCE(length((Cast(?thisform.bid AS CHAR))),0) = 0) OR MOTINA.ID = ?thisform.bid) AND "
thisform.de_padrao.camotina.selectcmd = thisform.de_padrao.camotina.selectcmd + " ((COALESCE(length((Cast(?thisform.bdescricao AS CHAR))),0) = 0) OR UPPER(motina.descricao) like UPPER(?thisform.bdescricao)) AND "
thisform.de_padrao.camotina.selectcmd = thisform.de_padrao.camotina.selectcmd + " ((COALESCE(length((Cast(?thisform.bsemdados AS CHAR))),0) = 0) OR MOTINA.ID = ?thisform.bsemdados) "
thisform.de_padrao.camotina.selectcmd = thisform.de_padrao.camotina.selectcmd + " ORDER BY MOTINA.ID "
thisform.de_padrao.camotina.CursorFill()
Folow my init form code
local liDS, __DOMINIO, __DOMINIOID, twUser && ERRO TIMEOUT
liDS = set('dataSession')
set dataSession to 1
twUser = thisform.wUserGet()
_screen.opera = VAL(EVL(NVL(GETWORDNUM(NVL(twUser,""),1,"|"),""),""))
__DOMINIO = GETWORDNUM(NVL(twUser,""),2,"|")
__DOMINIOID = GETWORDNUM(NVL(twUser,""),3,"|")
IF USED("HANDLES")
IF VAL(__DOMINIOID) > 0 &&!EMPTY(NVL(__DOMINIO,""))
customerID = CAST(__DOMINIOID AS I) &&NVL(CUR_DOMINIO.ID,0)
IF customerID > 0
IF SEEK(customerID,"HANDLES","DOMINIO_ID")
_SCREEN.Handle = NVL(HANDLES.HANDLE,0)
ENDIF
ENDIF
ENDIF
ENDIF
set dataSession to m.liDS
.de_padrao.datasource = _screen.handle
.de_padrao.camotina.datasource = _screen.handle
.de_padrao.caparam.datasource = _screen.handle
IF !USED("CRMOTINA")
.de_padrao.camotina.Cursorfill() && ERRO TIMEOUT
ENDIF
IF !USED("CRPARAM")
.de_padrao.caparam.Cursorfill() && ERRO TIMEOUT
ENDIF
.de_padrao.caparam.Cursorrefresh()
.de_padrao.camotina.CursorRefresh()