Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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, 10:56 AM.
    There can be only one.

    Comment


    • #3
      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

      Comment


      • #4
        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?

        Comment


        • #5
          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!!

          Comment


          • #6
            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.

            Comment


            • #7
              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")

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    All,

                    I finally stumbled across the fix.
                    Here is what I did....


                    Set up a global variable that I set on default screen with the field containing the sqlserver name. The variable is GServer.


                    GSeamLess_cnstring = "{A5API='SQLServer',A5DBVersion=2012,Server='" + Gserver + "',UserName='SA',Password='豱',Database='seamless'}"



                    a5_NamedConnectionCreate("SeamLess",GSeamLess_cnstring)


                    This function is in an Alpha5 website somewhere was listed definitely not in any manual.

                    The connection string is updated in my application and is very easy to place on other systems with varying sql server names.

                    I think I have been working on this problem off and on for 4 years.

                    Cheers
                    Bob

                    Comment

                    Working...
                    X