DML

Mon Nov 7 18:44:52 EST 2016

As Bas says, here is my two cents. If your application works with DML and has not started to show its age, then why change. Doug brings up a great number of issues which are very valid since I have dealt with those issues myself. If you do find yourself having to move into the realm of SQL then start out with a small project to get your feet wet. For all the statements about how easy it is to learn there are a large number of caveats that are related to SQL databases. Firstly learn about the structures of the database, just like you did with DML. Also each database has a different engine and will respond differently to queries. I deal with performance issues in databases all the time in my business and most of the time it is education of the developers of how relational database work. One classic error that new developers do is create an index on a key value that has only a limited number of values such as gender. If you have 100,000 rows that is not a good value to index, way to much overhead. The beauty of omnis studio is that it has always been a data centric development tool and makes live so much easier to work with database due to schemas and table classes.  Keep in mind also that at some point you will move to a SQL databases to be competative and to ease the sharing data. Don’t forget to plan for the future. 

Just a few thoughts.
Dan Ridinger
Managing Director

FutureChalk Software Inc.			
20521 92A Avenue						
Langley, BC  V1M 1B7

Phone No: 604.723.6837
EMail: dlr at futurechalk.com
www: www.futurechalk.com

> On Nov 7, 2016, at 2:09 PM, Bastiaan Olij <bastiaan at basenlily.me> wrote:
> 
> Hey All,
> 
> I'll throw my two cents worth into the mix as well especially as the
> subject came up and was discussed in some detail when we had our OzOmnis
> meetup last week.
> 
> I was lucky enough to enter Omnis 7 days straight into SQL and skipped
> the DML initially, FIQAS' initially application was DML based but they
> made the switch to SQL almost immediately and I rolled in when
> everything was already purring on Sybase.
> 
> It wasn't until I joined forces here in Australia that I had to work
> with the DML and that was a rather large project getting a large system
> off of the DML onto SQL. We went from a setup where we were dealing with
> data errors on a weekly basis rebuilding datafiles to not having to deal
> with those at all. But in that same breath I'd make the observation that
> it was only certain clients where these issues happened semi frequent.
> We have had several clients, and still have several clients, running on
> an older version of our software that still uses the DML and who have
> indeed been rock solid for many years.
> 
> One main thing for us that we've gained is performance but I think this
> is something that really depends on your type of system. We run some
> really complex reporting in our system and managed to bring processes
> that took minutes down to seconds, some really extreme cases we've even
> managed to change processes that run well near an hour to nearly
> nothing. I wager that we could get much better results out of DML if
> we'd run Omnis on the same physical machine as the datafile as it is
> mostly network that has always been the bottleneck here but still, a
> product like PostgresSQL is making far superior use of the available
> resources compared to churning through data within Omnis.
> 
> The other thing that really made a huge difference for us was SQLs
> ability to enforce constraints and the ability to use transactions. No
> more half saved invoices because someones WIFI signal got interrupted
> (not that WIFI and DML mix very well to begin with), no more deleted
> clients that orphan data structures because someone forgot to write some
> checking code, etc.
> 
> That all said, there is one thing the DML has that SQL currently can't
> touch and why I think so many people have problems letting it go and
> this was the main subject of our talk.
> 
> And no it's not easy of maintenance/installation that I've heard mention
> in earlier conversations. People seem to think SQL automatically means
> heavy server setup and maintenance. This may be true to some extent for
> the likes of PostgresSQL, Oracle, MS SQL, etc. but there are
> alternatives in the SQL world as well, SQLLite is build into Omnis and
> doesn't require any installation, SQL anywhere is designed to run both
> locally and on a server, and there are stand alone installs of Postgres
> that you can deliver alongside your app.
> 
> No the thing that DML has most going for it is the ease of working with
> it. Open chapter one of the old Omnis classic tutorial and it tells you
> to open a new datafile, create a file class, put some fields on a window
> and hey presto, working database front end. Add a few more columns to
> the file class, and presto it all works. Use a new version of your
> application against an older datafile (upgrade scenario), hey presto
> Omnis adds all the missing bits.
> 
> Before you are at the point that you have that sort of logic in SQL,
> you've written days worth of code, code to reverse engineer a database,
> code to update database tables based on your schema, code to make a
> window work. Yes the table classes are brilliant but they only get you
> so far.
> 
> Now for big systems it makes a lot of sense to use modeling software to
> organise and push out your database structure and have Omnis simply be a
> consumer of that database but for many types of Omnis applications I've
> seen out there (including our own) not being able to create a brand new
> database from your schema classes and not being able to push out schema
> changes to an existing database right out of the box is a mayor
> roadblock to creating an easy to deploy stand along application.
> 
> And in that respect I understand a lot of DML developers, why invest
> weeks of your time putting the structure in place to support a SQL
> database, and then spend months of your time rewriting your application
> to SQL, if there doesn't seem to be any added benefit. Especially when
> many of the benefits of a SQL solution are those you'll only understand
> after you experience them and many of the benefits of the DML are
> missing until you write oodles of code.
> 
> Just my few cents :)
> 
> Cheers,
> 
> Bas
> 
> 
> On 7/11/2016 11:36 PM, plum_hollow at cogeco.ca wrote:
>> Why do developers using DML feel like they have to eventually migrate to SQL?  I have been using DML for over 25 years and it is rock solid.  I’d be interested in knowing how many developers are using DML.  Going forward, can DML and fat client take advantage of JS, web and mobile app technology?
>> 
>> Randy
>> Plum Hollow Software
>> 
>>> On Nov 6, 2016, at 10:44 PM, Jim Creak <jim at jacsoft.co.nz> wrote:
>>> 
>>> I’m not a hardware person, and our product is still working with DML.
>>> 
>>> A couple of our clients are getting themselves Surface Pros and finding that they are getting padlocked when they go to do data entry, it is my gut feeling that the Surface Pro is sleeping the network adapter and hence causing the link to the datafile to get lost. BUT I have no idea how to fix this.
>>> 
>>> We ensure that they are hard wired to the network (i.e. not wireless) and plunged into the power adapter so as to prevent the device from going to sleep because it feels like it has to.
>>> 
>>> I have in the past found a setting on Network adapters that talk about allowing the OS to sleep the adapter if the OS feels like it is a good idea, and I tell people to ensure this is not enabled.
>>> 
>>> No I can’t go to SQL yet that’s in the pipeline of things to do, but will take a lot of time and effort before it will happen :)
>>> 
>>> Any ideas?
>>> 
>>> Thanks
>>> Jim
>>> 
>>> JACSoft Programming Ltd. <http://www.jacsoft.co.nz/main.shtml>
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> 
> -- 
> Kindest Regards,
> 
> Bastiaan Olij
> e-mail: bastiaan at basenlily.me
> web: http://www.basenlily.me
> Skype: Mux213
> http://www.linkedin.com/in/bastiaanolij
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com