Clash of the Backends leaves Developer stunned
Hi all,
Another scenario is when Omnis is used as a ”glue” environment between several different types of systems needed to integrate into a functional whole for end-users. In that context one can still use the specialised approaches from the different systems by customising the data access and processes for each specific db system while integrating the results into unique process flows. In one area where I am active qualified maintenance planners have to sift through and manage data and information from up to 20 different data- and information sources when planning certain procedures, each one of these different sources residing in different systems and formats. The strength of Omnis is the ability to integrate these sources and projects into a unified user interface (presented in some of my papers). Kindly be aware that this connectivity not only can include db access but also access through SOA and files.
This is a major strength behind Omnis, especially in combination with the ability to in parallel develop large segments of code for several deployment modes – let us just hope that Omnis starts to look heavily into modernising their GUI systems for mobile clients. The mobile solutions are getting more and more important – which I think must get a priority treatment to have an impact on the broader market.
Take care all, all the best…
Stene
_______
> 4 feb. 2018 kl. 23:33 skrev Bastiaan Olij <bastiaan@basenlily.me>:
>
> Hi Das,
>
> This I think is a question everyone needs to answer for themselves. Both
> options have pro’s and con’s.
>
> The most important thing to realize though is that SQL really falls
> short as a standard. The standard really only defines the syntax for
> writing queries and even that is followed to questionable levels by some
> databases (though you’re pretty safe here when sticking to the major ones).
>
> Obviously if you decide to stick to the standard you can easily switch
> between the different platforms. You can use SQLite to deploy to single
> user systems, you can use PostgreSQL for your larger clients, you can
> deploy to Oracle or MSSQL server for clients who for whatever misguided
> reason what a big name behind the technology you use, etc.
>
> However, if those types of issues do not mean anything to you, choosing
> a single database platform allows you to specialize in that RDMS and
> that can be very much worth your while.
>
> You’ve already had your first taste of one area where the SQL standard
> is hopelessly non-existent. Sequence numbers. There is nothing in the
> standard that dictates how to generate unique primary keys for tables.
> There is nothing in the standard for how to retrieve those values once
> generated. Every database has its own unique implementation. PostgreSQL’
> returning syntax as far as I know is unique to PostgreSQL but its also
> one of its strong points.
>
> Another area that does start to bite you is that there is no standard
> for data types. While each database has the standard data types for
> columns there are various differences especially around large text
> fields and binary fields that can be a pain. Especially once you start
> to have a need for optimizing how things work as there are crazy
> performance differences between say a varchar field and a text field on
> many RDMSs.
>
> For instance I saw someone suggest to you using “numeric_field::text
> ilike ‘123%'”, that works exactly as you’d want, it also completely
> kills performs on any large table as this relegates execution of that
> query to a table scan (the database reading each and every single row in
> the table to figure out if its the row you’re looking for = SLOW).
> Note that you can make this fast by creating an index on a cast column,
> and casting to a size limited varchar but again that is not part of the
> SQL standard and only supported by a handful of back ends (Postgres
> being one of those).
>
> A few other doors that will close for you if you want to stay backend
> agnostic:
> – no access to users or other login meta data, this can be a biggy, you
> have to roll your own access logic which will make a lot of security
> features in the RDMS unavailable to you unless you demand of your
> clients to create and maintain users logins outside of your application.
> – no access to triggers. many databases don’t support them, and for
> those that do the syntax is not portable
> – no access to stored functions. really just the same reason as triggers.
> – no access to specialized data types, you’d be amazed how much fun you
> can have with PostgreSQLs JSON data type especially now that we have
> half decent functions in Omnis for JSON.
>
> But again, and I can’t underline this enough. It totally depends on what
> sort of application you are making. I think you need very good and
> practical reasons not to specialize to one RDMS because you are giving
> up ALOT and making your life far more difficult. But depending on your
> situation there are compelling reasons to stick to standard SQL not to
> mention that there are certain areas of our business where you do not
> have a choice. Bottom line, if you have a reason to support multiple
> back ends, you have a really solid reason to stick with standard SQL.
>
> P.S. one final thing, keep in mind that Omnis in building what they
> build into the core product, stuck to standard SQL. On one hand I
> applaud them, it means that Omnis is not tied into a single back end. On
> the other hand its the foremost reason why it is so hard for new people
> to get started in Omnis Studio because they have to write all the
> missing bits.
> One really cool feature of Omnis Classic was that you could start it up,
> follow a tutorial that spanned less then an A4 that told you to create a
> datafile, create a file class, dump some fields and buttons on a window
> and you had a working application.
> It didn’t first have to explain to you how to setup users, how to log
> in, how to create tables, how to roll your own code for creating unique
> identifiers, how to well you get my drift…. You are a chapter into
> reading about writing boiler plate code in Omnis Studio before you reach
> that point with an SQL back end, and really that manual should be
> different for each back end.
>
> Us as a community can do better helping out there with more example code
> being made available which has become infinitely easier now that Omnis
> Studio supports GitHub. I hope to play my part in this soon once I am
> able to free up my schedule a little bit and redo my “getting started”
> tutorial.
>
> Cheers,
>
> Bas
>
>
> On 3/2/18 9:11 am, Das Goravani wrote:
>> So I’ve heard from a number of Omnis/SQL Gurus on the subject of “Generic SQL” and Backends.
>>
>> One told me to stick to the Standard SQL as much as possible for portability. Other amongst the knowing tell me that there are multiple standards and besides the backends give differing results for the same commands, and that trying to handle multiple backends is a pain.
>>
>> So my very general question is this: Should one tell oneself that they are trying to stick to the standard? Should one try at all?
>>
>> I just had the case where I need to “cast” my numeric field to text so that I can do a LIKE comparison in a Where Clause. I was given two ways of casting… one is to write cast() in front of the column name in question, and the other is Postgres’s way where you say name::text to cast it to text. I’m using a Postgres server as I develop, and it does not recognize the first method, only it’s own method.
>>
>> So as I sit here I’m faced with “Have bugs now”. I either can run in dev mode with this Postgres server and do things it’s way or face bugs now. Weird. I’d have to fork now for differing backends and I can see as Alex said that “That is a pain”. I can see that. I don’t want that.
>>
>> The clientele I’ll be aiming at in my “practice” when I’ve learnt enough to “get out there” will be smaller companies, because I work alone. I believe that smaller means “not as stuck on what backend they want to use” as larger corporations who may already have a standard they stick to. So I am MORE OK with just Postgres than if I were targeting the very large companies.
>>
>> What to tell myself ? Which way to develop in the here and now ? I don’t want to fork all over the place for different DAMS and backends. I can barely do what I’m doing, I mean it’s a hard learning curve as is. What to do brothers and sisters in arms ? What to do?
>>
>> _____________________________________________________________
>> Manage your list subscriptions at lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com
>
>
> —
> Kindest Regards,
>
> Bastiaan Olij
> e-mail: bastiaan@basenlily.me
> web: www.basenlily.me
> Skype: Mux213
> www.linkedin.com/in/bastiaanolij
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com
Sten-Erik Björling
Enviro Data
Kyrkogatan 5A 2 tr
SE-972 32 Luleå
Sweden
E-Mail: s-e.bjorling@enviro.se
Mobile: +46-70-655 11 72
Wire: @stenerikbjorling
Skype: stenerikbjorling
iChat: stene@mac.com
FaceTime: stene@mac.com
Hotmail / Messenger: stenerikbjorling@hotmail.com
GMail: stenerikbjorling@gmail.com
______
This email and any files transmitted with it are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, please note that any use, distribution, or reproduction of the contents of this email is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by return email and destroy all copies of the original message including any attachments thereto. Thank you.
Please note that we take reasonable precautions to prevent the transmission of viruses; however, we cannot guarantee that this email or its attachments are free from viruses. We only send and receive emails on the basis that we are not be liable for any loss or damage resulting from the opening of this message and/or attachments.
_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com