Hi and good day,
I am in the process of converting an existing application to Servoy. The existing system is based on the runtime version of Microsoft Access. The software is installed on their workstation, and away they go. Simple. ![:)]( "Smile")
The new application will have both a runtime version and a web-based version under Servoy. I am exploring alternatives for converting my existing users to the new system.
So far, most thinking has been around opening both databases in the application, and moving the data to the new structure from the old. There are challenges to doing this, not the least of which is that the runtime version will only support one database connection. There’s a 3rd party JDBC driver from a company in China that allows me to open the access database within Servoy Developer, so I know that at least that much can be done. And I should say that are definitely ways to do this, some of which are a bit painful; I prefer something a bit more elegant.
I’m trying to think of alternative ways of accomplishing this. Basically, I need to get data from the Access database to a postgres database under servoy, both as a runtime and a web-based app. The mechanism has to be easy to use, and require as little user interaction as possible. Their current Access database will be residing on their local PC hard drive.
I’d be interested in any thoughts you might have. Happy to answer any questions you might have.
As always, thanks for your assistance.
Ron
Hi Ron,
Did you consider writing a custom export routine in Access (to a file on disk), and then writing an import into Servoy? I haven’t done this; however, since I have been writing export/import routines in the old application for a long time, I am planning this approach to migration myself.
Don
Hi Ron and Don,
When you use Oracle/Sun Java then there is already a JDBC-ODBC bridge built into the JRE (Apple Java doesn’t have this feature) so you could already connect Servoy to your Access database using a ODBC datasource (when on Windows of course since only windows has Access compatible drivers).
Just right-click in developer on the Database Servers node and select ‘Connect to existing database’ > ‘ODBC Datasource’.
And if you are using PostgreSQL 9.1 then you could also use the SQL/MED (SQL Management of External Data) system that was introduced in that version. SQL/MED is a SQL (2003) standard that allows for a standardized way to handle remote objects in SQL databases. So in short you can let PostgreSQL connect to remote databases using a ‘Foreign Data Wrapper’ from within your PostgreSQL database. These Foreign Data Wrappers are extensions to PostgreSQL.
The remote tables will then be accessible inside your PostgreSQL database as if it’s a local one. Of course there might be a restriction here and there depending on the backend and wrapper.
Right now there is a growing list of these Foreign Data Wrappers in different states of maturity but I believe the ODBC one is pretty mature already. It’s an interesting list because there are also fdw’s for non-sql databases like LDAP and web-services (!).
Both options can be used to migrate your data off Access or to keep Access as a live datastore.
Hope this helps.
Hi Don,
I thought about writing a routine to export the data from Access and then import into Servoy. That would require that I either put out one more update to the old system, which I really don’t want to do, or distribute a separate runtime app to do the same. That’s certainly doable, but I’d rather not have the users have to do much to get this accomplished.
Robert, I’m using the version of Postgres and comes with Servoy. It looks like that is Postgres 9.0.3. But SQL/MED sounds like something that might be a good fit for what I need to do. I’ll check into that further. I want to get away from Access completely, so I’d be looking into moving my data off of it and then discarding the old database.
Thanks, as always, for the responses.
Ron
Hi Ron,
Be aware that in both of the aforementioned options you need to use ODBC and therefore an Access ODBC driver.
Option 1 you can use right now already, you don’t need to install any JDBC driver because it’s baked into the Oracle/Sun JRE. So if you just want to migrate that option might be the simplest option.
Hope this helps.
Hi Robert,
Hey, just backing up for a second. Does the SQL/MED feature get us around the problem of only being able to have one database connection open in the Runtime version of Servoy? There’s already a 3rd party JDBC driver that would allow me to open the Access database from within Servoy. But since I can only open one at a time there (the runtime), it doesn’t allow me to do something nice like open them both and just have logic execute that moves the data from to the other.
The other thing I’m wrestling with is in the web-based version. I can have 2 database connections open there, I believe, but in the application server, those 2 database connections are tied to 2 physical databases. For one of those, my new database, that’s fine, but the 2nd database will constantly change as users come in to convert their old databases, ie, the physical connections will change. Now, I think that what this means is that I can only do one conversion at a time, so I’d probably have to set up some sort of batch process to collect them and run them in sequence if I had multiple requests.
Of course, if that’s all true, that might actually be the solution to both, that is, set up a batch process that collects the databases to be converted, and then at some specified time, kicks off a process that converts them and either places them into the online multi-tenant database, or perhaps sends them back, in some way, to the runtime users.
Something to think about, anyway.
Thanks again.
Ron