Announcement

**Isomorphic** · 29 Nov 2012, 12:28

You can use a DynamicDSGenerator (see QuickStart Guide, Server Framework chapter) to dynamically generate a SQLDataSource that has a different value for dbName.

If the set of possible DB connections is also unlimited / determined on the fly, the Config object allows you to add new definitions.

There is no need to worry about storing the SQLDataSource in the session or anything like that. They are still pooled for you.

**kiipper** · 30 Nov 2012, 07:26

Amazing!

If anybody stumbled on the same problem that I was, here is some code that might help.
I create a new DB connection on the fly using Config object everytime a user successfully login, and I copy the same values of my sql.defaultDB entry at the server.properties file.

Code:

String login = "username"; //this value is the username that was logged
String password = "password"; //this calue is the DB password

//Call [Config.initGlobalConfig()] If running outside of a servlet environment
final Config conf = Config.getGlobal(true);

String userDB = login + "_db."; //<username_db>
String userDBPrefix = "sql." + userDB; //<sql.username_db>

//Use sql.defaultDB properties from server.properties
conf.put(userDBPrefix + "driver", conf.get("sql.defaultDB.driver"));
conf.put(userDBPrefix + "driver.serverName", conf.get("sql.defaultDB.driver.serverName"));
conf.put(userDBPrefix + "driver.driverName", conf.get("sql.defaultDB.driver.driverName"));
conf.put(userDBPrefix + "driver.context", conf.get("sql.defaultDB.driver.context"));
conf.put(userDBPrefix + "driver.networkProtocol", conf.get("sql.defaultDB.driver.networkProtocol"));
conf.put(userDBPrefix + "driver.portNumber", conf.get("sql.defaultDB.driver.portNumber"));
conf.put(userDBPrefix + "driver.databaseName", conf.get("sql.defaultDB.driver.databaseName"));
conf.put(userDBPrefix + "autoJoinTransactions", conf.get("sql.defaultDB.autoJoinTransactions"));
conf.put(userDBPrefix + "interface.type", conf.get("sql.defaultDB.interface.type"));
conf.put(userDBPrefix + "database.type", conf.get("sql.defaultDB.database.type"));
conf.put(userDBPrefix + "interface.credentialsInURL", conf.get("sql.defaultDB.interface.credentialsInURL"));
conf.put(userDBPrefix + "driver.user", login);
conf.put(userDBPrefix + "driver.password", password);

The DynamicDSGenerator code:

Code:

DataSource.addDynamicDSGenerator(new DynamicDSGenerator() {
    @Override
    public DataSource getDataSource(String id, DSRequest dsRequest) {
        DataSource ds = null;
        try {
            if (dsRequest != null) {
                HttpSession session = dsRequest.getHttpServletRequest().getSession();
                String dbUser = (String) session.getAttribute(DB_USER);

                DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
                Document doc = builder.parse(dsRequest.getServletContext().getResourceAsStream("/ds/" + id + ".ds.xml"));
                doc.getDocumentElement().setAttribute("dbName", dbUser + "_db");
                ds = DataSource.fromXML(doc);
            }
        } catch (Exception ex) {
            //ex.printStackTrace();
            Logger.getLogger(getClass().getName()).error(ex.getMessage(), ex);
        }
        return ds;
    }
});

The server.properties file:

Code:

sql.defaultDB.driver: org.postgresql.Driver
sql.defaultDB.driver.serverName: <somewhere>
sql.defaultDB.autoJoinTransactions: true
sql.defaultDB.driver.driverName: postgresql
sql.defaultDB.interface.type: driverManager
sql.defaultDB.driver.databaseName: <database name>
sql.defaultDB.driver.context: 
sql.defaultDB.database.type: postgresql
sql.defaultDB.interface.credentialsInURL: true
sql.defaultDB.driver.networkProtocol: tcp
sql.defaultDB.driver.portNumber: 5432

Everything works fine, now.
Plus:
1)I'm still using the .ds.xml files to define the datasource properties (I just removed the dbName attribute because it's defined dynamically).
2)Connection pooling is handled by the framework.

Thank you for quick answer Isomorphic, it helped us a lot.

**pegasuswebservices** · 9 Jan 2014, 01:09

User Level DB instance selection

I believe the server.properties solution is an application level solution. The new db instance would be used by all users. I have a requirement for each user to select 1 of 5 database instances all being run under one database server. In this case one database schema is copied to five databases. Each database contains the data for one warehouse. So at anytime user 1 can be looking at warehouse 1 data, user 2 can be looking at warehouse 2 data, etc.

I tried using the dynamic datasource solution calling .get(dbName_DataSourceName) which should be passed into my Dynamic DataSource class as (dbname, datasourcename). I see the correct data passed in sometimes and other times I see ($datasource, null) passed in. I'm not sure how to correct the problem so I always see the (dbname, datasourcename) passed in.
Please help me understand what I am doing wrong. Thanks...

**Isomorphic** · 9 Jan 2014, 02:34

The provided solution solves the problem you describe. However with such vague descriptions of the problem you are facing, there is no way for anyone to help you.

As a starting point, be sure to provide all of the information that the forums prompts you for, every time you post.

**pegasuswebservices** · 12 Jan 2014, 19:18

I'm not finding the solution...

I have one MySQL Server containing multiple database instances in it and want to switch instances at runtime.

I have found that I could put entries in the server.properties file such as:

mysql.DatabaseInstance1....
mysql.DatabaseInstance2....
mysql.DatabaseInstance3....
mysql.DatabaseInstance4....
mysql.DatabaseInstance5....

and create 5 DS XML files containing

file1.ds.xml
dbName="DatabaseInstance1"

file2.ds.xml
dbName="DatabaseInstance2"

