SQL Workers

Das Goravani goravanis at gmail.com
Tue Nov 29 18:24:35 UTC 2022

This is about SQL workers but really it might more be about Object References. I am working in 10.1. 

I am trying to send 22 queries as SQL workers. I am using a repeat loop. 

I am using Object Reference Variables that I set up in the variables pane. I am assigning them an object using $newref(). I wonder if this is right, sufficient, because something is wrong. 

It works for the first send of a SQL query if I step through the code and stop after 1. I get out a results list that is defined correctly according to the table I address. So all is well with sending #1. 

If I let it fire off more than 1 then I get no results at all, not even from the first one. It is as if the subsequent sendings clobber even the first one. 

Which makes me wonder if I am doing the object references correctly or if they are right for the job. 

I realize that usually when using SQL workers you are talking to the same database for all your queries. In our case, we are logging on to 22 postgres databases which are all on the same server. So we want to send 22 queries and have them simultaneously process, and get back 22 lists which we will merge into one. That’s the grand plan. 

First of all let me say that I have made around 22 object reference variables in the variables pane. They are instance vars. They are named after our databases that we are logging onto here. We are sending 22 queries. They go to 22 different Postgres databases. The rest of the info remains the same, the query, the hostname, username, etc. all remain the same across all 22 databases. 

My problem is I’m not getting 22 hits on my completed method. I get 1 if I send 1 and stop. More gets nothing. 

The fact that it stops after 1, or sends nothing at all to $completed, perhaps means that I am not understanding object references. Note that I tried objects but it made it crash if I sent all 22.  So objects seem to overload the system if there’s 22 queries sent. So I switched to object references, which the docs say is preferred. 

Now I have the instance vars. Then I am instantiating them(?) with the line 

Do $objects.oPGworker.$newref([icWorkerName]) Returns lWorkerRef. <<<<<<<<<<<<<<<important line

This gets me a valid situation on the first one when I step through the code and only send 1 query. Are the subsequent ones being assigned to the same object, the parent object, oPGworker (?) or are the instance vars I made being assigned to here, made valid here.. why is it not sending any results if I send 2 queries?

That’s the big question: Why no results if I send 2 or more queries?  Why only on first one does it work?

Here’s the code in full. Note that after I do the line above, I thereafter refer to the worker object ref using the reference this line passes back to me. 

More notes on why I used certain lines of code follow the code below. 

Note that if I hover over my ref to the worker object, it says it is a ref back to the parent object. But are my instance vars being used?  Does it always say the parent object by default and you have to simply know that it is using your instance var?  I am wondering if it’s just processing in the parent object and sending more than 1 clobbers the system so it doesn’t send anything. That’s what I’m wondering. 

Method: "FetchDataAsynch"

Calculate lcTemp as icDatabases (this is a string of database names)

Do bindvars.$define(idFrom,idTo) (you’re supposed to define your bindvars list as the fields you are including, correct?)
Do bindvars.$add()

Calculate query as 'SELECT * FROM infra.syslogerrors WHERE SLE_CWHEN >= @[bindvars.idFrom] AND SLE_CWHEN < @[bindvars.idTo]'

While lcTemp<>’’ (while there are still databases to process.. strtok takes this apart a few lines down)

Calculate database as strtok(nam(lcTemp),',')
Calculate database as trim(database)

If database<>''
Calculate icWorkerName as con('ioPGworker_',database) (I have instance variables made to match these names)

Do $objects.oPGworker.$newref([icWorkerName]) Returns lWorkerRef. <<<<<<<<<<<<<<<important line
Do lWorkerRef.$setExecutionParams(database) Is this going to the instance vars I made which are supposedly named by the ref?
Do lWorkerRef.$setCallback($cinst,'$completed','$failures') same here, is this going to the right var?

Calculate Params as row(query,hostname,username,password,database,bindvars)
Do Params.$redefine(query,hostname,username,password,database,bindvars)
Do lWorkerRef.$init(Params) Returns lbOK.  Works for the first one if I only send one
If not(lbOK)
End If
Do lWorkerRef.$sessionref(lObjRef) Returns #F
Do lObjRef.$port.$assign(tlDatabases.port)
Do lWorkerRef.$start() Returns lbOK  Works for the first one if I only send one
If not(lbOK)
End If

End If

End While

The reason I used the two lines that assign Params is that just doing $define does not put in the values. Doing $define then $add did not put in the values. This way I do it defines it and puts in the values. 

The reason I use $newref() is that just relying on the subclass of oPGworker in the variables pane left me with a situation where I would need to refer to the object reference using square bracket notation as I would not have a ref to it. So I would use [icWorkerName] as the start of the lines that hold $setExecutionParams and $setCallback.  When I did it that way, the processor stopped on those lines saying "Notation not supported".  So it wouldn’t do the indirection for this an object reference worker object. I don’t know why. Usually you can refer to things that way but not this time. 


Das Goravani

More information about the omnisdev-en mailing list