1. Marc James
  2. PowerServer Mobile (Obsolete)
  3. Monday, 18 May 2020 15:44 PM UTC

We have a customer that hosts site data on different databases.

When they login to the app, I would like the first prompt to be site selection, which will then determine that database the app will run on.

Is this possible? As when compiling I only see I can choose one database?

Accepted Answer
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 19 May 2020 02:25 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # Permalink

Hi,
Hi,

Appeon supports Dynamic transaction object to data source mapping, you can create multiple data sources to connect different database. Please refer to the following steps:
1. Add data source pointing to different database in AEM;
2. Follow below online help documentation to setup transaction object to data source mapping using dynamic transaction in Appeon Web Application rather than doing it in AEM.
https://www.appeon.com/support/documents/appeon_online_help/2017/server_configuration_guide_for_net/Dynamic_transaction_object_to_connection_cache_mapping.html

You can modify code like SQLCA.DBParm="CacheName='ASADataSource1'" to connect different data sources.
ASADataSource1 is a name of data source.

For example(please note the DBMS of ASE is ODB-SYC):

SQLCA.DBMS = "ODB-SYC"
SQLCA.AutoCommit = False

Choose Case db_name
Case 'PROD'
SQLCA.DBParm="CacheName='appeonsample'"
Case 'DEV'
SQLCA.DBParm="CacheName='appeonsample2'"
End Choose


And you can enable the Dynamic Database Connection option in AEM> Server> Resources> Data Source>[data source name] and specify the user name and password to connect to the database. More details please refer to the Appeon Help > Appeon Server Configuration Guide for .NET> AEM User Guide> Server> Resources> Data Source> Adding a data source section or the online help at:
http://www.appeon.com/support/documents/appeon_online_help/2015/server_configuration_guide_for_net/ch04s03s03.html#Adding_connection_cache

Then you can use the script like below to specify the user/password to connect to the database.
SQLCA.LogId = %your id%
SQLCA.LogPass=%your pass%

Just for your reference, when the Dynamic Database Connection feature is turned on the LogID and LogPass of the Transaction object will be used to connect to the database; when it is turned off then the user name and password specified in the data source will be used to connect to the database.

Regards,
ZhaoKai

 

Comment
There are no comments made yet.
Marc James Accepted Answer Pending Moderation
  1. Tuesday, 19 May 2020 12:46 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 1

Okay - it's working, but only when run locally - on my mobile phone I get the following issue (screenshot no.1)

connection:CacheName='quarry'

Connection cache name cannot be null.

The user selects the industry type, then I put that into a global string to do a choose case to determine database connection parameters (see below).

choose case gs_industry
			
	case "Quarry"
		sqlca.DBMS			= "ODB-ASA"
		sqlca.userid			= "dba"
		sqlca.dbpass		= "sql"
		sqlca.database		= "jpe"
		sqlca.dbparm		= "CacheName='quarry' ConnectString='DSN=jpe;UID=dba;PWD=sql'"
		
	case "Waste"
		sqlca.DBMS			= "ODB-ASA"
		sqlca.userid			= "dba"
		sqlca.dbpass		= "sql"
		sqlca.database		= "regen"
		sqlca.dbparm		= "CacheName='waste' ConnectString='DSN=regen;UID=dba;PWD=sql'"
			
			
	case else 
		return
		
end choose

I then have in my AEM the two data objects (see screenshot 2)

You'll also see I have my sql anywhere 16 data sources tested and working fine (see screenshot 3)

After I choose my case, I then do a CONNECT USING sqlca; and select the username from the database.

CONNECT using sqlca;
IF sqlca.sqlcode = 0 THEN
	SELECT string1 INTO :ls_name FROM base WHERE file = -21 AND code = :ls_code USING sqlca;
	st_name.text = ls_name
ELSE
	MessageBox("ERROR:database connection:" + sqlca.dbparm, sqlca.sqlerrtext)
END IF
DISCONNECT using sqlca;

This is now working when I run locally, I know I am able to collect a unique username in each database depending on which industry I choose.

Attachments (3)
Comment
  1. mike S
  2. Tuesday, 19 May 2020 14:04 PM UTC
In AEM, If you go to resources then data source, then you can add/maintain the datasources there.

jpe is the datasource name, which is what you use for the cachename.



glad you got it working!





  1. Helpful
  1. Roland Smith
  2. Tuesday, 19 May 2020 14:46 PM UTC
Shouldn't there be a comma or semi-colon after the cachename?
  1. Helpful
  1. Marc James
  2. Tuesday, 19 May 2020 15:02 PM UTC
All is working at the moment spot on with the below



sqlca.dbparm = "CacheName='jpe' ConnectString='DSN=jpe;UID=dba;PWD=sql'"



So I guess not?
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 18 May 2020 22:58 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 2

Marc -

   You could also host the names of the four databases in an INI file, as was mentioned above. The INI file is deployed with your application and is accessed as the logon window is being built. There are now three fields in the LOGON window, the bottom one being a dddw that displays the available databases.

   This option is FAR easier to code and maintain, and since hte users still need to know their credentials for each database, the security is not really an issue.


Good Luck,

Olan

Comment
  1. mike S
  2. Tuesday, 19 May 2020 12:20 PM UTC
actually, if the databases are not changing, there is no real reason not to simply have a drop down list and the database stuff just hardcoded.
  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Monday, 18 May 2020 18:54 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 3

for my client server app i store the server/database connection information in an ini file (for easy copy between workstations).  each database server has a name that allows the user to easily know what it is.  This info can be saved in the registry or yet another database if you prefer.

anyway, the user selects the database they want to connect to in the login screen, along with username/password, the system looks up the connection information (server, database, port, driver, etc).  So, the databases can be on different servers and can even be different (sql server vs oracle vs sql anywhere vs ase vs postgresql) etc.

 

Comment
  1. Marc James
  2. Monday, 18 May 2020 22:18 PM UTC
Hi Mike, when you refer to workstations, do you mean mobile phones?
  1. Helpful
  1. mike S
  2. Monday, 18 May 2020 22:24 PM UTC
sorry, didn't see it say powerSERVER in the group. i wish it was clearer which product area.



what i do for powerserver is use a table as a directory. log in to that again with an id to indicate the database, then use that information to connect. again, it can be any server, any database.



what i do is have the site connection id, username, and password on the login screen. connect to the directory and verify that the connection id is valid, and then change to the indicated database and use the login/password to verify that the user can connect there.
  1. Helpful
  1. mike S
  2. Monday, 18 May 2020 22:26 PM UTC
to be clear, the directory table has the id that the user selects, as well as the database connection information.
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Monday, 18 May 2020 15:50 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 4

The only reason you need a database connection for compile is the syntax check.

Many in your situation will have a 'credentials' database that only has a few tables that identify users. This would include the name of the database that the application will work from.

The login window would connect to the credentials database to find out what application database to use. It would then disconnect from credentials and connect to application.

Comment
  1. Roland Smith
  2. Monday, 18 May 2020 15:52 PM UTC
And if the user is allowed more than one application database, they could be given a choice.
  1. Helpful
  1. Marc James
  2. Monday, 18 May 2020 16:07 PM UTC
Thanks for the quick reply - so I could essentially connect to a simple 1 table database that holds their 4 sites with the connection profile strings (ODBC - sybase central database). So then as soon as they have selected 1 of the 4 sites, it disconnects from that first database, and just connects a new one (ODBC)?
  1. Helpful
  1. Marc James
  2. Monday, 18 May 2020 16:10 PM UTC
The user table will be held against each sites own database
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.