file3.ds.xml
dbName="DatabaseInstance3"

file4.ds.xml
dbName="DatabaseInstance4"

file5.ds.xml
dbName="DatabaseInstance5"

and use setDataSource(fiel1-5)

at every ListGrid in the system, but with 18 ListGrid Components and 5 database instances I would need to use 5 * 18 ds xml files(90 files).

I am looking for a good example using the Dynamic Source Generator and have it just change the DatabaseInstance name...Is there such an example?

-- Jeff

**Isomorphic** · 14 Jan 2014, 14:09

Um.. yes there is such a sample. Look up (post #3 in this thread).

**kiipper** · 11 Mar 2014, 12:23

Is Field type 'leaking' in the datasource id parameter?

Bug details:
1. SmartClient Version: v9.1p_2014-03-11/PowerEdition Deployment (built 2014-03-11)
2. any browser (it's a server side problem)
3. Server side Exception:

Code:

java.lang.IllegalArgumentException: InputStream cannot be null
	at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:117)
	at org.microcodigo.server.CustomIDACall$1.getDataSource(CustomIDACall.java:152)
	at com.isomorphic.datasource.DataSource.getDynamicDataSource(DataSource.java:392)
	at com.isomorphic.datasource.DataSource.forName(DataSource.java:228)
	at com.isomorphic.datasource.DataSource.forName(DataSource.java:222)
	at com.isomorphic.datasource.DataSource.forName(DataSource.java:211)
	at com.isomorphic.datasource.PoolableDataSourceFactory.makeUnpooledObject(PoolableDataSourceFactory.java:130)
	at com.isomorphic.pool.PoolManager.borrowUnpooledObject(PoolManager.java:123)
	at com.isomorphic.datasource.DataSourceManager.getDataSource(DataSourceManager.java:93)
	at com.isomorphic.datasource.ValidationContext.getType(ValidationContext.java:299)
	at com.isomorphic.datasource.ValidationContext.getType(ValidationContext.java:279)
	at com.isomorphic.datasource.DataSource.getProperties(DataSource.java:2340)
	at com.isomorphic.datasource.DataSourceBeanFilter.filter(DataSourceBeanFilter.java:204)
	at com.isomorphic.js.JSTranslater.convertMap(JSTranslater.java:1162)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:761)
	at com.isomorphic.js.JSTranslater.convertIterator(JSTranslater.java:1373)
	at com.isomorphic.js.JSTranslater.convertCollection(JSTranslater.java:1324)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:763)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:699)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:690)
	at com.isomorphic.js.JSTranslater.convertMap(JSTranslater.java:1211)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:761)
	at com.isomorphic.js.JSTranslater.convertIterator(JSTranslater.java:1373)
	at com.isomorphic.js.JSTranslater.convertCollection(JSTranslater.java:1324)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:763)
	at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:699)
	at com.isomorphic.js.JSTranslater.toJS(JSTranslater.java:661)
	at com.isomorphic.rpc.RPCManager.completeResponse(RPCManager.java:1496)
	at com.isomorphic.rpc.RPCManager.send(RPCManager.java:623)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:172)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:137)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:409)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1044)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:722)

4. RPC is working fine
5. No Javascript Errors
6. Sample code: Look up (post #3 in this thread)

First, thank you for improving the code at post #3.
(see thread: http://forums.smartclient.com/showthread.php?t=29555)
Second, the exception I'm picking here does not 'break' my project, it only happen when we use a datasource (ds.xml) file with a localeFloat field.
For example:

Code:

<DataSource ID="sometablename" serverType="sql" dataSourceVersion="1">
    <fields>
        <field name="primkey" type="sequence" title="C�digo" primaryKey="true">
        </field>
        <field name="somedate" type="date">
        </field>
        <field name="imabuggyfield" type="localeFloat" title="Field with Locale">
        </field>
        <field name="anotherfield" type="text" length="2000" title="Texto">
        </field>
    </fields>
    <allowAdvancedCriteria>true</allowAdvancedCriteria>
</DataSource>

Still, even after the exception, my the Listgrid or DynamicForm used does render and save the localeFloat field with the right pattern for the locale (de, pt_BR, etc.)

What I have found, while debugging the code, is that the id parameter value of the method below is set with the String "localeFloat":

Code:

DataSource.addDynamicDSGenerator(new DynamicDSGenerator() {
    @Override
    public DataSource getDataSource(String id, DSRequest dsRequest) {
        if (id.equals("localeFloat")) {
            System.out.println("Should the id be 'localeFloat'?");
        }

        DataSource ds = null;
        try {
            if (dsRequest != null) {
                HttpSession session = dsRequest.getHttpServletRequest().getSession();
                String dbUser = (String) session.getAttribute(DB_USER);

                DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
                Document doc = builder.parse(dsRequest.getServletContext().getResourceAsStream("/ds/" + id + ".ds.xml"));
                doc.getDocumentElement().setAttribute("dbName", dbUser + "_db");
                ds = DataSource.fromXML(doc);
            }
        } catch (Exception ex) {
            //ex.printStackTrace();
            Logger.getLogger(getClass().getName()).error(ex.getMessage(), ex);
        }
        return ds;
    }
});

I understand that this is a complex (and somewhat challenging) error because it includes Dynamic DataSource and Locales.
I just want to know if you guys can reproduce this behavior?
If so, does it make sense to look for a datasource id named "localeFloat"?

**Isomorphic** · 11 Mar 2014, 12:26

Never mind "localeFloat" in particular - if you have a DynamicDSGenerator you can't assume that every ID you are asked for actually exists as an XML file on disk.

Often the system is attempting to find out whether there is a DataSource with a given ID.

Announcement

Questions about SQLDataSource (server-side) and Multiple users

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment