Results 1 to 9 of 9

Thread: Forms loading off a sql set - how do I edit named connection string

  1. #1
    Member
    Real Name
    Bob Nilles
    Join Date
    Feb 2010
    Posts
    21

    Default Forms loading off a sql set - how do I edit named connection string

    I have developed a form based on a active linked sql database -actually a set. When I go to distribute it the sql server maybe named differently on each user site. I can program a connection string for most of my program/project but when I load this form it errors because it can not find the server. How or can I edit the AlphaDAO named connection string so that the form looks to the proper server at the end user installation site?

    Thanks
    Bob

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Forms loading off a sql set - how do I edit named connection string

    You can export the connection string (yours) to a text file. The contents will be something like

    AlphaDAO||SAL||{A5API='ADONET',A5Provider='System.Data.SqlClient',A5Syntax='SQLServer',Data Source='BGDCR03',Initial Catalog='SAL5',Integrated Security='True'}||SAL

    Send the text file with your application. Tell the end user to edit the data source section and save.

    Give them a button or other means to import the edited connection string. Code is

    a5_importconnectionstrings()

    which will open the import dialog. They can navigate to the saved location. They will probably have to restart Alpha after importing it for the form to recognize the change.
    Last edited by Stan Mathews; 12-30-2016 at 10:56 AM.
    There can be only one.

  3. #3
    Member
    Real Name
    Bob Nilles
    Join Date
    Feb 2010
    Posts
    21

    Default Re: Forms loading off a sql set - how do I edit named connection string

    Stan,
    Thanks. I give them a default database field that they can save the server name in.
    Can I automatically create a new export string and then run a5_importconnectionstrings() to update the namespaces and thusly allow the forms to load properly? Wow what a long question.

    Bob

  4. #4
    Member
    Real Name
    Bob Nilles
    Join Date
    Feb 2010
    Posts
    21

    Default Re: Forms loading off a sql set - how do I edit named connection string

    Or does the dialog screen have to show up? Silent mode?

  5. #5
    Member
    Real Name
    Bob Nilles
    Join Date
    Feb 2010
    Posts
    21

    Default Re: Forms loading off a sql set - how do I edit named connection string

    Stan,
    I am not sure I posted properly but I have a couple follow up questions. And many Thanks. Happy New Year!!

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Forms loading off a sql set - how do I edit named connection string

    a5_importconnectionstrings() doesn't accept or require parameters. It just opens the dialog for selection of a file to import.
    There can be only one.

  7. #7
    Member
    Real Name
    Paul Verboom
    Join Date
    Apr 2006
    Location
    Halifax, Nova Scotia, Canada
    Posts
    135

    Default Re: Forms loading off a sql set - how do I edit named connection string

    l,ve run into same the same issue, active link tables that need to connected to different SQL sources. The following function will update the named connection string used by a list of active link tables. The function looks up the named connection from table where the user can save a number of connection strings you will need to edit this part of the code.

    Code:
    'Date Created: 22-Sep-2016 08:07:27 PM
    'Last Updated: 27-Sep-2016 10:43:01 AM
    'Created By  : verboopa2
    'Updated By  : verboopa2
    '------------
    'zLNKadjust
    '------------
    'Function to adjust the NAMED CONNECTION string in a list of Active Link Tables.
    'Tables must be defined with a named connection when creating the Active link tables
    'Script searches the *.DBP file for the following string and updates it.
    '<ConnectionString="::Name::NSES_Test_Site1">
    'The path to the files are hard coded in the *.DBP file but do not appear to be consulted
    '
    'DialogTtl 	- Tital for Dialogs produced by this function reflects what Active Line Tables are being adjusted
    'TableList	- List of Active Link Tables to Update Named Connection In
    'SiteSys	- The Connection Type Site or System that Active Link Tables are connecting to
    'RETURNS	- nothing and errors are handled by this function, calling function is just expected to provide list of tables
    
    FUNCTION zLNKadjust AS C (DialogTtl as C, TableList AS C, SiteSys as C)
    'FOR DEBUGGING
    'DialogTtl = "Set ADT edit Site (ADT-EditSite)"
    'TableList =<<%txt%
    'l_hisorders
    'l_hispatients
    'l_hisvisits
    '%txt% 
    'SiteSys = "Site"
    
    	'---------------------
    	'// Set Debug Flags --
    	'---------------------
    	DIM Debug as C = "t" 											'use following statement to check for debug flag: if ("m" $ Debug)
    	DbgSet(type::xbasicmodule.get_current(),Debug)					'set debug values in deployed enviroments
    	's - skip checking existing Named Connections, used force reset of Named Connections
    	'u - Error out if Named Connection is not recognised NOT recommended once deployed
    	't - trace out the Connect string lines as it is altered
    	'w - skip rewritting the Altered .DBP files
    
    	'------------------------------
    	'// Set up for Error handler --
    	'------------------------------
    	ON ERROR GOTO ERRORHANDLER
    	
    	'------------------------------------
    	'// Get existing Named Connections --
    	'------------------------------------
    	'Section gets the Named connection used on the Local or Shadowed 
    	'and Master copies of Tables if they exist does sveral checks to verify
    	'Named Connections are cosistant and exist.
    	
    	DIM LclCnct as C											'Connection used by this Local installation
    	DIM Master as L = .F.										'Master copy and Shadow copy situation	
    	DIM MstCnct as C											'Connection used in MAster table if Shadowed install
    	DIM FileName as C											'The Link Table Data Base Pointer File
    	DIM InstallType as C										'Installation Connection being adjusted in
    	DIM Temp as C												'Temporary string		
    	
    	Master = .NOT. isnull(a5.Get_Master_Name())					'Set falg for Master Shadow situation
    	IF .NOT. ("s" $ Debug)
    		IF Master												'There is a master get its Connection Names from it
    			InstallType = "Master"	
    			FOR EACH Line in TableList							'Check connenction used for each Link Table in list
    				FileName = file.filename_parse(a5.Get_Master_Name(),"dp")+Line+".DBP"
    				IF File.Exists(FileName)
    					Temp = zLnkSetCnnct(FileName,"",Debug)
    					IF isnull(Temp)
    						error_generate("Unable to determine Named Connection")
    					ELSEIF (MstCnct <> Temp) .AND. (MstCnct  <> "")
    						error_generate("Mismatched Named Connections")
    					ELSE
    						MstCnct = Temp
    					end if
    				ELSE
    					error_generate("File missing")
    				end if
    			NEXT
    			MstSiteName = lookup(zGetTblName("a-config"),"Connectionstring =" + Quote(MstCnct),"Name")
    			IF isnull(MstSiteName)
    				IF ("u" $ Debug) 
    					error_generate("Unrecognized Named Connection: " + MstCnct)
    				ELSE
    					MstSiteName = "<< Requires Reset >>"
    				end if
    			end if
    			InstallType = "Shadow"								'We have checked master install Connections
    		ELSE													'so local is really shadow copy
    			InstallType = "Local"								'No master not a shadow copy	
    		end if
    																'Check connection for each Link Table in
    		FOR EACH Line in TableList								'Local table which can be shadowed table
    			FileName = file.filename_parse(a5.Get_Name(),"dp")+Line+".DBP"
    			IF File.Exists(FileName)
    				Temp = zLnkSetCnnct(FileName,"",Debug)
    				IF isnull(Temp)
    					error_generate("Unable to determine Named Connection")
    				ELSEIF (LclCnct <> Temp) .AND. (LclCnct  <> "")
    					error_generate("Mismatched Named Connections")
    				ELSE
    					LclCnct = Temp
    				end if
    			ELSE
    				error_generate("File missing")
    			end if
    		NEXT
    		LclSiteName = lookup(zGetTblName("a-config"),"Connectionstring =" + Quote(LclCnct),"Name")
    		IF ("u" $ Debug) .AND. isnull(LclSiteName)
    			IF ("u" $ Debug) 
    				error_generate(*concat_lines("Unrecognized Named Connection",LclCnct))
    			ELSE
    				LclSiteName = "<< Requires Reset >>"
    			end if
    		end if
    	end if
    
    	'------------------------------------
    	'// Let User Select Site or System --
    	'------------------------------------
    	DIM MstUpdate as L
    	DIM Result as C
    	DIM Connection as C
    	ConnectionList = table.external_record_content_get("a-config","Name","","type = " + Quote(SiteSys))
    	
    	Dialog =<<%dlg%
    {region}
    {wrap=80}
    If you are using a Shadow installation you can optionally update the master installation.;
    Additional shadow installations must use this same script to updated selected installation. The Refresh Shadow Installation can be used to get updates from the Master Installation. 	
    All forms using these Connections must be closed and reopened before changes take effect.
    {endregion};
    {region}
    {staticConditionalSection:1:Master}
    Shadow Installation {Insert_SiteSys}:| [%r%.40LclSiteName];
    Master Installation {Insert_SiteSys}:| [%r%.40MstSiteName];
    Update Master Installation:| (5MstUpdate);
    {end_staticConditionalSection:1}
    {staticConditionalSection:2:.NOT.Master}
    Local Installation {Insert_SiteSys}:| [%r%.40LclSiteName];
    {end_staticConditionalSection:2}
    Set Installation\(s) to {Insert_SiteSys}:| [.40,5Connection^#ConnectionList];
    {endregion};
    {line=1,0};
    {region}
    <*15&Proceed!Proceed> <15&Exit!exit>
    {endregion};
    %dlg%
    
    	DIM sr as C													'control what gets subsituted
    	sr =<<%txt%
    {Insert_SiteSys} = SiteSys
    %txt%
    	Dialog = stritran_multi_expressions(Dialog,sr) 				'Subsitute in some variables
    	
    	Code =<<%code%
    		if a_dlg_button = "proceed" then 						'Used to proceed after dialog			
    			Mess = ""
    			IF isnull(Connection) 
    			    Mess = *concat_lines(Mess,"A " + SiteSys + "Connection Must be selected")
    			end if
    			'..... more checks here ...
    			IF .NOT. isnull(Mess)
    				ui_msg_box(DialogTtl,Mess,UI_ATTENTION_SYMBOL+UI_OK)
    				a_dlg_button = ""
    			end if
    		end if
    	%code%
    	
    	Result = ui_dlg_box(DialogTtl,Dialog,Code)					'present Dialog
    	if Result <>"Proceed" then									'do we proceed
    	    end
    	end if
    			
    	'-----------------------------
    	'// Update Named Connection --
    	'-----------------------------
    	InstallType = "Both"
    	FileName = ""
    	NewConnection = alltrim(lookup(zGetTblName("a-config"),"Name =" + Quote(Connection),"Connectionstring"))
    	IF isnull(NewConnection)
    		error_generate(*concat_lines("Unrecognized Connection selected",Connection))
    	end if
    	
    	
    	IF Master .AND. MstUpdate								'Do we updtae Named Connections in master files
    		InstallType = "Master"	
    		FOR EACH Line in TableList							'Update Name Connection in each file
    			FileName = file.filename_parse(a5.Get_Master_Name(),"dp")+Line+".DBP"
    			IF File.Exists(FileName)
    				Temp = zLnkSetCnnct(FileName,NewConnection,Debug)
    				IF isnull(Temp)
    					error_generate("Unable to set new Named Connection")
    				end if
    			ELSE
    				error_generate("File missing")
    			end if
    		NEXT
    		InstallType = "Shadow"								'We have updated master install Named Connections
    	ELSE													'So local is really shadow copy
    		InstallType = "Local"								'No master not a shadow copy but just local
    	end if
    															'Update Named Connection in each 
    	FOR EACH Line in TableList								'Local table which can be shadowed table
    		FileName = file.filename_parse(a5.Get_Name(),"dp")+Line+".DBP"
    		IF File.Exists(FileName)
    			Temp = zLnkSetCnnct(FileName,NewConnection,Debug)
    			IF isnull(Temp)
    				error_generate("Unable to set new Named Connection")
    			end if
    		ELSE
    			error_generate("File missing")
    		end if
    	NEXT
    			
    END FUNCTION
    'FOR DEBUGGING
    'END
    '----------------------
    '// Support Function --
    '----------------------
    'Locate and optionally replace Named Connection in Active Link Table
    'Debug			- Debug flag set in calling script/function
    'FileName		- Name of file must include full path and .DBP extension
    'NewConnection	- Optional new Named Connection to install if null just return existing named connection
    'RETURNS		- Existing named connection NULL if unable to determine Named Connection
    '
    'When debugging scripts that use String Scanner Objects 
    ' the following watch variable is very useful
    'SS.GetToOffset() + "*" + SS.GetRemainder()
    
    FUNCTION CnctName as C(Debug as C, FileName as C, NewConnection as C = "")
    	DIM txt as C
    	DIM SS as P
    	txt = GET_FROM_FILE(FileName)
    	SS = stringscanner.create(txt)
    	IF SS.SkiptoString("<ConnectionString=\"::Name::")
    		SS.ScanOverSmatch("<ConnectionString=\"::Name::")
    		CnctName = SS.ScanToString("\"")
    		IF .NOT.(isnull(CnctName).OR.isnull(NewConnection))		'Do we place in a new Named Connection
    			IF ("t" $ Debug)									'Trace the Connection line before rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Orginal Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.SkipOver(-1 * LEN(CnctName))						'Move back over the Named Connection found
    			SS.Replace(NewConnection,LEN(CnctName))				'New Named Connection replaced charcters
    			IF ("t" $ Debug)									'Trace the Connection line after rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Altered Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.Reset()											'Get the modified file and rewrite it
    			txt = SS.GetRemainder()
    			SAVE_TO_FILE(txt,FileName,.F.)
    		end if	
    	ELSE
    		CnctName = ""
    	end if
    END FUNCTION
    
    
    '-------------------
    '// ERROR Handler --
    '-------------------
    ERRORHANDLER:
    ON ERROR GOTO 0													'No more special error traps that continue the code
    
    Mess =<<%txt%
    ERROR
    {error_text_get()}
    FileName: {FileName}
    Install Type: {InstallType}
    Script: {error_script_get()}
    Line: {error_line_number_get()}
    %txt%
    Mess = Evaluate_String(Mess)
    ui_msg_box(DialogTtl,Mess,UI_ATTENTION_SYMBOL+UI_OK)
    zLogAdd(Mess)
    END
    The above function makes use of following function.
    Code:
    'Date Created: 22-Sep-2016 08:07:27 PM
    'Last Updated: 27-Sep-2016 10:43:01 AM
    'Created By  : verboopa2
    'Updated By  : verboopa2
    '------------
    'zLNKadjust
    '------------
    'Function to adjust the NAMED CONNECTION string in a list of Active Link Tables.
    'Tables must be defined with a named connection when creating the Active link tables
    'Script searches the *.DBP file for the following string and updates it.
    '<ConnectionString="::Name::NSES_Test_Site1">
    'The path to the files are hard coded in the *.DBP file but do not appear to be consulted
    '
    'DialogTtl 	- Tital for Dialogs produced by this function reflects what Active Line Tables are being adjusted
    'TableList	- List of Active Link Tables to Update Named Connection In
    'SiteSys	- The Connection Type Site or System that Active Link Tables are connecting to
    'RETURNS	- nothing and errors are handled by this function, calling function is just expected to provide list of tables
    
    FUNCTION zLNKadjust AS C (DialogTtl as C, TableList AS C, SiteSys as C)
    'FOR DEBUGGING
    'DialogTtl = "Set ADT edit Site (ADT-EditSite)"
    'TableList =<<%txt%
    'l_hisorders
    'l_hispatients
    'l_hisvisits
    '%txt% 
    'SiteSys = "Site"
    
    	'---------------------
    	'// Set Debug Flags --
    	'---------------------
    	DIM Debug as C = "t" 											'use following statement to check for debug flag: if ("m" $ Debug)
    	DbgSet(type::xbasicmodule.get_current(),Debug)					'set debug values in deployed enviroments
    	's - skip checking existing Named Connections, used force reset of Named Connections
    	'u - Error out if Named Connection is not recognised NOT recommended once deployed
    	't - trace out the Connect string lines as it is altered
    	'w - skip rewritting the Altered .DBP files
    
    	'------------------------------
    	'// Set up for Error handler --
    	'------------------------------
    	ON ERROR GOTO ERRORHANDLER
    	
    	'------------------------------------
    	'// Get existing Named Connections --
    	'------------------------------------
    	'Section gets the Named connection used on the Local or Shadowed 
    	'and Master copies of Tables if they exist does sveral checks to verify
    	'Named Connections are cosistant and exist.
    	
    	DIM LclCnct as C											'Connection used by this Local installation
    	DIM Master as L = .F.										'Master copy and Shadow copy situation	
    	DIM MstCnct as C											'Connection used in MAster table if Shadowed install
    	DIM FileName as C											'The Link Table Data Base Pointer File
    	DIM InstallType as C										'Installation Connection being adjusted in
    	DIM Temp as C												'Temporary string		
    	
    	Master = .NOT. isnull(a5.Get_Master_Name())					'Set falg for Master Shadow situation
    	IF .NOT. ("s" $ Debug)
    		IF Master												'There is a master get its Connection Names from it
    			InstallType = "Master"	
    			FOR EACH Line in TableList							'Check connenction used for each Link Table in list
    				FileName = file.filename_parse(a5.Get_Master_Name(),"dp")+Line+".DBP"
    				IF File.Exists(FileName)
    					Temp = zLnkSetCnnct(FileName,"",Debug)
    					IF isnull(Temp)
    						error_generate("Unable to determine Named Connection")
    					ELSEIF (MstCnct <> Temp) .AND. (MstCnct  <> "")
    						error_generate("Mismatched Named Connections")
    					ELSE
    						MstCnct = Temp
    					end if
    				ELSE
    					error_generate("File missing")
    				end if
    			NEXT
    			MstSiteName = lookup(zGetTblName("a-config"),"Connectionstring =" + Quote(MstCnct),"Name")
    			IF isnull(MstSiteName)
    				IF ("u" $ Debug) 
    					error_generate("Unrecognized Named Connection: " + MstCnct)
    				ELSE
    					MstSiteName = "<< Requires Reset >>"
    				end if
    			end if
    			InstallType = "Shadow"								'We have checked master install Connections
    		ELSE													'so local is really shadow copy
    			InstallType = "Local"								'No master not a shadow copy	
    		end if
    																'Check connection for each Link Table in
    		FOR EACH Line in TableList								'Local table which can be shadowed table
    			FileName = file.filename_parse(a5.Get_Name(),"dp")+Line+".DBP"
    			IF File.Exists(FileName)
    				Temp = zLnkSetCnnct(FileName,"",Debug)
    				IF isnull(Temp)
    					error_generate("Unable to determine Named Connection")
    				ELSEIF (LclCnct <> Temp) .AND. (LclCnct  <> "")
    					error_generate("Mismatched Named Connections")
    				ELSE
    					LclCnct = Temp
    				end if
    			ELSE
    				error_generate("File missing")
    			end if
    		NEXT
    		LclSiteName = lookup(zGetTblName("a-config"),"Connectionstring =" + Quote(LclCnct),"Name")
    		IF ("u" $ Debug) .AND. isnull(LclSiteName)
    			IF ("u" $ Debug) 
    				error_generate(*concat_lines("Unrecognized Named Connection",LclCnct))
    			ELSE
    				LclSiteName = "<< Requires Reset >>"
    			end if
    		end if
    	end if
    
    	'------------------------------------
    	'// Let User Select Site or System --
    	'------------------------------------
    	DIM MstUpdate as L
    	DIM Result as C
    	DIM Connection as C
    	ConnectionList = table.external_record_content_get("a-config","Name","","type = " + Quote(SiteSys))
    	
    	Dialog =<<%dlg%
    {region}
    {wrap=80}
    If you are using a Shadow installation you can optionally update the master installation.;
    Additional shadow installations must use this same script to updated selected installation. The Refresh Shadow Installation can be used to get updates from the Master Installation. 	
    All forms using these Connections must be closed and reopened before changes take effect.
    {endregion};
    {region}
    {staticConditionalSection:1:Master}
    Shadow Installation {Insert_SiteSys}:| [%r%.40LclSiteName];
    Master Installation {Insert_SiteSys}:| [%r%.40MstSiteName];
    Update Master Installation:| (5MstUpdate);
    {end_staticConditionalSection:1}
    {staticConditionalSection:2:.NOT.Master}
    Local Installation {Insert_SiteSys}:| [%r%.40LclSiteName];
    {end_staticConditionalSection:2}
    Set Installation\(s) to {Insert_SiteSys}:| [.40,5Connection^#ConnectionList];
    {endregion};
    {line=1,0};
    {region}
    <*15&Proceed!Proceed> <15&Exit!exit>
    {endregion};
    %dlg%
    
    	DIM sr as C													'control what gets subsituted
    	sr =<<%txt%
    {Insert_SiteSys} = SiteSys
    %txt%
    	Dialog = stritran_multi_expressions(Dialog,sr) 				'Subsitute in some variables
    	
    	Code =<<%code%
    		if a_dlg_button = "proceed" then 						'Used to proceed after dialog			
    			Mess = ""
    			IF isnull(Connection) 
    			    Mess = *concat_lines(Mess,"A " + SiteSys + "Connection Must be selected")
    			end if
    			'..... more checks here ...
    			IF .NOT. isnull(Mess)
    				ui_msg_box(DialogTtl,Mess,UI_ATTENTION_SYMBOL+UI_OK)
    				a_dlg_button = ""
    			end if
    		end if
    	%code%
    	
    	Result = ui_dlg_box(DialogTtl,Dialog,Code)					'present Dialog
    	if Result <>"Proceed" then									'do we proceed
    	    end
    	end if
    			
    	'-----------------------------
    	'// Update Named Connection --
    	'-----------------------------
    	InstallType = "Both"
    	FileName = ""
    	NewConnection = alltrim(lookup(zGetTblName("a-config"),"Name =" + Quote(Connection),"Connectionstring"))
    	IF isnull(NewConnection)
    		error_generate(*concat_lines("Unrecognized Connection selected",Connection))
    	end if
    	
    	
    	IF Master .AND. MstUpdate								'Do we updtae Named Connections in master files
    		InstallType = "Master"	
    		FOR EACH Line in TableList							'Update Name Connection in each file
    			FileName = file.filename_parse(a5.Get_Master_Name(),"dp")+Line+".DBP"
    			IF File.Exists(FileName)
    				Temp = zLnkSetCnnct(FileName,NewConnection,Debug)
    				IF isnull(Temp)
    					error_generate("Unable to set new Named Connection")
    				end if
    			ELSE
    				error_generate("File missing")
    			end if
    		NEXT
    		InstallType = "Shadow"								'We have updated master install Named Connections
    	ELSE													'So local is really shadow copy
    		InstallType = "Local"								'No master not a shadow copy but just local
    	end if
    															'Update Named Connection in each 
    	FOR EACH Line in TableList								'Local table which can be shadowed table
    		FileName = file.filename_parse(a5.Get_Name(),"dp")+Line+".DBP"
    		IF File.Exists(FileName)
    			Temp = zLnkSetCnnct(FileName,NewConnection,Debug)
    			IF isnull(Temp)
    				error_generate("Unable to set new Named Connection")
    			end if
    		ELSE
    			error_generate("File missing")
    		end if
    	NEXT
    			
    END FUNCTION
    'FOR DEBUGGING
    'END
    '----------------------
    '// Support Function --
    '----------------------
    'Locate and optionally replace Named Connection in Active Link Table
    'Debug			- Debug flag set in calling script/function
    'FileName		- Name of file must include full path and .DBP extension
    'NewConnection	- Optional new Named Connection to install if null just return existing named connection
    'RETURNS		- Existing named connection NULL if unable to determine Named Connection
    '
    'When debugging scripts that use String Scanner Objects 
    ' the following watch variable is very useful
    'SS.GetToOffset() + "*" + SS.GetRemainder()
    
    FUNCTION CnctName as C(Debug as C, FileName as C, NewConnection as C = "")
    	DIM txt as C
    	DIM SS as P
    	txt = GET_FROM_FILE(FileName)
    	SS = stringscanner.create(txt)
    	IF SS.SkiptoString("<ConnectionString=\"::Name::")
    		SS.ScanOverSmatch("<ConnectionString=\"::Name::")
    		CnctName = SS.ScanToString("\"")
    		IF .NOT.(isnull(CnctName).OR.isnull(NewConnection))		'Do we place in a new Named Connection
    			IF ("t" $ Debug)									'Trace the Connection line before rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Orginal Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.SkipOver(-1 * LEN(CnctName))						'Move back over the Named Connection found
    			SS.Replace(NewConnection,LEN(CnctName))				'New Named Connection replaced charcters
    			IF ("t" $ Debug)									'Trace the Connection line after rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Altered Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.Reset()											'Get the modified file and rewrite it
    			txt = SS.GetRemainder()
    			SAVE_TO_FILE(txt,FileName,.F.)
    		end if	
    	ELSE
    		CnctName = ""
    	end if
    END FUNCTION
    
    
    '-------------------
    '// ERROR Handler --
    '-------------------
    ERRORHANDLER:
    ON ERROR GOTO 0													'No more special error traps that continue the code
    
    Mess =<<%txt%
    ERROR
    {error_text_get()}
    FileName: {FileName}
    Install Type: {InstallType}
    Script: {error_script_get()}
    Line: {error_line_number_get()}
    %txt%
    Mess = Evaluate_String(Mess)
    ui_msg_box(DialogTtl,Mess,UI_ATTENTION_SYMBOL+UI_OK)
    zLogAdd(Mess)
    END
    The named connection strings can are edited in the Alpha5 enviroment they can also be edited in runtime using the following function

    a5_ado_connectionstrings("","cs")

  8. #8
    Member
    Real Name
    Paul Verboom
    Join Date
    Apr 2006
    Location
    Halifax, Nova Scotia, Canada
    Posts
    135

    Default Re: Forms loading off a sql set - how do I edit named connection string

    I just looked at my previous posting from my desktop development machine. I really shouldn't try to answer question from my tablet I always get something wrong. Please disregard my previous post and lets try again.

    Active link tables can be defined with all the the SQL connections details (Ad hoc Connection String) OR using a Named Connection String

    Named Connection String can be defined from the Control Panel via Tools -> External Databases -> AlphaDAO Connection strings

    The end user can define Connection strings using the same dialog just provide a menu option or button that executes the following statement;

    a5_ado_connectionstrings("","cs")

    This also works with the runtime.

    If you wish to give the end user the ability to select a named connection string that can easily be done with a xDialog and using the following function to get a list of connections

    a5_getsavedadoconnections("","AlphaDAO")

    Finally the following function will update the connection string used by a Active Link table. The table must however be defined with a Named Connection String.

    Code:
    '---------
    'zLnkSetCnnct
    '---------
    'Locate and optionally replace Named Connection in Active Link Table
    '
    'Uses string scanner Object to edit the Active Link DBP file
    'The DBP file appears to be XML but it has multiline elements that 
    ' are not supported by the A5 XML parser
    'When debugging scripts that use String Scanner Objects 
    ' the following watch variable is very useful
    'SS.GetToOffset() + "*" + SS.GetRemainder()
    '
    'FileName		- Name of file must include full path and .DBP extension
    '					Path is not adjusted in function as path is dependant on master or shadow installs etc
    '					Extension is verified to prevent unintentional modification of other file types
    'NewConnection	- Optional new Named Connection to install if null just return existing named connection 
    '					Optional default null and no new connection set
    'Debug			- Debug flag set in calling script/function 
    '					Optional default no debug
    '					w = do not alter DBP file
    '					t = write to trace window the changes
    'RETURNS		- Existing named connection NULL if unable to determine Named Connection
    
    FUNCTION zLnkSetCnnct AS C (Filename AS C, NewConnection AS C = "", Debug as C = "")
    	DIM txt as C
    	DIM SS as P
    	DIM ConnectName as C										
    	IF right(FileName,4) <> ".DBP"
    		EXIT FUNCTION
    	end if
    	txt = GET_FROM_FILE(FileName)										'Get the text of the file into a string scanner object
    	SS = stringscanner.create(txt)
    	IF SS.SkiptoString("<ConnectionString=\"::Name::")
    		SS.ScanOverSmatch("<ConnectionString=\"::Name::")
    		ConnectName = SS.ScanToString("\"")
    		IF .NOT.(isnull(ConnectName).OR.isnull(NewConnection))	'Do we place in a new Named Connection
    			IF ("t" $ Debug)									'Trace the Connection line before rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Orginal Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.SkipOver(-1 * LEN(ConnectName))					'Move back over the Named Connection found
    			SS.Replace(NewConnection,LEN(ConnectName))			'New Named Connection replaced charcters
    			IF ("t" $ Debug)									'Trace the Connection line after rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Altered Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			IF .NOT. ("w" $ Debug)								'Get the modified file and rewrite it
    				SS.Reset()										'unless the debug option to not do this is turned on
    				txt = SS.GetRemainder()
    				SAVE_TO_FILE(txt,FileName,.F.)
    			end if
    		end if
    		zLnkSetCnnct = ConnectName	
    	ELSE
    		zLnkSetCnnct = ""
    	end if
    END FUNCTION
    If you are using shadow tables the situation gets more complicated. I won't get into the details of how that can be handled but I will provide the following code for anyone wishing to address this, to puzzle over. Unlike the previous function it is very implementation specific and would need much editing before it could be used.

    Code:
    '------------
    'zLNKadjust
    '------------
    'Function to adjust the NAMED CONNECTION string in a list of Active Link Tables.
    'Tables must be defined with a named connection when creating the Active link tables
    'Script searches the *.DBP file for the following string and updates it.
    '<ConnectionString="::Name::NSES_Test_Site1">
    'The path to the files are hard coded in the *.DBP file but do not appear to be consulted
    '
    'DialogTtl 	- Title for Dialogs produced by this function reflects what Active Line Tables are being adjusted
    'TableList	- List of Active Link Tables to Update Named Connection In
    'SiteSys	- The Connection Type Site or System that Active Link Tables are connecting to
    'RETURNS	- nothing and errors are handled by this function, calling function is just expected to provide list of tables
    
    FUNCTION zLNKadjust AS C (DialogTtl as C, TableList AS C, SiteSys as C)
    'FOR DEBUGGING
    'DialogTtl = "Set ADT edit Site (ADT-EditSite)"
    'TableList =<<%txt%
    'l_hisorders
    'l_hispatients
    'l_hisvisits
    '%txt% 
    'SiteSys = "Site"
    
    	'---------------------
    	'// Set Debug Flags --
    	'---------------------
    	DIM Debug as C = "t" 											'use following statement to check for debug flag: if ("m" $ Debug)
    	DbgSet(type::xbasicmodule.get_current(),Debug)					'set debug values in deployed enviroments
    	's - skip checking existing Named Connections, used force reset of Named Connections
    	'u - Error out if Named Connection is not recognised NOT recommended once deployed
    	't - trace out the Connect string lines as it is altered
    	'w - skip rewritting the Altered .DBP files
    
    	'------------------------------
    	'// Set up for Error handler --
    	'------------------------------
    	ON ERROR GOTO ERRORHANDLER
    	
    	'------------------------------------
    	'// Get existing Named Connections --
    	'------------------------------------
    	'Section gets the Named connection used on the Local or Shadowed 
    	'and Master copies of Tables if they exist does sveral checks to verify
    	'Named Connections are consistant and exist.
    	
    	DIM LclCnct as C											'Connection used by this Local installation
    	DIM Master as L = .F.										'Master copy and Shadow copy situation	
    	DIM MstCnct as C											'Connection used in MAster table if Shadowed install
    	DIM FileName as C											'The Link Table Data Base Pointer File
    	DIM InstallType as C										'Installation Connection being adjusted in
    	DIM Temp as C												'Temporary string		
    	
    	Master = .NOT. isnull(a5.Get_Master_Name())					'Set flag for Master Shadow situation
    	IF .NOT. ("s" $ Debug)
    		IF Master												'There is a master get its Connection Names from it
    			InstallType = "Master"	
    			FOR EACH Line in TableList							'Check connenction used for each Link Table in list
    				FileName = file.filename_parse(a5.Get_Master_Name(),"dp")+Line+".DBP"
    				IF File.Exists(FileName)
    					Temp = zLnkSetCnnct(FileName,"",Debug)
    					IF isnull(Temp)
    						error_generate("Unable to determine Named Connection")
    					ELSEIF (MstCnct <> Temp) .AND. (MstCnct  <> "")
    						error_generate("Mismatched Named Connections")
    					ELSE
    						MstCnct = Temp
    					end if
    				ELSE
    					error_generate("File missing")
    				end if
    			NEXT
    			MstSiteName = lookup(zGetTblName("a-config"),"Connectionstring =" + Quote(MstCnct),"Name")
    			IF isnull(MstSiteName)
    				IF ("u" $ Debug) 
    					error_generate("Unrecognized Named Connection: " + MstCnct)
    				ELSE
    					MstSiteName = "<< Requires Reset >>"
    				end if
    			end if
    			InstallType = "Shadow"								'We have checked master install Connections
    		ELSE													'so local is really shadow copy
    			InstallType = "Local"								'No master not a shadow copy	
    		end if
    																'Check connection for each Link Table in
    		FOR EACH Line in TableList								'Local table which can be shadowed table
    			FileName = file.filename_parse(a5.Get_Name(),"dp")+Line+".DBP"
    			IF File.Exists(FileName)
    				Temp = zLnkSetCnnct(FileName,"",Debug)
    				IF isnull(Temp)
    					error_generate("Unable to determine Named Connection")
    				ELSEIF (LclCnct <> Temp) .AND. (LclCnct  <> "")
    					error_generate("Mismatched Named Connections")
    				ELSE
    					LclCnct = Temp
    				end if
    			ELSE
    				error_generate("File missing")
    			end if
    		NEXT
    		LclSiteName = lookup(zGetTblName("a-config"),"Connectionstring =" + Quote(LclCnct),"Name")
    		IF ("u" $ Debug) .AND. isnull(LclSiteName)
    			IF ("u" $ Debug) 
    				error_generate(*concat_lines("Unrecognized Named Connection",LclCnct))
    			ELSE
    				LclSiteName = "<< Requires Reset >>"
    			end if
    		end if
    	end if
    
    	'------------------------------------
    	'// Let User Select Site or System --
    	'------------------------------------
    	DIM MstUpdate as L
    	DIM Result as C
    	DIM Connection as C
    	ConnectionList = table.external_record_content_get("a-config","Name","","type = " + Quote(SiteSys))
    	
    	Dialog =<<%dlg%
    {region}
    {wrap=80}
    If you are using a Shadow installation you can optionally update the master installation.;
    Additional shadow installations must use this same script to updated selected installation. The Refresh Shadow Installation can be used to get updates from the Master Installation. 	
    All forms using these Connections must be closed and reopened before changes take effect.
    {endregion};
    {region}
    {staticConditionalSection:1:Master}
    Shadow Installation {Insert_SiteSys}:| [%r%.40LclSiteName];
    Master Installation {Insert_SiteSys}:| [%r%.40MstSiteName];
    Update Master Installation:| (5MstUpdate);
    {end_staticConditionalSection:1}
    {staticConditionalSection:2:.NOT.Master}
    Local Installation {Insert_SiteSys}:| [%r%.40LclSiteName];
    {end_staticConditionalSection:2}
    Set Installation\(s) to {Insert_SiteSys}:| [.40,5Connection^#ConnectionList];
    {endregion};
    {line=1,0};
    {region}
    <*15&Proceed!Proceed> <15&Exit!exit>
    {endregion};
    %dlg%
    
    	DIM sr as C													'control what gets subsituted
    	sr =<<%txt%
    {Insert_SiteSys} = SiteSys
    %txt%
    	Dialog = stritran_multi_expressions(Dialog,sr) 				'Subsitute in some variables
    	
    	Code =<<%code%
    		if a_dlg_button = "proceed" then 						'Used to proceed after dialog			
    			Mess = ""
    			IF isnull(Connection) 
    			    Mess = *concat_lines(Mess,"A " + SiteSys + "Connection Must be selected")
    			end if
    			'..... more checks here ...
    			IF .NOT. isnull(Mess)
    				ui_msg_box(DialogTtl,Mess,UI_ATTENTION_SYMBOL+UI_OK)
    				a_dlg_button = ""
    			end if
    		end if
    	%code%
    	
    	Result = ui_dlg_box(DialogTtl,Dialog,Code)					'present Dialog
    	if Result <>"Proceed" then									'do we proceed
    	    end
    	end if
    			
    	'-----------------------------
    	'// Update Named Connection --
    	'-----------------------------
    	InstallType = "Both"
    	FileName = ""
    	NewConnection = alltrim(lookup(zGetTblName("a-config"),"Name =" + Quote(Connection),"Connectionstring"))
    	IF isnull(NewConnection)
    		error_generate(*concat_lines("Unrecognized Connection selected",Connection))
    	end if
    	
    	
    	IF Master .AND. MstUpdate								'Do we update Named Connections in master files
    		InstallType = "Master"	
    		FOR EACH Line in TableList							'Update Name Connection in each file
    			FileName = file.filename_parse(a5.Get_Master_Name(),"dp")+Line+".DBP"
    			IF File.Exists(FileName)
    				Temp = zLnkSetCnnct(FileName,NewConnection,Debug)
    				IF isnull(Temp)
    					error_generate("Unable to set new Named Connection")
    				end if
    			ELSE
    				error_generate("File missing")
    			end if
    		NEXT
    		InstallType = "Shadow"								'We have updated master install Named Connections
    	ELSE													'So local is really shadow copy
    		InstallType = "Local"								'No master not a shadow copy but just local
    	end if
    															'Update Named Connection in each 
    	FOR EACH Line in TableList								'Local table which can be shadowed table
    		FileName = file.filename_parse(a5.Get_Name(),"dp")+Line+".DBP"
    		IF File.Exists(FileName)
    			Temp = zLnkSetCnnct(FileName,NewConnection,Debug)
    			IF isnull(Temp)
    				error_generate("Unable to set new Named Connection")
    			end if
    		ELSE
    			error_generate("File missing")
    		end if
    	NEXT
    			
    END FUNCTION
    'FOR DEBUGGING
    'END
    '----------------------
    '// Support Function --
    '----------------------
    'Locate and optionally replace Named Connection in Active Link Table
    'Debug			- Debug flag set in calling script/function
    'FileName		- Name of file must include full path and .DBP extension
    'NewConnection	- Optional new Named Connection to install if null just return existing named connection
    'RETURNS		- Existing named connection NULL if unable to determine Named Connection
    '
    'When debugging scripts that use String Scanner Objects 
    ' the following watch variable is very useful
    'SS.GetToOffset() + "*" + SS.GetRemainder()
    
    FUNCTION CnctName as C(Debug as C, FileName as C, NewConnection as C = "")
    	DIM txt as C
    	DIM SS as P
    	txt = GET_FROM_FILE(FileName)
    	SS = stringscanner.create(txt)
    	IF SS.SkiptoString("<ConnectionString=\"::Name::")
    		SS.ScanOverSmatch("<ConnectionString=\"::Name::")
    		CnctName = SS.ScanToString("\"")
    		IF .NOT.(isnull(CnctName).OR.isnull(NewConnection))		'Do we place in a new Named Connection
    			IF ("t" $ Debug)									'Trace the Connection line before rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Orginal Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.SkipOver(-1 * LEN(CnctName))						'Move back over the Named Connection found
    			SS.Replace(NewConnection,LEN(CnctName))				'New Named Connection replaced charcters
    			IF ("t" $ Debug)									'Trace the Connection line after rename	
    				zTraceStamp(type::xbasicmodule.get_current())
    				trace.WriteLn("File: " + FileName)
    				trace.WriteLn("Altered Named Connection Line")
    				txt = SS.GetLineText()
    				trace.WriteLn(txt)
    			end if
    			SS.Reset()											'Get the modified file and rewrite it
    			txt = SS.GetRemainder()
    			SAVE_TO_FILE(txt,FileName,.F.)
    		end if	
    	ELSE
    		CnctName = ""
    	end if
    END FUNCTION
    
    
    '-------------------
    '// ERROR Handler --
    '-------------------
    ERRORHANDLER:
    ON ERROR GOTO 0													'No more special error traps that continue the code
    
    Mess =<<%txt%
    ERROR
    {error_text_get()}
    FileName: {FileName}
    Install Type: {InstallType}
    Script: {error_script_get()}
    Line: {error_line_number_get()}
    %txt%
    Mess = Evaluate_String(Mess)
    ui_msg_box(DialogTtl,Mess,UI_ATTENTION_SYMBOL+UI_OK)
    zLogAdd(Mess)
    END

  9. #9
    Member
    Real Name
    Bob Nilles
    Join Date
    Feb 2010
    Posts
    21

    Default Re: Forms loading off a sql set - how do I edit named connection string

    Paul,
    Thanks Paul. I went back to the application because more interest in my program exists. I will implement your suggestions.
    Cheers.
    Bob

Similar Threads

  1. publish grid w/ different connection string from Project Named AlphaDAO connection
    By KYBiggs in forum Application Server Version 10 - Web/Browser Applications
    Replies: 4
    Last Post: 01-22-2012, 01:40 PM
  2. Error converting named connection to connection string
    By michaelxcampbell in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 09-21-2011, 03:38 PM
  3. Custom Connection String vs Named CS
    By marvinbase in forum Alpha Five Version 10 - Desktop Applications
    Replies: 1
    Last Post: 02-17-2011, 06:08 PM
  4. Named Connection String
    By TheMobileGuru in forum Alpha Five Version 10 - Desktop Applications
    Replies: 1
    Last Post: 09-23-2010, 07:20 PM
  5. Named Connection string
    By urskumark in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 11-11-2008, 07:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •