1. main@magicu-l.groups.io
  2. Messages
   Date   
Re: Multiple Direct SQL Tasks In One Transaction

Andreas Sedlmeier
 

Hi Mike,

You can try to set the max connections for th DB to 1. In theory Magic is forced to use a single transaction then, since there can be only one per connection.

You also should check the lock tables and that stuff to be sure whats going on. Its not that likely, MSSQL does however suffer from a bug where you can get a deadlock within the same connection. Thats due to the parallel nature and its worker threads.

Best regards,

Andreas
Re: Multiple Direct SQL Tasks In One Transaction

Mike Bannon
 

Now I’ve looked at this problem again, I’m not sure I’ve analysed the cause of it accurately.

 

I open a (physical) transaction in UP, do a few updates, run a Direct SQL task which calls stored procedure ‘A’ which updates a table in another database, then run another Direct SQL task which calls stored procedure ‘B’ which attempts to update the same record as ‘A’ did. At this point the system hangs, presumably due to a locked record. If I move the call to stored procedure ‘B’ so that it is outside of the main transaction, all works fine, but this isn’t an option for the production release.

 

There isn’t usually a problem updating the same record twice in the one transaction, and I’ve just written a simple test suite which does exactly this using stored procedures – that works fine. Can anyone explain what is going on here, and how I can fix it?

 

TIA

Mike

 

From: main@magicu-l.groups.io [mailto:main@magicu-l.groups.io] On Behalf Of Mike Bannon
Sent: 01 May 2019 11:29
To: main@magicu-l.groups.io
Subject: [magicu-l] Multiple Direct SQL Takss In One Transaction

 

Hi All

Using UP 1.5

Just throwing this one out in case someone has a better solution than the one I'm planning to implement: 

We have an application which integrates to our customer's back-end CRM system. As part of one of our processes, we have to call a few stored procedures in the CRM system to update its data. This has worked fine for years, using Direct SQL tasks, but now we are adding new updates to this process, which means adding a call to a new stored procedure, in a new Direct SQL task. Unfortunately, this new sp updates one of the records already updated in one of the earlier sp calls. Now, we have, and need, all these updates wrapped in the one transaction, but when UP runs a Direct SQL task it opens a new SQL session (as far as I remember - feel free to correct me if that's wrong). So, the update in the earlier sp locks the record, which blocks the update from the later one. This just causes the system to hang as it waits for the lock to be released, which doesn't happen as it's all the one UP transaction, although separate transactions in the SQL server.

My solution is to create my own stored procedure, which will call all the CRM's procedures, so this can be implemented in the one Direct SQL task, and therefore the one SQL session/transaction. This will take a bit of work to implement, as there are a few sp's to be called, and each will involve cycling a set of query results, and calling for each record.

So, before I embark on this, I was wondering if anyone has any better solutions? Is there, for example, some way of having multiple direct SQL tasks in the same SQL session/transaction?

TIA
Mike Bannon
DataFormation Ltd., UK


Virus-free. www.avast.com

Multiple Direct SQL Takss In One Transaction

Mike Bannon
 

Hi All

Using UP 1.5

Just throwing this one out in case someone has a better solution than the one I'm planning to implement: 

We have an application which integrates to our customer's back-end CRM system. As part of one of our processes, we have to call a few stored procedures in the CRM system to update its data. This has worked fine for years, using Direct SQL tasks, but now we are adding new updates to this process, which means adding a call to a new stored procedure, in a new Direct SQL task. Unfortunately, this new sp updates one of the records already updated in one of the earlier sp calls. Now, we have, and need, all these updates wrapped in the one transaction, but when UP runs a Direct SQL task it opens a new SQL session (as far as I remember - feel free to correct me if that's wrong). So, the update in the earlier sp locks the record, which blocks the update from the later one. This just causes the system to hang as it waits for the lock to be released, which doesn't happen as it's all the one UP transaction, although separate transactions in the SQL server.

My solution is to create my own stored procedure, which will call all the CRM's procedures, so this can be implemented in the one Direct SQL task, and therefore the one SQL session/transaction. This will take a bit of work to implement, as there are a few sp's to be called, and each will involve cycling a set of query results, and calling for each record.

