Hi,
While browsing an Sql table in a list. Many times it shows extra records which are the same records repeated in the list. For ms Sql tables I used to add additional sort field and this used to fix it. Now I am using Sqlite table and it did not work with it.
Any suggestions are highly appreciated
I am using clarion 6.3
Regards
If the problem occurs after you page up or page down (or fewwer records than a browse-full), then the problem is that you don’t have anything the browse can use as a primary key to say (already got that one, don’t need to add it). It requires you adding additional sort fields so that in the Sort Order you have either all the fields in a key in clarion that is declared as either the primary key or a unique key (I think a unique key is fine, but even after all these years I’m not sure). MSSQL and SQLite will work exactly the same way – it’s a problem with what you have set up on the Clarion side.
If the problem occurs without paging then you have set up a browse with duplicates, e.g. you have added in a child file and are displaying only the parent columns.
Thank for your reply.
actually it happens as you described after paging up or down, and it is a single table with no related tables , also I already set the additional sort field using the primary key.
The weird thing that in the same application I had 2 tables; one is Mssql and the other is Sqlite and used the same structure and the same setting. It worked with Mssql and not with Sqlite.
That’s why I wrote this post.
Regards
Check the declaration of the file in Clarion. Clarion needs to know how to identify a unique record – so the key on the clarion side needs to be declared as primary or unique. It may be declared as a primary key in SQLite, but unless Clarion knows it is a primary key (from the dictionary), you will still have the problem. You might also have problems with update forms.
Hi,
It is defined in the dictionary as primary and unique but still the same.
Thank you and Regards
Might be a good problem to bring up and look at at the weekly ClarionLive open webinar on Wednesday.
It would be nice.
thank you
Regards
This is usually an issue with the sort order for the view not resolving to a unique row.
Can you post your VIEW definition and exactly what is being used as the sort order?
Hello everyone,
I think I had a clue for my problem and why did it work in Mssql and and not Sqlite; mostly it is because of the field width I am using which is around 1000 and as far as I know that Mssql allows maximum 900 chars and Sqlite Allows much more. because I tested other fields in Sqlite other than the one I wanted to use and they were ok.
Some other reason could be that for Mssql I use the native driver while for Sqlite I use ODBC driver (as I am using Clarion 6.3).
Thank you all,
Regards
I have a vague memory that the POSITION command in clarion is limited to 1024 characters (i.e. that’s what Clarion declares it as), even though the help says the size of the string returned is 4 bytes plus the sum of the sizes of fields in the key. So yes, a field with over 1000 bytes in it might cause trouble.
I don’t think the driver is likely to be the issue. The MSSQL driver is a modified ODBC driver. I don’t think it would behave any differently than an ODBC driver for SQLite.
One thing you could do is:
create a view on the server, something like:
create view myfileview as
select row_number() over (partition by customer_id order by mylongtext) rn,a.*
from mytable a
and then import myfileview as a table, and use the customer_id and rn columns to make your primary key. That way clarion only has to worry about two little columns and the long text column isn’t involved in the key (or ordering so far as clarion is concerned).
I will try that.
Thank you.
Regards
The Browse template generates a Queue to feed the LIST that has a Brw1::POSITION variable that is STRING(1024).
