Update Browse gets crazy when I connect it to a DB

[lugregu]

Hello!
Please,
i have a dictionary, and an application. Very simple they both are.
The application has a window with a list of entities from which we can acess to the detailed info of each entity.
It belongs to the detailed info of a entity the list of phone numbers of it. As so, in the window with the detailed info there is another list - a list of phone numbers. The problem is, that when i try to change that list, after i insert, change or delete a record, the list does not show the right set of records that should show.
This problem only occours when i connect the application to a DB (either MSSQL or PostgreSql). If i connect the dictionary to TPS files, everything goes perfect.
Note the same application funtions and not functions if the dictionary is connected to TPS files or DB, respectively.

If anyone can help me, i'd be greatfull
Luís

 

[ShankarJ]

Are you refreshing the Browse specifically after an Insert,Change or Delete. If you are and the List is still incorrect it might be worth it to set up the MS-SQL Profiler and check what statement is sent to the back-end on a Refresh.

 

[lugregu]

Thanks ShankarJ,
How do you suggest to make a refresh?
I'm doing this way:
BRW1.resetQueue(reset:Queue)
thisWindow.reset(true)

I can't make it work this way. Tell me how you'd do.

 

[ShankarJ]

I normally use a BRW1.ResetSort(True) to do a forced Reset of the Browse. This always works when the Filter or Range or Data has changed. Try it Out.

 

[lugregu]

I tried it, but unfortunally didn't work, Shankarj.
I put it in the embed of the insert button, i put it in the accepted embed of the browse list, but, unfortunally didn't work.
I tried BRW1.ResetSort(True),
i tried BRW1.resetFromFile,
i tried BRW1.ResetFromBuffer

and nothing... it simple doesn't work well...
Do you have another possible solution?

Lugreguigreguis

 

[ShankarJ]

The first thing to do is to see if the browse is refreshed manually i.e. exit the browse procedure and load it again and check if the browse has refreshed to show the recent record additions or modifications. If it shows the records properly, then the problem can be resolved in Clarion BUT if the record additions and modifications are not shown then maybe there is a problem in your back-end DB.

Are you using a normal browse created from Clarion Templates i.e. without any 3rd party add-ons?

What version of Clarion are you using?

Is it a Page-loaded browse or File-loaded browse?

Are you using PROP:SQL, PROP:SQLFILTER statements which change the call to the back-end?

Have you tried using SQL-Profiler (for MS-SQL) to see what statement is sent to the back-end when you ask for a Refresh?

Does the Table in question have a Primary key?

 

[lugregu]

Anwsering your questions:

>The first thing to do is to see if the browse is refreshed >manually i.e. exit the browse procedure and load it again >and check if the browse has refreshed to show the recent >record additions or modifications.
>If it shows the records properly, then the problem can be >resolved in Clarion BUT if the record additions and >modifications are not shown then maybe there is a problem >in your back-end DB.
A: It is showed.

>Are you using a normal browse created from Clarion >Templates i.e. without any 3rd party add-ons?
A:yes

>What version of Clarion are you using?
R:5.5.

>Is it a Page-loaded browse or File-loaded browse?
A:I’ve tried both, but originally I tried Page-loaded. What is the difference?

>Are you using PROP:SQL, PROP:SQLFILTER statements which >change the call to the back-end?
A:Nop, I only use the templates.

>Have you tried using SQL-Profiler (for MS-SQL) to see what >statement is sent to the back-end when you ask for a >Refresh?
A:I don’t know how to use it. I’ll try to discover how to use it.

>Does the Table in question have a Primary key?
A: Yes, it has. I have a table called entity, and a table called telephone and they are like this:
Entity(code[PK], description)
Telephone(code[PK], number, entity[FK])

Can you give me a clue, say so ...

 

[lugregu]

Running the SQL Profiler i rimarked that after a delete, insert or update operation the select comand is like this:

SELECT A."CODE", A."NUMBER", A."ENTITY" FROM dbo.telephone A WHERE ( A."ENTITY" = '1' ) AND A."ENTITY" >= @P1 ORDER BY A."ENTITY"

I don't know why the where clause has this condition: A."ENTITY" >= @P1. I think this might be the problem, but i don't know how to change it in order to solve it. The Browses are all automatically created by the Clarion templates.

 

[ShankarJ]

The A."ENTITY" >= @P1 is how Clarion makes sure that it gets the right Page of records to show. Have you tried to see how it works with a File Loaded browse.

The ABC Classes normally use BRW1.ResetQueue(Reset:Queue) for a DeleteRecord action and BRW1.ResetFromFile for other actions.

For a Change of Insert Action, you could try BRW1.ResetFromBuffer assuming that the Record Buffer contents still exist.

 

[lugregu]

With a File Loaded browse the condition A."ENTITY" >= @P1 in the where clause remains.
I tried to do your sugestion, and i used BRW1.ResetQueue(Reset:Queue)after a delete action and BRW1.ResetFromFile for insert and change actions, followed by BRW1.ResetFromBuffer.
Unfortunally didn't work, and the browse still goes crazy (as myself too).