So, before I embark on this, I was wondering if anyone has any better solutions? Is there, for example, some way of having multiple direct SQL tasks in the same SQL session/transaction?

TIA
Mike Bannon
DataFormation Ltd., UK
Re: XPA 3.3: Creating a batch to clear the FormUserState.xml file for specific forms

Derek Ernst
 

Thank you for the reply Todd! 

I had actually played around with your method and managed to get it working. I was just trying to avoid having to add this to all programs in the future when we make updates. 

If I am unable to get my method to work I will be resorting back to adding a global function and running it in the task prefix of each online screen. 

 
Re: XPA 3.3: Creating a batch to clear the FormUserState.xml file for specific forms

Wes Hein
 

What I have been doing lately is using the program/task/form number in the 'User State Identifier' (Should maintain unique values that way), then adding another number or letter to the end, when the form changes, edit the last character so it gets a new entry.  I have found that for complex forms sometimes need a complete FormUserState reset.

I see XPA has added a 'Persistent Form State Version' property.

Wes
Re: XPA 3.3: Creating a batch to clear the FormUserState.xml file for specific forms

Todd Baremore
 

Derek,

Try this:
Set a virtual in the Main Prog with the current version of your app.  v_Current_Version
Create a table named UserFormReset
UserID | ProgamName | UserVersion
Add a unique index on UserID +ProgramName

Create a batch program (End Condition Yes, After) with a Link Write (with inits) to the table above on UserID and ProgramName, and the following parameters:
pi_ProgramName    Alpha 40
po_ResetForm  Logical

Record Suffix
Block If v_Current_Version<>UserVersion
Update po_ResetForm with 'True'Logical
Update UserVersion with v_Current_Version
EndIf

Add virtual v_Reset_Form to the program which may need to reset its form. From the TP call the batch program above.
Evaluate FormStateClear  Cnd: v_Reset_Form

I have not tried this, so I have no idea if it will work.  It does seem reasonable though......
Todd
On 4/30/2019 11:17 AM, Derek Ernst wrote:

Hi all, 

I was wondering if anyone has any experience with modifying the FormUserState.xml file for their application manually. 

We have a client that has said our two FormStateClear() function keys for resetting forms has not been working out for all their users. Basically, the users are not always competent enough to use these functions after being told to do so repeatedly, and rather than wasting time with these problems, they asked if we could reset the forms automatically when we make an update to a form. We have our own system in place for making changes to programs, and I thought that given I have a list of all our changes by program #, I could just write a small utility program that will go in and clear the saved form state details for the task when each user logs in.

My thought is that when we load new changes to our clients server, we will also include a file that will have all program numbers for all the modified forms. Upon logging in, the system will import the file to a memory table with the saved task numbers. I will run a batch that will search through Progs.xml, and using the indexes, get the internal id for each program number. I would save all the ids I need in the memory table as a separate column and then go to the users FormUserState.xml file and search for <Form> tags with the internal ids and removing the node. After doing some digging, I will need to consider different Form Ids as I believe that these are created based on the program #, task # and form #, so there could be multiple Form nodes for the program. 

I know that I will also probably need to implement some sort of logging system to know that each user has ran this batch before the next version has been placed on the server. Almost weekly we are loading new files to our client's servers for new program changes so there could be a chance that a user has not logged in over the week to receive those changes. 

I would love to hear your thoughts and any advice on achieving this. 

Thanks in advance :) 
XPA 3.3: Creating a batch to clear the FormUserState.xml file for specific forms

Derek Ernst
 

Hi all, 

I was wondering if anyone has any experience with modifying the FormUserState.xml file for their application manually. 

We have a client that has said our two FormStateClear() function keys for resetting forms has not been working out for all their users. Basically, the users are not always competent enough to use these functions after being told to do so repeatedly, and rather than wasting time with these problems, they asked if we could reset the forms automatically when we make an update to a form. We have our own system in place for making changes to programs, and I thought that given I have a list of all our changes by program #, I could just write a small utility program that will go in and clear the saved form state details for the task when each user logs in.

