Unhappy in SQL Database World

[ADJob]

Hi,

> Using the plain method, do you put JUST the fields that changed in your statement, you don’t put all the columns of the table correct.. 

Yes.

> I’m wondering about verbosity and difficulty in handling long strings of field names.. 

I thought it was ugly at the beginning, but there are several advantage of knowing the trick behind all magic.

> your example I think shows one field being updated.. which in real world is all that needs to be updated quite often..
> one field. Not whole row.. please comment on whether you always use just the fields that matter or whole rows, is it hard to paste in whole rows..
> does it take up a very long line of code? 

As I said I use both plain and the lazy ”row.$update(old_row). But you have to create the rows behind (sometimes rather verbose). And you cannot always be lazy then. So I use the magic when I am lazy and plain with smaller updates.

> Interesting that you found the ORM method not workable in the real world..

It is workable but you do not learn SQL. When you hit the roof, you have hard to do subqueries and cte (WITH list as etc) using ORM.

> I am developing on ORM and plan on using it in real world.. but I do find it touchy.. it seems to get twisted and not work sometimes for no reason one can know… I have to reboot Omnis for it to behave again.. is that the kind of thing you encountered?  

db.Model(&product).Update("Price", 2000) (ORM)

OR

UPDATE product SET price=500 WHERE… (plain SQL)

The magic is not always that clear. The problem appear when ORM hit the roof. You have no knowledge how to make a plain SQL. Just relying on magic.

> I learn NO SQL doing ORM.. true.. you don’t do sequel, you do three statements, Insert, Update, Delete, that’s all you do, you never learn SQL.

I think ORM is called ODM when using NoSQL. I do know nothing about NoSQL.

> It’s interesting to note that when Omnis converts your data for use with SQL in the Data Migration Tool they do not make schemas..
> which means the MAPDMLTODAM functionality does NOT use ORM AT ALL but they must have programmed it entirely with the plain method of SQL… Interesting thought. 

Dunno.

> The ORM method does only whole row.. which can be unwieldy..
> I have very large rows on some tables and only need a few columns read or updated at times.. never is the whole row changed, never.. 
> 
> I’m so far doing “Keep CRB”,

I have not used CRB for 20 years. I prepared for SQL by avoiding CRB.

> “Centralized Code”, ORM.. so I have update in only one place and it handles ALL tables.. this is possible with ORM..
> I supposed it’s possible with plain method.. you have to have impersonal code, that handles all, not specific, so you don’t know what fields are to be updated for some given table that comes through for an update… so you do whole rows.. you have to.. you simply don’t know the specifics of any one specific save that occurs.. I like this impersonal centralized coding way.. you have your working main code in ONE PLACE ONLY.. you worry about one place only. I like that.. I also like that I can get specific and do plain way at some given location in the code that doesn’t fit into the generic model.. 
> 
> So far for me the ORM works if I do delete insert for update..
> this is acceptable in my appp with my customers, my circumstances..
> not being a business app, not on a mission at all.. a desktop entertainment app mine is.. low data loads.. low usage… very cool.. not hot.. 
> 
> I’m happy so far.. pleased with ORM.. 

> It’s interesting that GRADUATED players like yourself, Kelly Burgess, Jerry Greenburg, all tell me they don’t like the “black box” ORM method and don’t use it but prefer plain method.
> some people go so far as to skip table classes as they prefer the NO MAGIC way where they control exactly what goes on, and they can SEE their SQL they always say.. interesting realities

At the end of the day you want to get the job done. Perfection does not always put food on the table. ORM can do the job.

But I pointed out my experience. Learning plain SQL makes the life easier after a while.

BTW. Using this tool: https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/0 you can test plain SQL and find what is wrong rather easy.

FWIW,

/Mats