O$: Object vs Object Refence?

Tue Apr 20 00:30:58 UTC 2021

Hi Everyone,

Thank you all for your responses - those who posted on and off the list, it is much appreciated.

As many have pointed out, our approach probably isn't the best way, and there are more efficient ways of handling the connection.  That said, this codebase began over 5 years ago when we started converting our DML-based apps to SQL, and we wanted a way to unobtrusively convert our code.  The solution was to create an oSQL object that mirrored the DML commands eg "Single File Find" became "do ioSQL.$SingleFileFind" etc.  We then wrote a parser to convert our DML code to the SQL object, intelligently parsing the parameters in their myriad forms and converting them to something that could be passed to the oSQL object.  We even did some fancy parsing of search calculations so that Omnis search calculations could be converted to SQL.  That meant that we could run an update routine over our library, it would insert the oSQL object calls as required, and very little had to change to make it work. Overall that approached allowed us to convert all our applications to SQL, the largest app running manufacturing plants, production lines, despatch, ordering etc.  

In talking through the solution with a few people, we realised that in our oSQL object we initialise our Postgres session, but when we're using the jsClient/remote task we initialise the session in the main library startup task.  We decided to refactor the code so that regardless of desktop or remote, it would call the same routine to obtain a session, and this was done in the startup task.  Bottom line: changed a couple of methods, and a handful of lines of code, and I think we've got it sorted.

Moving forward from here I can see that maybe session pools aren't required, and the bigger issue of data isolation may mean it's better to have multiple databases rather than multiple schemas, so I can see that we'll keep refining our code using the suggestions that you've all given.

Once again I'm impressed with the community of Omnis developers that are willing to share their knowledge and experience with others - you guys rock!

Regards,
Paul.

