Issues with an INT64 parameter in a Com UFL..

[NLD]

I have a COM UFL written in Visual Basic.  Been working for years with CR2016 designer and SP34 of the Crystal Reports for Visual Studio.  32 bit.  My database has had a INT64 (DBTYPE_I8) column added to it, and I'd like to do some custom formatting with this value.  My reports can read the column just fine, display it as a number, etc, but I want to pass it to a UFL function as a 64 bit int.  In Visual Basic, visual studio 2019, a Long datatype is an INT64.  So I added the world's simplest function to the UFL:

Public Function TestLong(ByVal value as Long) As String  

 TestLong := "TestLong"

End Function

I'm not calling this function in the report, but it is in the dll.  I regasm'd it, copied it hither and yon, etc etc just like always.  Yet the mere presence of this function causes my DLL not to load.  In CR2016 I get the 'saved data will be discarded since one or more formulas failed to compile' and from CRforVS, at runtime, I get the infamous 'UFL u212com.dll that implements this function is missing'.

Obviously Crystal is not liking my UFL with this function using a Long parameter.

I recompiled as 64bit, regasm'ing the 64 bit version, and used CR2022.  Same results.  If I take this function out of the dll, all is well.  Put it in, fails.

I also changed Long to Int64 in the VB code to see if that made any difference.  Same results.

Anyone have any ideas?  Anyone using a COM UFL with a function taking an INT64 parameter?

[DonWilliams]

R&D is still looking into the issue, He confirmed Int32 and Int16 does work but to get consistent results you must register the UFL using 64bit regasm for CR 2020 to use.

[ido_millet · Accepted solution]

Most likely, your record selection is using a Crystal function, forcing the filtering to be done by Crystal rather than by the DBMS. Google the topic of "optimizing Crystal Reports" and you'll see various tips on how to avoid that problem. 

Since you are familiar with creating UFLs, you could implement the SQL conversion using a formula that uses a UFL function that calls the SQL statement and returns the result of the conversion. 
Ken Hamady maintains a listing of 3rd-party UFLs here.  My CUT Light UFL provides such an SQL function. 

[NLD · Accepted solution]

I appreciate the help, I really do. I believe you have assisted me on issues in the past. I see the SQL function being passed to my database. When I use the preview from the design, again it is all sent to the database. But the initial load of the report in the designer uses no filtering. As for the UFL function, this reminds me of the song ' theres a hole in my bucket'. I originally wanted to do this with a UFL function pass the column value directly. The column is an INT64, thus the original issue rears its ugly head: i can't create a UFL function that takes an int64 param. I'm going to work around these apparently Crystal issues by adding a new column to the table which will be a string representation of the INT64 column. Ugly, but resolves my issue.

[DonWilliams]

The problem you are having is due to CR 2016 is a 32 bit application, it can't handle 64 bit integers. 15 digits plus one for the sign is all it can handle, always been that way, it's the nature of C++ Int's.

You could try CR 2020, it's 64 bit and see this KBA on how to create a C++ or COM UFL:

1603381 - How to create a C++ UFL using the new Unicode UFL interface in Crystal Reports 2008 & late...

Other than that Ido's work around is one option, the other is to use a Stored Procedure to break the value into 2 parts and put them back together using a field object and drop the most significant+least significant field into the object so they appear as one value.

[NLD]

Thank you for that response. A long long in C++ is 64 bits regardless of whether the application is compiled 32 bit or 64 bit. Nonetheless I did try this with CR2020SP4 with the same results: If a have a function in the UFL with a 64 bit param CR2020 will fail to load the UFL. Very repeatable.

[NLD]

I meant to keep typing but the single line box for reply is difficult for me. Ido's SQL Expression is a great idea, but CR2016 and CR2020 insist on loading the entire table I want to use this on everytime I open the report or modify the SQL Expression. My table has millions of rows. It takes forever and for what purpose? Is there any way to disable that?

[DonWilliams]

Correct but internally CR 2016 will not handle 64 bit int's, it simply is not capable of dealing with it, as I said it can only handle 15 digits+sign. As for CR 2020 I'll ping the developer to have them look into 64bit int's, it could be u212 wasn't updated. For Ido's suggestion SQL Expressions will query the DB for every record returned. have a look at this KBA on using Expression: https://userapps.support.sap.com/sap/support/knowledge/en/2344277

[NLD]

Ok, that's good info re: CR2020, but perhaps you misunderstood my SQL Expression issues. Perhaps it should be its own topic. My SQL Expression is a SQL Function I want to apply to a column in a table. Of course it executes for every row returned when the report is previewed or run. But then, there is a where clause on sql statement. My issue is that loading the report in the designer performs a select <func>(table.column). That's the entire table. What possible purpose could that serve? For me, it makes this solution unusable. Is there any way to disable that?

[DonWilliams]

Did you look at that KBA I posted, it explains a lot oon how SQL Expressions work in CR

[NLD]

Yes, Don, I did read the KB. I believe I am using the SQL Expression for its indended purpose. Basically I want to perform my database function TOSTRING on a column for every row returned in the query. That works fine when I execute the report for real. My where clause is applied, a limited number of rows come back, and ToString is performed on my column for every row. Perfect. Did you understand what I claim the issue is? Simply having this expression in my report results in a full table read when I load the report into the designer. No where clause is applied, not even the one in my record selection criteria. My table has potentially millions of rows.

[ido_millet]

Sounds like you simply need to create an SQL Expression. 
No need for a UFL for such a simple case.

[NLD]

Thank you for the feedback. Of course I simplified my question as much as possible to focus on the root cause of my issue: Why can't I have an INT64 parameter in UFL function? Nonetheless, your suggestion of a SQL Expression for my TOSTRING(table.column) is a good one. I can reference that in my formula and it works great a runtime. The Designer though (I'm using CR 2016 at the moment) insists on doing a select TOSTRING(table.column) every time I look at or modify that SQL Expression. There are millions of rows in my table and this takes forever. Is there any way to suppress the design from doing that? I can't put a where clause on the SQL Expression because the real clause for the SQL is created at runtime.

[ido_millet]

Use Integer. If you need to accommodate larger values, pass as String and convert on either side of the method.

[NLD]

Thank you for that feedback. Integer is not my solution as the values of this database column are much larger than ints. The String idea is a good one though however how to realize? I want to manipulate this value inside a formula. The ToText built in is no good it must internally convert the value to a double. What I'd really like is a way to not select column Table.LongVal, but instead select TOSTRING('table.longval') where TOSTRING is a SQL function my database knows about and have access to that in my formula. I just can't seem to find a way to do that.