4 replies to this topic

[bkemp]

Hi
We are using Proiv ver 5.5 2.0.4 to run our pension fund administration system connecting to SQL 200 server. We have a program which opens an ASCI (csv) file which contains a list of members on a pension fund. The program then compare and match the file's data to the SQL database and updates the matched ones. This works 100% at our offices.

At a client we have a seperate standalone copy our our system using the same verion of Proiv and the same source codes for all the programs. Here however it matches all the members up to U (A Mr Ungerer) and then stops. The proces say it's finished and that there are no more members on the database.

The only difference between us and them is the collation definition in the SQL database. Is Proiv using the collation when it compares data ?

Regards
Barry

[George Macken]

Hi

I've no major experience of SQL Server, however I have current experience using PRO-IV and ORACLE.

Could you better descibe how your pro-iv function is structured ?

Could you supply your customer/client site with another pro-iv programme, structured something similar to the problematic function but in this function maybe just output the keys/data to a log file, reading the tables in Lookup mode only. Really prove that the expected data is definitely present in both the source ascii file and also in the SQL table.

Whenever I'm using a SELECT in PRO-IV code to retrieve more than 1 record from an ORACLE table I always specify the ORDER BY command otherwise the Data is not always retrieved in Key Sequence but in the order in which it was added to the table.

SELECT FROM table_name
WHERE (whatever criteria you wish to specify)
ORDER BY (primary_key).

hope this helps

Rgds

George

[Joseph Bove]

Barry,

We found collation to be a significant issue on keys fields.

To this extent we specifically set the collation on our key fields to collate sql_latin1_general_cp1_cs_as

From what we've seen ProIV does not like data retrieved out of ASCII order. It typically stops retrieving.

If this is your issue, then see if the "U" record is followed by a "u" record.

hth,

Joseph

[TalentedFool]

Collation is a major influence on SQLserver and how it reads data from the database

sql_latin1_general_cp1_cs_as causes the following to be true

'A' = 'a' ,

where as sql_latin1_general_cp1_ci_ai causes the following

'A' <> 'a'

So if you are trying to match input from a CSV to the database I suggest you make sure that both sites are using sql_latin1_general_cp1_ci_ai or sql_latin1_general_cp1_ci_as if you're not worried about accents.

hth

Lee

[Richard Bassett]

The only difference between us and them is the collation definition in the SQL database. Is Proiv using the collation when it compares data ?

No, AFAIK, the ProIV kernel will not internally support any special character collations for sorts, string comparison in logic, key comparison for SEL-RANGE, key ordering in ProISAM etc. Internally ProIV only seems to handle raw ASCII/BINARY.
This is a big, big problem for application internationalization. Please note it is possible I'm out of date however.