Data Retrieval Shortfall

[Former Member]

I've contracted my development application and database out to a developer to do some extraneous work.  However, when testing the application in the developer's environment, datastore retrievals and embedded SQL do not return all the data - it's as if the SQL is only retrieving a small sub-set of data.

I've used Team Viewer to test the retrieved data in the developer's environment.  What SQL works fine in PowerBuilder's ISQL will only return a subset in the application. 

For example, a retrieve in ISQL using SQL code copied from a datastore returned an expected 112 rows but only 12 via the datastore when debugging the application.  Doing a SELECT MAX(id) in imbedded SQL returned 12 whereas the code in ISQL retuned a correct 668.   Embedded SQL used to retrieve string values returned NULL although the values sought were towards the end of the tables - that's if the SQL only searched early rows of the table.

Neither I nor the developer have encountered such a problem.  We're both using PB 12.5.2, Windows 8.1, and MS SQLServer - version 2008 in my case (not sure of the developer's version).

[Former Member]

Is this issue happening only on one specific table or with other tables as well?

If on only one specific table, I would suggest retrieving one row at a time and see which row is preventing the retrieval to continue. It appears to me that there is a data with a strange character and when the datawindow encounters that character, it stops the retrieval.

This kind of thing happened to me quite a while ago and all I did was update the data to remove the 'strange' character and the problem was solved. That character might not be visible to you. It's worth a try in my opinion.

Be sure to backup your data first.

[Former Member]

Thanks Neil,

It happens with several tables - I can't say which tables it doesn't fail on.  So I doubt whether it is caused by data with strange characters.  Tried, road-tested, and working Embedded SQL in my environment fails to correctly SELECT when running the application in the developer's environment.

But the copied SQL performs perfectly well in ISQL in the developer's environment on the same DB.

One simple basic value that fails to SELECT from within the application is 'ADMIN' from a staff table. 

In this respect, in my environment, SQL SELECT is case-insensitive - will return 'ADMIN', admin', 'Admin'.  I confess to not knowing enough about MS SQL Server to know whether there can be a setting for case sensitivity.  But I doubt whether this is an issue as there are failures on numeric values as well as varchar.    For example, SELECT MAX(key_value) FROM ....;  Such a call within the app may return, say, 26, but the same SQL in ISQL will return a correct value of 646. 

[Former Member]

More things to try if haven't been done yet:

1. Perform a full rebuild of the application.

2. Create a brand-new datawindow to retrieve the data and try retrieve the data from the IDE and from a running application.

[ricardojasso]

William,

Based on all the input you've provided so far I think of only three possible causes for this problem:

1) The database connection parameters are different between the IDE and the running application. If these were exactly the same then this shouldn't be the cause, but you can check the database connection syntax on the preview tab of the database profile setup against the one used by the application to be sure they are so, including the LogId parameter.

2) The PB dlls used to access the database from the IDE are different than the ones used when running the application. I don’t know if this is possible but if the behavior is different inside the IDE than when running the application then it’s worth checking. By the way, I assume you are running the application within the IDE and not as a standalone application. If on the other hand you are running the application using the runtime dlls then this is more worth checking to be sure it is not using different versions of PB dlls (e.g. 12.5 or 12.5.1 against 12.5.2) which have the same name. Or maybe you are deploying the application as “32-bit or 64-bit” or “64-bit” modes while the IDE runs in 32-bit mode and this might use different dlls or in a different way. Maybe other members of this forum can help as we work only with version 12.5 and 64-bit deployment was introduced in version 12.5.1.

3) The running application executes tasks before the retrievals that consume memory which then restricts the amount of data retrieved in the datawindow. To check this, I’d suggest you create a single datawindow and place it inside a single window in a new empty application and try. This will reduce the amount of memory needed to run the application and it might now get the complete result set.

Regards,

Ricardo

[Former Member]

Thanks Ricardo,

Unfortunately I'm not in a position to follow through on yours and other appreciated responses because the problem is on the developer's machine - I can only trust he is trying these recommendations and will respond if he identifies the problem. 

[Former Member]

Hi,

Can you test your application on Windows 7 ?

PB 12.5.2 was not tested with Windows 8.1.

There is a known issue with PB 12.6 on Windows 8.1 when using AdoResultSet.

This problem was fixed for PB 12.6 only.

Regards.

Abdallah.

[Former Member]

Thanks Abdallah,

Unfortunately I don't have access to the developer's environment.  I've asked him to visit this forum to see the responses kindly offered, which I know he has.  I also asked him to respond directly to the advice given.  He has not done the latter, nor do I know how much latitude he has as an employee to spend time testing.   

[Former Member]

Hey Bill,

Maybe there is an inadvertent filter on the datastore...

Does the where clause have dates?

Lars

[Former Member]

Good to hear from you, Lars. 

No filters involved - as mentioned above, the problem occurs with a variety of tables, with a variety of embedded SQL, and with at least one datastore. 

By way of example, the application, on booting, checks a whole raft of tables to update them programmatically with lookup data or additions to drop-down lists tables etc.

The respective SQL checks to see if the value exists, usually by embedded SQL, and if the value in question is not retrieved/selected, following SQL code adds the new, and ostensibly missing, value.

This adding of the 'new' values results in a series of DB errors, such as duplicate keys, as the application attempts to add values which already exists.

This in turn indicates that a different database is not being accessed. 

[Former Member]

Maybe space padding or case sensitivity plays a role... Just a thought

[Former Member]

