SQLCA in a shared object (yet again)

[Former Member]

I know, asked and answered - global variables are not available to shared objects. However...

I was looking at some old code of 11 years ago from a multi-threaded task manager that our company still uses. In most cases, SQLCA is passed in to the shared object as a user object of type transaction. In some cases, and in these cases the code seems to work fine, the shared object instantiates a nonvisual, and this nonvisual's methods use the global SQLCA. So to simplify my question - does an object instantiated by a shared object have access to the main application's global variables? By appearances, I'd say 'yes'. By the book, 'no'.

Larry

[former_member190719 · Accepted solution]

Each shared object has it's own set of global variables, as it's running in a separate memory space.  Take another look at that code to see if the SQLCA that is being passed in isn't being used to pass connection information and that the shared object isn't making it's own connection based on it.

[Former Member · Accepted solution]

Hi, Bruce. Are the globals set with the same values they had in the main thread, or are they empty variables? Perhaps this is why these threads seem to work is that their copy of SQLCA has the values from the *real* SQLCA back in the main thread?

[Former Member]

To confirm what I wrote, I did a quick test to verify your initial question :

does an object instantiated by a shared object have access to the main application's global variables

The response is NO.  A string filled in the main application gets empty in the shared object.

I knew that each thread has its own global variables but I tought that the value is passed.

Same for SQLCA. If you connect to the DB in the main application and then you start the

shared object, dbhandle() returns 0 in the thread.

Regards.

Abdallah.

[Former Member]

Correct ... unless you pass in a GV reference to the sub-thread .. its its own "unique space". 

[Former Member]

I did the same test and I got the non-zero dbhandle values from both (same value, btw). If the dbhandle was indeed zero, I would routinely get 'not connected' errors from those threads using raw sqlca, right?

I found out what is happening in our task manager. 100% of the threads get their own db connection when they start running. A few chose to ignore their own connection and use SQLCA instead. These threads still work, but they may collide with work being done in the main thread.

[Former Member]

   Correct, each sub-thread gets its own new copy of SQLCA and thus the dbHandle would be 0 initially until you either connect or pass in a pointer to the real SQLCA. For example:

of_init (ref transaction ao_trans)

SQLCA = ao_trans

---- However, I would personally recommend  instead ------

of_init (ref transaction ao_trans)

ns_transaction          io_trans    // Instance pool

io_trans = ao_trans

  Yes, this is the scenario I often encountered where each sub-thread gets its own connection via its own SQLCA - thus you have one DB thread per PB App sub-thread and its quite easy then for one DB thread to lock out another if you do not design your DML access properly. The alternatives are to a) use one copy of SQLCA as THE db access thread, b) let the main task handle all DBIO or c) let one sub-thread handle all DBIO.

[ricardojasso]

Each sub-thread will start with a clean SQLCA in its own memory space. It will NOT get a copy of the SQCLA from the process that spawned it. It should be treated that way and you need to set its connection parameters and issue a connection statement to be able to work with it.

Moreover, copying the referenced transaction object to the thread's SQCLA (SQLCA = ao_trans) is not a good thing to do. Although SQLCA starts clean in the thread, it has already been instantiated, so you'll leave orphaned the memory space it is occupying. And from tests I've done this doesn't work. It makes PB crash. I don’t think it is a good idea to have several asynchronous processes sharing a single db connection because you don’t know what they’ll be doing with the connection at any one time and they could stumble upon each other.

The correct thing to do is to copy the transaction parameters from the referenced SQLCA transaction object into the spawned SQLCA transaction object, as has already been noted, and then issue a connect statement. This can be done in an initialization function in the shared object:

uo_shared.of_initialize(ref transaction atr_sqcla) returns integer

SQLCA.DBMS = atr_sqlca.DBMS

SQLCA.DBParm = atr_sqlca.DBParm

SQLCA.LogPass = atr_sqlca.LogPass

SQLCA.LogId = atr_sqlca.LogId

SQLCA.AutoCommit = atr_sqlca.AutoCommit

(etc.)

Connect Using SQLCA;

The SQLCA in the spawned object can then be used by any object instantiated inside the shared object, like the nonvisual mentioned.

I believe this is what the mentioned application must be doing some way or another. A closer look at the code might clarify this.

[Former Member]

Hi Ricaldo;

   That is another great DBMS transaction design alternative! I have used this approach as well successfully.

   You can still reduce your physical DB connectivity over-head as well by using an ODBC or ADO.net DB Client where "connection pooling" is enabled.

Regards ... Chris

[Former Member]

Hi,

If you get troubles using SQLCA in a shared object (a thread), please don't blame Powerbuilder.

Moreover SQLCA is not simply a global variable but it handles a DB connection.

My rule in threading is

Start the thread -Get the DB connection - Do the Job - Disconnect- Dispose the objects,

and then you are safe.

Because the DB connections may be handled by a Connection pool of the main thread.

Regards.

Abdallah.

[Former Member]

Hi Abdullah;

  Great advise!

1) I never use any global variables.

2) I only use my own local or instance Transaction objects (not SQLCA)

3) DB Connection Pooling is another aspect to consider when designing you MT applications

