Jump to content


Photo
- - - - -

Collation is SQL


4 replies to this topic

#1 bkemp

bkemp

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Male
  • Location:South Africa

Posted 16 May 2005 - 09:30 AM

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

#2 George Macken

George Macken

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 248 posts
  • Gender:Male
  • Location:Co. Wicklow, Ireland

Posted 16 May 2005 - 12:46 PM

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

#3 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 16 May 2005 - 01:10 PM

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

#4 TalentedFool

TalentedFool

    Member

  • Members
  • PipPip
  • 43 posts
  • Gender:Male

Posted 16 May 2005 - 03:29 PM

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
Thanks

Lee

#5 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 31 May 2005 - 12:19 PM

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.
Nothing's as simple as you think



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users