FoxInCloud
Handle Conections
Gravatar is a globally recognized avatar based on your email address. Handle Conections
  Arcadio Bianco
  All
  Apr 16, 2019 @ 11:09pm

Do I have different connection handles for different users using cursor adapter?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 17, 2019 @ 12:59am

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.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 17, 2019 @ 04:04am

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.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 17, 2019 @ 04:09am

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

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 17, 2019 @ 04:27am

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.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 17, 2019 @ 07:45am

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().

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 17, 2019 @ 08:01am

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.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 17, 2019 @ 08:06am

Unclear… what means “call the form twice”?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 17, 2019 @ 08:11am

I have to open the form, make the release and open again.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  Arcadio Bianco
  Apr 17, 2019 @ 09:38am

Did you understand the problem?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 18, 2019 @ 12:34am

thisForm.wUserSet() (where I advised you set dataEnvironment.dataSource) executes before restoring the form's dataSession so it should be fine.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 18, 2019 @ 02:37am

I'll try and if it does not work I'll record a video to show you

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 18, 2019 @ 02:39am

please do some debugging beforehand to make sure how the connection handle changes

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 18, 2019 @ 06:13am

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 	
Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 18, 2019 @ 09:34am

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()

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  Arcadio Bianco
  Apr 18, 2019 @ 10:39am

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.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 19, 2019 @ 01:32am

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).

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 19, 2019 @ 09:39am

I understand, how do I create the cursor in a datasession?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 23, 2019 @ 01:28am

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
Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 23, 2019 @ 05:33am

I understood your idea. I'll see if it works out the way I need it. Thank you!

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  Arcadio Bianco
  Apr 28, 2019 @ 05:19pm

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?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 29, 2019 @ 05:31am

You would probably add another case in the .wUserSet() code to get a new handle

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 29, 2019 @ 05:48am

If the property is informed in xxxServer._ScreenPropertiesSaveNot, will the new handle be available on all servers?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 29, 2019 @ 05:53am

each server runs in a separate process and needs its own connection handles; IOW you won't share the connection handles across logical servers

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  Apr 29, 2019 @ 06:27am

What would be the idea, then? If you lose the connection to the same server only to the logical server that detected the problem?

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  Apr 29, 2019 @ 06:33am

exact, each server runs its own .wUSerSet()

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  re: Handle Conections
  May 5, 2019 @ 05:49am

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 
Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  FoxInCloud Support - Thierry N.
  Arcadio Bianco
  May 5, 2019 @ 06:31am

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.

Gravatar is a globally recognized avatar based on your email address. re: Handle Conections
  Arcadio Bianco
  FoxInCloud Support - Thierry N.
  May 5, 2019 @ 07:35am

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()
© 1996-2019