Plex 2E

Hi, I was getting zero counts where I knew there were records and so I mocked up a simple example that illustrates the issue.

If I then call this function repeatedly with the code below...

I get the following results where there should have always been 212...

I have run SQL Server Profiler and all these select counts are being executed correctly. I created a stored procedure and called it via exec sql and still the same results. I even just put in "select 212" to hardcode the number returned by SQL Server but this still came back with erratic results where zero was returned more times than the real value. The view status is always successful.

Has anyone experienced this and is there a solution?

Thanks

Chris

dont think you need the fetchnext as the first row is returned automatically to your variable

That's not the way we've used this in the past. For select statements, the exec sql will "position" in plex terms (or in actuality open the cursor) and then to return any values the fetchnext is required.

I took the fetchnext away and I just got zeros and no correct values.

can you try with out the ouput varaible specified but to use a view, does not have to implmented just to have the view varaible present then map the output from the view latter on

George - this does make it work consistently thanks - but I have got hundreds of exec sql statements where the returned values are assigned to a variable. Is this no longer supported properly in 7.2? I guess I can log a ticket.

yep I thought so, wanted to use the explicit variable in some patterns instead of the view but found it really did not work in various scenarios and esoecially not with C#.....wasted a **** of lot of time which at least you have benefited from...

contact CA and send my regards as I simply could not be bothered to do.

appreciate you marked correct

But in fairness you were not using the statement correctly as the fetch next is not used for a count...not you but the previous coders...so if I was ca i would say yes we will fix the explicit variable mapping but you will have to fix your code...sorry

Appreciate with what you are saying about using count but this scenario is exactly why I am using the explicit variable. Also, the problem is still the same if I do a standard select on a column or multiple columns that are properly represented as fields in the explicit variable.

Try also Terminate instead of Return

the fetchnext is still required

Interesting, if I put a "Close" statement after the fetchnext, it works consistently...

The cursor does not seem to be closed properly as it was in the past. This would be a simpler change but still a lot of work to change code that worked as it should do before and is in line with syntax etc.

Is your function closing correctly ... ?

Exiting a C++ Function

A function with an Events Handler does not exit until the user closes the panel or until a Return statement is encountered. In both cases, the function then closes down and control returns to the calling function.

Functions with no Events Handler closes down when the last instruction in the action diagram has been executed or when an explicit Return statement is encountered. In either case, control returns to the calling function.

In a C++ function, Terminate is the same as Return.

Hi,

Nearly same code works for us at plex 7.0.

Does output variable have single numeric field.

Store output value to a local field.

Add close statement after FetchNext.

Regards

AA

Hi,

Yes the output variable has just one numeric field and the code does have a return statement at the end - just in case. It also behaves the same way whether it is external or internal.

I think that Close statement must be key as that made the difference - however, we are told that this is handled automatically in a view function and so it was in the past.

I'm sorry but CA are going to disagree with you

"If you use Exec SQL, you should not use any of the standard CA Plex database access statements within the same function. The one exception is FetchNext in conjunction with a SQL SELECT" CA Plex Help

as I said even if i was doing a count or multiple rows the explicit variable was flaky and simply did not work for C# BUT

A count should not use FetchNext  see plex help "SQL statements are defined within a source code object. When using SELECT statements, the first row selected is returned into the specified output variable. If no output variable is specified, the view variable is used."

I am still using a select statement - whether I use aggregate functions, scalar functions or not - and I have a properly defined field in the variable to receive the value. Anyway, with a more standard select to an explicit variable it does not work either. We'll see what they say.

"Anyway, with a more standard select to an explicit variable it does not work either." I have said that along.

I think there are two issues.

One = The explicit variable has got some bugs in C++ and and in C#. This is why I highlighted it to you early on dont use explict variables

Two = The manner you use Exec SQL

• If you are expecting ONE row you should not use FetchNext
• If you are expecting potentially multiple rows you need to use FetchNext to get the second, third, fourth etc records

WHich does give me an idea when it broke and how to you fix it. The introduction of Maximum Recordset Size Option happened post 4.5 plex which is where you upgrading from! This most likely introduced bugs. But what about you using Maximum Recordset Size Option  set 1 for your single select / count?