Thanks again for the advice.  Because I do not have access to the developer's machine, I've contacted him and asked him to assess the recommendations kindly made, and provide feedback.

I can answer some questions.  First I do not believe it is a 'retrieve as needed' problem because the problem exists with embedded sql with a single SELECT, as well as with datastores, and probably datawindows - we didn't get that far into the application to test datawindows.

Nor do this think it a miss-match in dw column lengths to corresponding columns in the DB as the development application and DB are exactly the same as the one I develop in, and that runs fine - but it is interesting this caused a like problem.  This goes for 'retrieve as needed' settings in datastores/datawindows.

As for being connected to another DB, I've yet to get an assurance back from the developer but a screen shot I saw with the staff table opened in MS SQL Server had all required staff details.  But the application failed to retrieve an existing User Name via embedded SQL.  The User Name retrieved with the copied SQL when run in ISQL.

I am confident the developer set the DBMS as SNC as he copied my registry settings.  It seemed he used ADO.NET in the PowerBuilder connection profile, and had to use a UID and PWD in the registry settings (not required in my configuration with trusted connection) to connect to his server, which housed the DB.  This would have resulted in different connection profiles in the application to that in PB - but he said he tried SNC instead of ADO.NET and the problem remained. 

[ricardojasso]

You might have the same table created on the same database but with a different schema. Make sure you are fully qualifying the table in the select statement both in the datastore/embedded SQL as in ISQL. (e.g. dbo.Customer vs sales.Customer)

[Former Member]

Are there two of the same table? There may be one 'dbo.MyTable' and 'userid.MyTable' so that depending on which userid you log in as you will get different tables.

[Former Member]

No, only the one table - the problem occurs with a variety of tables.

[Former Member]

William Beale wrote:

It seemed he used ADO.NET in the PowerBuilder connection profile, and had to use a UID and PWD in the registry settings (not required in my configuration with trusted connection) to connect to his server, which housed the DB.

Maybe it is a row level security issue if the developer and you are connecting with different users!? (different permissions -> different results)

[ricardojasso]

How did the developer create the database in his environment? Was this done by restoring a backup of the database or by executing a generation script? If the later, what was the user/login used to execute the script? Also, what is the database role of the user?

[Former Member]

Thanks Ricardo,

Because my DB contains heaps of look-up tables (drop-down list data, tables for data validation such as given and surnames, postcodes, country names, state names, etc) I provided my complete DB file and log, as well as my source code for the application.  We are both working on Windows 8.

Stated differently, our working environments are essentially identical although I'm not sure of his MS SQL Server version.  Mine is 2008 so he may have migrated my DB to a later version.

[Former Member]

Is the Retrieve.AsNeeded property in the DW enabled (either in the DW Painter or via code)?

DW Painter: Menu > Rows > Retrieve Options > Rows As Needed

dw_1.Object.DataWindow.Retrieve.AsNeeded = "Yes" // or...via dw_1.Modify

[Former Member]

do a select count(*) in embedded sql in the app with the table that has 112 rows.

if it doesn't return the number of expected rows, then it has to be that the database is either corrupted or a different one than what you think it is.

[Former Member]

Also, try ...

Select @@Version

That should tell us if the SS's you guys are using are the same.  

[Former Member]

I once had the exact same problem. Here's what I discovered: when I originally created the DW, one of the string columns was 100 character long. Then that column was changed in the DB to 1000 characters. My application suddenly no longer retrieved all the rows. I simply opened the DW in source edit and changed "type=char(100)" to "type=char(1000)". Since then, I always make sure my DWs are in synch with the DB.

[Former Member]

Hi William;

  FWIW: Your description of the problem seems to me that the application is somehow connecting to a different instance of SQL Server and/or the database for this to happen. The only other possibility might be a different version of SS which has a DML parsing bug.

BTW: How are you and the application connecting to the DBMS (ie: OLE-DB, ADO.Net, ODBC, SNC, etc)?

Regards ... Chris

[Former Member]

Thanks Chris, I use SNC, the developer ADO.net

[Former Member]

Hmmm ... interesting variance. ISQL would use "direct connect" (aka like SNC). I wonder if this is an ADO.net issue?

I wonder what would happen if the application used SNC instead?

[Former Member]

I've since been in contact with the developer and he tried SNC and said he got the same problem 

[Former Member]

I think the next step then would be to use a PB SQL trace.

For example:

SQLCA.DBMS = "TRACE SNC"

  - or -

SQLCA.DBMS = "TRACE ADO.Net"

Then in the SQL trace, check:

a) SQLCA connection information

b) DML being sent from the in-line SQL

c) result set coming back & return codes.

Now compare that to a trace where the inline SQL works as expected and look for discrepancies.

[Former Member]

Try doing a database trace by putting 'TRA ' at the beginning of sqlca.DBMS. All SQL statements will be written to a file.

Then open the file in Notepad and you can see what SQL statements were executed and what the results were.

My guess is that it is connecting to a different database than you expected.

[Former Member]

Thanks Rene, Roland,

I did check the PB connection process, and the registry settings all appeared as though I was connecting to the one DB.  Moreover, there would be no point for the developer to create a second DB and empty it.

I gather it not a PB problem beyond some DB connection DB parameter.

[ricardojasso]

Can you show us the Database Connection Syntax from the Preview tab of the database profile the developer is using to connect via Interactive SQL, and the corresponding connection code in the application?

[Former Member]

Are you shure that you use the same database and the same schema in ISQL and application.

I do not think that this is a problem with powerbuilder.