GUIDS

rbuser

Hi I'm using ReportBuilder 7 with D6.


Will the product be able to work with GUIDS in the near future????

I have a situation in which Master / Detail linking using GUIDS is producing
results which make no sense at all.

Use of unique identifiers in database schemas is nothing new and I'm
suprised that RB does not handle them correctly.



Cheers,
Ben

nardmoseley

The datapipeline linking traversal logic requires the the detail data be
sorted on the linking datafields. The database engine sorts the data, but
what is the collation order that it uses? RB would have to know this. You
have to be able to compare the linking field values.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nard,

Thanks for the reply.

I'm using SQL server and it does not sort unique identifiers
alphanumerically. It does however "group" all similair guid values together.

RB is detecting the unique identifier field as a STRING and therefore
expects that the data is going to be sorted alphanumerically - which it is
not... and this causes all the problems.

Is there any change / fix that could be made to "CompareLinkedData" you
could recommend ?? I don't mind losing a bit a performance at this stage.

Also, as asked in the previous post - will RB ever provide support for GUID
fields. I'm getting grilled for the fact that we are using a reporting tool
that is unable to use unique identifiers from our SQL server.


Cheers,
Ben

nardmoseley

The key to solving this issue is to determine the collation order for the
GUID datatype. I searched Google but have not found anything. I will
continue to research it.

Thus far the only information I can find was a newgroup post that stated
that it works differently under Access 2000 and SQL Server 2000. Access
converts the GUID to a char(38). The author of the message suggested
casting the guid as a CHAR (38) for SQL Server.

use northwind
go
select cast(newid() as char(38)) as myguid
from employees
order by myguid



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nard,

Thanks for the reply.

Yup, I've seen references to casting the field to a char. That would solve
the problem, but peformance would be poor - no indexing on a cast field. It
would also have a large impact on the rest of our product.

I've managed to find some info on google relating to how SQL Server compares
Guids - it turns out, that it has a lot to do with which OS you are running
on.

Anyway, for me the best thing to do is try and find out what "scheme" is
used to compare guids for SQL and Windows 2000.

I realize that in a real world situtaion it makes no sense to "order" guids,
but since RB is treating them like strings I am forced to take this route.


My plan of action is as follows:

1) Find the scheme used for comparison
2) Modify CompareLinkedData in ppDB.pas to detect GUIDS and use the
alternate comparison so that it can return 0 or -1 or 1 where appropriate.


Do you think this could work??

Cheers
Ben

nardmoseley

Please let me know what you find and I can make the necessary modifications
to the RB source code. We can add code that detects the OS and performs the
appropriate logic.

CompareLinkData is part of the code that needs to be modified.

The other code that needs to be modified has to do with the accessing the
data as a GUID rather than as a string. ReportBuilder converts Delphi's
TFieldType to a TppDataType. This code is in ppDBPipe. We'll probably have
to add a new TppDataType (dtGUID), add a TppDataPipeline.GetFieldAsGuid
method, and then override it in ppDBPipe.




--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nard,

Part of the problem is that the SQL Server is (most of the time) not running
on the same machine as the reporting engine. So, determining the local OS
would not work. Maybe a property could be exposed that allows the user to
specify the OS of the SQL Server.

Im still trying to find an algorithm for GUID comparison on Windows 2000.


Cheers,
Ben

rbuser

Hi Nard,

Suppose I was not able to find / create a suitable GUID comparison function,
how would you procede?????

I find it almost impossible to believe that I am the first person who is
trying to use RB with SQL server and unique identifiers !!

nardmoseley

There has to be a collation order that is applied by the database engine.
Without a comparison function the only option would be to look thru the
entire dataset - which is very slow and would require a much bigger change.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nard,

Ok, so the only plan of action here is to find the collation order for
unique identifiers on SQL Server on Windows 2000.

I have been able to find some info and will investigate further.


Cheers,
Ben