How to handle a BO for different database?

StrataFrame Forum

Back

How to handle a BO for different database?

Author	Message
Edhy Rijo	E Edhy Rijo posted 14 Years Ago Topic Details Share Topic
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	I don't know if this is possible or not with the BO, but I need to design an application that can be used with different database like VistaDB and MS-SQL. The idea is that there will be customers that will use VistaDB mostly for single users and others will use MS-SQL for multiple users. Can a BO be setup to connect to either database? How? P.S. My understanding is that VistaDB database is pretty much compatible with MS-SQL TSQL language, they do not support all the features of MS-SQL of course, but at least it looks like they support all the standard features and if that is the case, then it may work for my project. Edhy Rijo
	Reply Like 0
Greg McGuffey	Greg McGuffey posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 2K, Visits: 6.6K	Assuming you can name all the tables and columns the same, it should be pretty easy. You'd just need to have some sort of configuration option that indicated which type to use, then simply setup the correct datasource using the same datasource key. The tricky part could be in any SQL you have in the app (like for fill methods). If there were differences, you'd have to handle those. If you're app is pretty straight forward and you're mostly doing straight up selects, it will likely be easy. Taking a look at VistaDb, I'm thinking that if you do your initial development against VistaDb, you'll likely be fine as it sounds like all of VistaDb's commands are in T-SQL, but not the other way around.
	Reply Like 0
Charles R Hankey	Charles R Hankey posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 524, Visits: 30K	Seems this would require the BO Mapper to be pointed to DDT for VistaDB, no? which you'd manage by hand but use for SQL Express ? Or will BO Mapper allow you to see a VistaDB database if your connection string works?
	Reply Like 0
Greg McGuffey	Greg McGuffey posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 2K, Visits: 6.6K	I'm guessing that the ODBC connection would work in BO mapper with VistaDB. However, you could always just use the SQL Server version to get the mapping done, since they have to have the same table/column names. As to deployment, DDT would work for the SQL server variety, but I believe you're on your own for VistaDb.
	Reply Like 0
Edhy Rijo	E Edhy Rijo posted 14 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Greg, The data structures will be the same. The idea is that for single users to use VistaDB and for multi users then use MS-SQL Express. I use the DDT for all my data design to take advantage of updating customer structure and data with DDT. I have not try VistaDB yet, but will do so next week. I also need to check if the RBS will work with VistaDB. I will keep all my SQL commands as simple as possible to keep avoid database incompatibility, so will try to test everything in VistaDB first. I have many single users with this application in VFP and VistaDB looks like a good alternative to MS-SQL in this case. Edhy Rijo
	Reply Like 0
Edhy Rijo	E Edhy Rijo posted 14 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Charles R Hankey (04/19/2010) Seems this would require the BO Mapper to be pointed to DDT for VistaDB, no? which you'd manage by hand but use for SQL Express ? Or will BO Mapper allow you to see a VistaDB database if your connection string works? I only hope I could use the DDT for VistaDB since all my BOs are mapped to the DDT excepts for views which could be a problem between VistaDB and MS-SQL. Will start testing next week with VistaDB and would like to hear from Trent or Dustin about any possible issues with this scenario and RBS compatibility issues. Edhy Rijo
	Reply Like 0
Charles R Hankey	Charles R Hankey posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 524, Visits: 30K	I'll bet you won't be able to use DDT with Vista DB unless it exposes an SMO object model that is identical to SQL Server and that seems really unlikely. But the rest of it sounds pretty smooth. (don't know about RBS of course)
	Reply Like 0
Dustin Taylor	Dustin Taylor posted 14 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 364, Visits: 771	Charles is right on (seems like I've been saying that a lot lately ) Your BOs should be no problem at all. As Greg mentioned, you will just be dealing with your DataSourceKey, the BO logic shouldn't be affected, especially if you write your Queries to be compatible with both MS-SQL and VistaDB from the get-go. The DDT, however, will not be able to deploy to VistaDB, and the RBS would be sketchy at best. The best option for your VistaDB boxes may be to put RBS on it's own SQLExpress instance. That way you don't have to worry about compatibility, and the RBS won't hit any size limits like you may run into with your main APP database. If you did take that approach, I would probably go ahead and seperate out your RBS tables into a seperate DB for all of your installations, regardless of whether they use VistaDB or MS-SQL. That way, you don't have to account for anything fancy on the security side of things, it will always be it's own DataSourceKey, which you set up once and, afterwhich, can rely on. If you end up running into performance issues by having two connections to the same DB open for each MS-SQL client, we can re-evaluate that, but I think I'd design it simple up front, and then only add extra complication if it proves necessary after testing. From there, the only thing you have to account for is deploying your VistaDB, and keeping track of which datasource to use for your BOs (which wouldn't be terribly hard to account for using an inherited BaseBO.)
	Reply Like 0
Edhy Rijo	E Edhy Rijo posted 14 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Dustin Taylor (04/20/2010) Charles is right on (seems like I've been saying that a lot lately ) Agree, Charles is just getting better and better. Your BOs should be no problem at all. As Greg mentioned, you will just be dealing with your DataSourceKey, the BO logic shouldn't be affected, especially if you write your Queries to be compatible with both MS-SQL and VistaDB from the get-go. Good to know will test that next week. The DDT, however,will not be able to deploy to VistaDB, and the RBS would be sketchy at best. The best option for your VistaDB boxes may be to put RBS on it's own SQLExpress instance. That way you don't have to worry about compatibility, and the RBS won't hit any size limits like you may run into with your main APP database. If you did take that approach, I would probably go ahead and seperate out your RBS tables into a seperate DB for all of your installations, regardless of whether they use VistaDB or MS-SQL. That way, you don't have to account for anything fancy on the security side of things, it will always be it's own DataSourceKey, which you set up once and, afterwhich, can rely on. If you end up running into performance issues by having two connections to the same DB open for each MS-SQL client, we can re-evaluate that, but I think I'd design it simple up front, and then only add extra complication if it proves necessary after testing. From there, the only thing you have to account for is deploying your VistaDB, and keeping track of which datasource to use for your BOs (which wouldn't be terribly hard to account for using an inherited BaseBO.) I understand and that is the reason we are asking you guys to add support for those databases or at least some sort of open source hook where we can make the tools works with other database. In my case, it will be impossible to install MS-SQL express on some customer computers, SQL-Express setup will not even install and unfortunately for this application that is the majority of customers, some customers have decent hardware with one or more branch office, for those MS-SQL Express will work just fine. About RBS I hope the structure and logic used in SF could be use in VistaDB, so after testing I will let you guys know what i find. Hopefully the overhaul being done to the DDT will have some open architecture to allow us to expand the tools and be able to use it with any database with our customization if needed. Edhy Rijo
	Reply Like 0