My thought is that when we load new changes to our clients server, we will also include a file that will have all program numbers for all the modified forms. Upon logging in, the system will import the file to a memory table with the saved task numbers. I will run a batch that will search through Progs.xml, and using the indexes, get the internal id for each program number. I would save all the ids I need in the memory table as a separate column and then go to the users FormUserState.xml file and search for <Form> tags with the internal ids and removing the node. After doing some digging, I will need to consider different Form Ids as I believe that these are created based on the program #, task # and form #, so there could be multiple Form nodes for the program. 

I know that I will also probably need to implement some sort of logging system to know that each user has ran this batch before the next version has been placed on the server. Almost weekly we are loading new files to our client's servers for new program changes so there could be a chance that a user has not logged in over the week to receive those changes. 

I would love to hear your thoughts and any advice on achieving this. 

Thanks in advance :) 
Re: MS/SQL performance in Magic XPA 3.2d very bad

Marc Buntinx
 

Problem solved:

1. Rechange fields from collation Latin1_General_BIN2 to database default
2. Set SQL database information:  ONLINE_ARRAY_FETCHING=N

BUT: I have to convert all tables with fields collation Latin1_General_BIN2 and than I've problems with Edeveloper and XPA 2.5
Re: Q-mode exported as E-mode [UP1.9

Frederik Soete
 

Hi Florian,

About evaluating 'Q'MODE as an alpha in runtime (V9 + XPA): that in fact does always give us an ascii 'Q' character.

Just now I checked an old V9 export (with added indentation, and some ellipses): while expressions did have 'Q'MODE literals (not 'E'MODE), the initial task mode 'Query' did show up as an 'E' back then:

PRG = {
    HDR = {...},
    RSRCE = {...},
    DTLS = {
        KEY = {
            MOD = N
        },
        DB = {
            DB = {}
        },
        FLW = B,
        DEL = N,
        END = Y,
        EMOD = A,
        MOD = {
            MOD = E
        },
        etc.

Apparently MSE made a change in the direction of uniformity, by always using 'E' for query in their internal formats in UP/XPA.

Why then did they not use a consistent symbol 'Q' for query? I do not have an explanation for that.

Have a good day,

Frederik Soete.
Re: Q-mode exported as E-mode [UP1.9

Frederik Soete
 

Hi,

@Luuk: In the XMLs an InitialMode @val-value 'E' stands for "Query," while an empty value (or ommitted?) with an @Exp-value equal to an expression ID stands for "By Exp.".

@Henning: I do not think OS language settings really change the representation of task modes. I think UP/XPA's internal 'E' symbolizing query mode applies world-wide. Maybe the 'E' is even older than UP/XPA.

@Florian: I have never tried displaying the result of studio expression 'Q'MODE as an alpha field in a magic runtime. Not in V9, UP, XPA. It occurs to me, that if we did, the alpha field would perhaps also show the strange 'E'. I would have to check sometime at the office. At any rate, the symbol 'E' for query must have been intentional at some point in the history of the programming language.

Bye,

Frederik Soete
Re: Q-mode exported as E-mode [UP1.9

Luuk
 

Doy you have an expression as your initial task mode? 

below is from 3.3a help:

M

Modify

Edit existing records and delete records. This is the default mode.
When you are in Modify mode, you can also create records. This is a specific mode in Magic xpa called Create in Modify. You can move to this mode by using the Ctrl+M keyboard shortcut.

C

Create

Create new records. When you are in Create mode you can only see the records that you have added in the current session. To view all of the records, you need to switch to Query or Modify mode. You can move to this mode by using the Ctrl+E keyboard shortcut.

D

Delete

Delete all records of the data view. Available in non-interactive Rich Client and Web Client tasks, and Batch tasks. Non-interactive Rich Client support – Since version: 2.4

Q

Query

Scan through records, without updating the records or deleting them. You can move to this mode by using the Ctrl+Q keyboard shortcut. This is the mode you want for “lookup” or “view only” tasks. However, the user can still change the mode from Query to Modify by selecting Options->Modify Records (Ctrl+M), unless you prevent that by also setting Task Properties->Options->Allow Options to No.

In Batch, the data sources are opened in Read Only mode, which does not allow any update to the scanned records, even if the data source itself was opened for Write access.

See also Query Mode Behavior.

P

As Parent

Run a task or program whose mode is identical to that of its parent in runtime. If a program with the As Parent task mode in runtime is not called by another program called from the menu, the task mode defaults to Query.
When the Parallel Execution task property is set to True, setting this property to As Parent will appear as an error when you run the Check Syntax.

If a program with the As Parent task mode is called in a subform, then this property is evaluated:

  • In Online – whenever the subform task is opened.

  • In Rich Client – whenever the parent task mode is changed. (Since version: 2.2a)
    On mobile devices, the property will be evaluated once when the subform task is loaded.

E

By Exp

Specify an expression in the Initial Mode Exp property, which will be evaluated dynamically at runtime to determine the Initial Mode.

Re: Q-mode exported as E-mode [UP1.9

voss@...
 

Hi Florian,

I think it has to do with local/language-settings. Do you habe the same mgconstw.xxx in both systems?

Henning
Re: Providing Web Services with magic xpa3.3 and axis2

Gábor Bor
 

Dear András,

It's not simple example, you can find it on the server like /home/XXXXXXX(magic folder)/web_services/MobileDevices/src/com/XXXXX. This java source will "represent the webservices".

Best regards,
Gábor
Re: Q-mode exported as E-mode [UP1.9

Frederik Soete
 

Hi Florian,

I do not know the reason why MSE changed 'Q'MODE to 'E'MODE in expressions.

In any case all tasks also contain a task property 'InitialMode' which is also 'E' for query mode (perhaps also in Unipaas?). So you could say that Magic is now consistent.

I also recall an earlier bug our company encountered, long since fixed: using V9CONVERTER.EXE resulted in replacement of 'Q'MODE by ''MODE (so an *empty* literal); the more recent EXEs use 'E'MODE.

Just for your information,

Frederik Soete.
Q-mode exported as E-mode [UP1.9

Florian Groothuis
 

Hi all,

I'm working on a migration plan to get our application from UP to XPA. What I stumbled on was that UP (and XPA) exports expressions like "IF (A,'C'MODE,'Q'MODE)" as  "IF (A,'C'MODE,'E'MODE)". Why the E instead of the Q?

TIA
Florian
Re: Providing Web Services with magic xpa3.3 and axis2

T鏒h András
 

Hi Gábor!

Could you tell me, where exactly the "MobileDevices webservice sample" you mentioned before is?
I cannot find it in the Sample Projects magic xpa 3.3.
Or is that your private sample?

Thank You

András
Re: XPI access to application GUI #mc13lv

Andreas Sedlmeier
 

On Sat, Apr 27, 2019 at 05:48 PM, tomdzio wrote:
Hello, I remember that old iBolt had a tool allowing to read/write data to AS400 terminal screens. You could simply define a mask of the screen, assigne variables and ready.
Hi Tomasz,

I am not aware of such a thing in iBOLT. Maybe you refer to the "5250 Connector" from Orgarat, a german company with plenty of "Magic & AS/400" know-how ?
Not sure if they are still around and if they still do sell this. That was available long before iBOLT and it was used for things you describe.

Re 2.). Well there is a lot. You find such stuff in every Test-Suite basically. Maybe Autoit is what you look for: https://www.autoitscript.com/site/

Best regards,

Andreas
Re: Web style collapsing menu in XPA

R A Murali
 

Re: MSSQL LockInfo #tools

Luuk
 

maybe change:
ALTER PROCEDURE [dbo].[LockInfo]
to:
CREATE PROCEDURE [dbo].[LockInfo]

It's nice, most people who work with MS-SQL will have the need for someting like this.

A example of the output:
MSSQL LockInfo #tools

Thomas Titus
 

Hi Folks,
I have uploaded a MSSQL Stored procedure LockInfo() to the file area (https://magicu-l.groups.io/g/main/files/LockInfo.zip ).
This stored procedure lists actual data of the locked record along with user name, table name, hostname, duration of the lock etc.
FYI
Thanks
Thomas Titus.
5761 - 5780 of 195966