4) I always cleanup all my resources at the end of transaction processing.

    I never wait or count on Garbage collection.

    (must be my old  - aah ... mature - that's it - Assembler programming training)

Regards ... Chris

[Former Member]

Oh. So you start your career with Assembler. Good start !

I started it with Fortran without a screen or a mouse

Cheers.

Abdallah.

[Former Member]

"Kool"!

   I actually started in school with APL and then moved into Fortran as I was studying electronic engineering. They taught us programming in order to solve complex calculations for resistance and conductance in designing processors. I liked the programming aspect so much more that after my electronics degree, I went back for Computer Science. Its there that they taught me Assembler in the 1st semester and I fell in love with its low level abilities to control things at the machine hardware level and its efficiency when coded properly,    

// Multiply by 2 by shifting bits in a register

SLL  (R14)   // Shift Logical Left

[Former Member]

Curious. I studied electronic engineering as well, and used Fortran to solve some of our more complicated problems. After I got my 2-year degree, and after a year at HP as a line technician, I crossed over to the *dark side* and started writing assembler code for the Zilog Z-80. Then macro-assembler. Then C. Then Powerbuilder. Maybe a little Java and HTML and CSS thrown in there to round out my resume.

[Former Member]

42 years ago.Where the crap did the time go?!

[Former Member]

.... and I guess, you used to listen to Bob Dylan and Neil young Music

Lets get back to the shared object please !

[Former Member]

Sorry. So, my take on this is that each threaded object gets copies of the global variables from the main thread (the running app). What's not 100% clear is whether the variables hold the same values as their actual global brethren.

I never liked either one of them.

[ricardojasso]

 1) I never use any global variables.

Chris,

In your STD framework you have a global variable called go_ac of type nc_app_controller_master. This class in turn has many instance variables declared inside. For all practical purposes this is the same as declaring all those instance variables as global variables. I can tell because this is the same strategy I use in my PB applications which makes it easy to transport common code from one application to another. But then you run into trouble in cases like this or when trying to build a .NET assembly using objects from the framework to be used in a .NET application.

Regards,

Ricardo

[Former Member]

Hi Ricardo;

  Yes, that is the correct architecture description for my Integrated framework.

  The design was originally done that way since PB 2 (1993 - way before .NET) when I used to call it the PowerGuide framework (distributed by AJJA Information Technologies) and then later called the PowerExpert framework. I revised the framework again for PocketBuilder in 2000, WebForms in 2005, Appeon in 2008 and then a few tweaks for newer PB's in the latest releases.

   What I did when PB 12.0 came out was port the framework to PB.Net and it worked flawlessly with a few minor tweaks but never considered .Net assemblies at that point. However, I discontinued the PB.NET flavor of the framework at the end of 2013 when I saw that I only had 15 downloads of it versus 1,000's of downloads for PB Classic over the same 1 year period. From that point on-wards I have only concentrated on Integrated framework for PB Classic, Appeon and Web Services.

   What would be an interesting framework offshoot would be a version geared to .Net Assemblies. However, with PB.Net's adoption just under 5% in the PB community and Appeon about to take over the PB product - I'm not going to invest any time at the moment in this area until I see a solid road-map including PB.Net.  

FYI ... (I average around 6,000-7,000 downloads a year at an average of 350-400 per month).

  On further thought ... a branch off the Web Services framework might be more appropriate for use in a .Net Assembly as its more encapsulated in its design but incorporates an MVC architecture that would allow the developer to build very robust OO oriented assemblies for sure.

Regards ... Chris

[Former Member]

Hi Larry;

1) the key to using SQLCA from the main task is to pass it's address into each sub-thread. In the sub-thread, assign SQLCA to an instance variable of type Transaction - thus gaining a pointer to the real SQLCA. Now each sub-thread can reuse the one Connection.

2) number #1 being said, you have to be careful in your multithreading design that you pull a minimal amount of dat. In other words, small infrequent DML requests otherwise, you can monopolize the Connection for long periods of time that will in effect shutout the other threads. That will in effect could make your multithreading application run slower than a single threaded application!

3) in one case, I had to redisgn a multithreaded application to move all the DML processing to only one sub-thread. This application was monitoring a work queue and doling out work to other threads based on work items found. I found that if each sub-thread vied for the work queue, they would in effect block each other too much. Also, using the main task to handle the work queue where DBMS response might be slow would hamper dispatching new work and handling sub-thread requests simultaneously.

HTH

Regards ... Chris

[Former Member]

1)  the key to using SQLCA from the main task is to pass it's address into each sub-thread. In the sub-thread, assign SQLCA to an instance variable of type Transaction - thus gaining a pointer to the real SQLCA. Now each sub-thread can reuse the one Connection.

I get: "Error: Object passed to shared/remote object method is not a nonvisual user object at line...."

[Former Member]

Hi Lars;

Yes, in Shared Object processing ... only NVUO's can be passed back & forth between sub-threads or between the main thread & a sub-thread. Also remember that you can only POST methods / events between these as well (no Triggering).

Regards ... Chris

[Former Member]

Hi, Chris. Sadly, all of our hunderd or so threads use the same connection. It would be a monumental effort to make it right now. We should have had some forethought/insight at the get-go. But... 10 years ago we didn't think our little multi-threaded task manager would someday run all of the background tasks for the company. It's a behemoth now.

And thanks to Abdallah for the insight we should have had back in the day. Going forward, I think I'll write something up as a guidline for creating new tasks in our system.

Funny, I was just hired back after being laid off 3 1/2 years ago. Nothing has changed.

[Former Member]

Congratulations on the "hiring back" part though!  

[Former Member]

What happens is; each thread get their own copy of the global variable. Changing it in either thread does not change the other. So in the case of SQLCA, you can connect in the main thread after setting properties, but you have do it again in the other thread, including connecting.

[Former Member]

Is this true then: Main thread connects with SQLCA. It then spawns thread1, which gets its own copy of SQLCA. Is this copy's properties still set to the values of SQLCA in the main thread? Do I have to connect again? The code in question in our app just uses SQLCA out of the box without any further connections or property setting - it uses the same connection as the main thread (which is OK). I just wasn't sure how SQLCA was getting set in the sub thread.

[Former Member]

As far I know global variables can be referred anywhere in PB object if all object are under same Target. For you question Yes.