George - I am using Exec SQL and FetchNext as designed. You should have a look at a standard BlockFetch - you will see that you are always required to use a FetchNext to get the first record. The only difference here is that I am using an Exec SQL to "position" or declare the cursor - something that would by implemented by Position GE or Position EQ.

ok dokie when I came across your issue in Jan 2016 (Plex7.1) I noticed the variable issue and when debugging I noticed the FIRST row is mapped to the view with out FetchNext and I read Plex help to agree with that finding..but no matter, maybe I remember in correctly  and good luck with the runtime fix and glad you have the cause of your problem

Plex help screenshots that I think explain the proper use of Exec SQL to return rows:-

Thanks for your help George - I simply feel that since I am using it correctly I should just not have to put in the extra hours to apply a workaround for this new version of Plex by adding the Close statement.

All the best.

Definitely not adding the Close statement as CA Plex help states "If you use Exec SQL, you should not use any of the standard CA Plex database access statements within the same function. The one exception is FetchNext in conjunction with a SQL SELECT"

My red herring was the use of FetchNext cant think why I thought that now..cant recreate it....was using a TabledValueFunction but that still should have made no difference I think...My fault...FetchNext is required to get the FIRST record

But the variable issue was what led my to answer this thread as I knew that was an issue.

all coming back to me now!

FETCHNEXT is tied to the VIEW

Just try "FetchNext OUTPUT"  I thought it would work but it did not for me in Jan 2016 BUT it is works for me now in my simple test (more help for CA Support)

this works

This works also

That makes my application fall over(!) I can see that the optional variable is supposed to be a key variable to indicate what to restrict on. Unfortunately I don't think this fits with the way I am using it.

cool i made it fall over. Sorry, been fun Chris, think you are off to support. Create a tiny local model with an example of the error..unfortunately I think this is not just a runtime but also a generation issue..you could attach this issue https://communities.ca.com/message/241723305#comment-241723305

cheers

https://communities.ca.com/message/241723305#comment-241723305

Hi,

So, I found what was causing this issue.

As stated above, the EXEC SQL was done to a variable, not the "VIEW" variable, but another one with the same fields in the same order.

Using the "FetchPayingDocuments" variable, the cpp was generated with the access to the wrapper columns on the form of theWrapper->GetColumn(i) as can be seen on my second image on the first post.

Just for the sake of sanity, depite PLEX help indicating that we can do the EXEC SQL into a variable ("SQL statements are defined within a source code object. When using SELECT statements, the first row selected is returned into the specified output variable. If no output variable is specified, the view variable is used."), I decided to try and do it to the "View" variable.

This is the cpp generated:

As you can see, the code to access the columns on the wrapper is quite different and this does work withtout the previous issue of returning the columns on the correct order in odd fetches and not in even.

Best regards.

Hi,

The parameters input to an Exec SQL statement and the output variable fields that receive the output must exactly match in both number and type.

Exec works fine , if above condition is met.(including other requirements of Exec SQL)

View variable is recommended as it maintians the order that the Exec SQL returns output.

Regards

Hi,

I contacted CA Support and received some really great help from Stephen Slaughter. We discovered my issue was that I had build 21 of CA Plex 7.2 where in fact I should have had the latest build 22. As soon as we deployed version 7.2.22.0 Plex runtime objects over my application, the erratic behaviour was corrected and everything now works. Now I have upgraded Plex using the latest download.

For information sake, Build 21 runtimes resulted in unreliable data access on Windows 8.1 and Windows Server 2012 R2 operating systems whereas previous operating systems worked fine.

Thanks so much for everyone's help.

Chris

You are very welcome, Chris! This was an interesting case, and I enjoyed investigating it with you. It was fun! I'd be happy to investigate the issue you addressed to the community regarding disabling top level menus in MDI child panels as well. Let me know and I'll go ahead and open up a new support case for you. We can setup a meeting at your convenience so you can show me the problem in your environment and we can chat about it.

Cheers,

Stephen