> On 19 Apr 2021, at 4:53 pm, Rob Mostyn <mostyn at platformis.net> wrote:
> 
> Hi Paul,
> 
> We have multiple applications with different operating paradigms.  Some run thick client, some run jsClient, some are RESTful and some apps have a mixture.  
> All apps have a common infrastructure library ( infra.lbs ).  Our most elevated superclasses are in infra.
> The startup task does not kick off from infra though.  The startup task is app/library specific.
> 
> We support both complete-single-connection-for-the-whole-app sessions and session pools.  Sometimes we establish a single (instance object) connection for a specific window because it is connecting to a different database from the application norm.  Our table classes in infra do not care which connection method is used.  We don’t use object references though, but I don’t think that is relevant to this discussion.  Most of our apps are using session pools but the motivation for implementing them is waning.  We are considering moving away from them but there is no urgency.
> 
> Our technique is to call an “ensure session resource is there” method before every Begin statement command within the table classes and after the sql has been executed, release the session back to the session pool if that is where you obtained it from.
> 
> The code within a table method to fetch data looks like this:
> 
> 	Do $cinst.$ImmediateSession(loSession,loStatement) Returns lbImmediateSession
> 	If lbImmediateSession
> 		Do $cinst.$SetSession(loSession,loStatement)
> 	Else If not(ibGoodConnectionToDb)
> 		Quit method kFalse
> 	End If
> 
> 	Begin statement
> 	Sta:select [$cinst.$selectnames()]
> 	Sta:from [$cinst.$servertablenames]
> 	# etc
> 	End statement
> 
> 	Do $cinst.$executesqlandfetch() Returns lbOK
> 
> 	If lbImmediateSession
> 		Do $cinst.$SetSession(#NULL,#NULL,kTrue)
> 	End If
> 
> loSession and loStatement and lbImmediateSession are local variables.  lbImmediateSession is set true if you are using session pools and is used to determine if something needs to be released back to the pool.    $ImmediateSession determines whether a session pool or task-var based session object or if, indeed, a specific session has already been assigned to the table class (this occurs when a window, say, is connecting to a specific db).
> 
> $ImmediateSession looks like this:
> 	If $cinst.$sessionobject.$state=kSessionStateLoggedOn
> 		# ready
> 		Calculate ibGoodConnectionToDb as kTrue
> 		Quit method kFalse
>  
> 	Else If $ctask.tFormSession.$state=kSessionStateLoggedOn
> 		# this will remain persistently connected
> 		Calculate $cinst.$sessionobject as tFormSession
> 		Calculate $cinst.$statementobject as tFormStatement
>  
> 		Calculate ibGoodConnectionToDb as kTrue
> 		Quit method kFalse
>  
> 	Else
> 		If icWhichDatabase<>''
> 			Do $DynamicSession(pSession,pStatement,icWhichDatabase) Returns lbOK
> 		Else
> 			Do $DynamicSession(pSession,pStatement) Returns lbOK
> 		End If
> 		Calculate ibGoodConnectionToDb as lbOK
>  
> 		If icSetTimeZone<>''
> 			Do pStatement.$execdirect(icSetTimeZone) Returns lbTimezoneSet
> 			If not(lbTimezoneSet)
> 			Breakpoint
> 			End If
> 		End If
>  
> 		Quit method lbOK
> 	End If
> 	Quit method kFalse
> 
> As you can see we even include timezone support for web based apps but igniore that for this thread.
> 
> $SetSession looks like this:
> 	If pbKillSession
> 	Do $cinst.$sessionobject.$assign(#NULL) Returns lbOK
> 	Do $cinst.$statementobject.$assign(#NULL) Returns lbOK
> 	Else If pSession.$state=kSessionStateLoggedOn
> 	Do $cinst.$sessionobject.$assign(pSession) Returns lbOK
> 	Do $cinst.$statementobject.$assign(pStatement) Returns lbOK
> 	Else If $ctask.tFormSession.$state=kSessionStateLoggedOn
> 	Do $cinst.$sessionobject.$assign($ctask.tFormSession) Returns lbOK
> 	Do $cinst.$statementobject.$assign($ctask.tFormStatement) Returns lbOK
> 	Else
> 	Breakpoint !
> 	End If
> 
> When it comes to code in your visual or RESTful classes it looks like this…
> 
> When using session pools OR using a single connection throughout the app (for us this would be the Task basesd object tFormSession and tFormStatement)
> 	Do yourList.$definefromsqlclass(’tClient’)
> 
> after that it doesn’t matter if you use pools or task based sessions, you simply call your sql method such as:
> 	Do youList.$fetch_pk(primarykey)
> 	Do yourList.$fetch_fk(foreignkey)
> 	Do yourList.$fetchTodaysActions()
> 
> IF, however, you want to steer the connection away from your application norm, one does something like this:
> 	Do yourList.$definefromsqlclass(’tClient’)
> 	Do yourList.$SetSession(ioSession,ioStatement)
> the ioSession and ioStatement you have established may have been determined with a $logon to a separate db within the instance you are running   OR   you may have added a Do yourList.$ImmediateSession() within the visual class if there is a transaction involved.  
> 
> If you are running a transaction then you have to use the explicit $SetSession(ioSession,ioStatement) for each table class used within the transaction.
> 
> I would like to thank Sten-Erik for this “paradigm” of ‘grab session, use session, release session’ that applies to session pools.  It has helped make our code very resilient to different application regimes.
> 
> Rob Mostyn
> mostyn at platformis.net <mailto:mostyn at platformis.net>
> 
> +44 (0)20 3233 0044
> 
> As Carl Sagan once said:
> One of the great commandments of science is, "Mistrust arguments from authority." ... Too many such arguments have proved too painfully wrong. Authorities must prove their contentions like everybody else.
> 
>> On 19 Apr 2021, at 07:55, Bruno Del Sol <bruno.delsol at bydesign.fr <mailto:bruno.delsol at bydesign.fr>> wrote:
>> 
>> Hi Paul,
>> 
>> The SQL architecture we use for js apps is :
>> 
>> - each remote task instance has 1 Objectref instance var holding a SQL session
>> 
>> - each remote task instance also has 1 Objectref instance var holding a SQL statement object (some time more than one if cursors are needed)
>> 
>> - The SQL session (and statement) are lazy loaded, ie they are initialised and connected only right before the first $execdirect occurs during the remote task instance lifetime.
>> 
>> - the main (desktop) task has also its own Objectrefs for 1 session and statement and is also lazy loaded, but practically those are used only at launch time to retrieve a bunch of app parameters from the database. It then sits idle and is eventually wiped out pretty soon by the RDBMS connection timeout.
>> 
>> Therefore, no open SQL connection is held live.
>> 
>> This setup has proven to be rock solid in production (no Omnis crashes, no memory leaks) on the 3 platforms combined with MySQL, PG and MSSQL. It also makes maintenance a lot easier because you can reboot servers or restart services (Omnis or rdbms) and your app will still be live as if it didn't happened.
>> 
>> You may think this is too much changes for you app, but I'm not so sure, I was once using sessionpools also, and this change is far from being the trickier rewrite we had to do with Omnis over the years.
>> 
>> HTH
>> Regards
>> 
>> Bruno
>> 
>> By Design
>> http://www.bydesign.fr <http://www.bydesign.fr/>
>> Bruno Del Sol
>> bruno.delsol at bydesign.fr
>> 46, rue de La Tour d'Auvergne
>> 75009 Paris (France)
>> 
>> Le 19/04/2021 à 02:04, Paul Mulroney a écrit :
>>> Hi Everyone,
>>> 
>>> We've been using a class var to hold a Postgres session object ever since we started migrating our software to SQL.  For a recent project, we're going to use one postgres database with multiple schemas, and found that our class var isn't going to work.  The basic issue is that the session holds the current schema, so if you change schemas in one place, the next call elsewhere will use the new schema.
>>> 
>>> For our desktop app, that's going to be OK, because it always references the one schema.  However we use the same framework for our jsClient - each connection could potentially reference a different schema.
>>> 
>>> My plan was to use a class var for the desktop, and instance vars for the jsClient, and somehow switch between the two.  However, I couldn't find an easy way do that.  The best I could find was to convert the Object to an Object Reference, and then I could easily pick between either the session pool or the class var.
>>> 
>>> Next problem: Our typical block of SQL code goes like this:
>>> 
>>> Do ioSQL.$mySessionObject returns voSQL
>>> calculate vsQuery as <something>
>>> Do voSQL.$execdirect(vsQuery)
>>> ... etc
>>> 
>>> where voSQL is an Object.
>>> 
>>> My question: Can I have an Object Reference, and somehow get back to the Object?  Otherwise, there's hundreds of lines of code across dozens of libraries that need to change <groan>
>>> 
>>> Regards,
>>> Paul.
>>> 
>>> 
>>> 
>>> Why does a chicken coop have two doors? If it had four doors it would be a chicken sedan.
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 

A man rushed into the doctor’s office and shouted, “Doctor! I think I’m shrinking!”
The doctor calmly responded, “Now, settle down. You’ll just have to be a little patient.”
-- 
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102