Greetings,
First time this error popped up!
SAP database, MS SQL .
Browse contains Invoice related fields, including these two fields.
CardName, nvarchar(100), Clarion CSTRING(101)
NumAtCart, nvarchar(100), Clarion CSTRING(101)
An ABC browse has been working since 2010, never missed a beat. Went through all the version of clarion to the latest 11, still beating.
Then NumAtCart was added to the same Browse. Now when the user click on a Print button, Generate an invoice, we get the error above. Only on some records…
Background:
NumAtCart: is the Customer’s Orders Number. I guess the Sales Agent copies and pastes from their e-mail the customers’ OrderNumber… So there might be “bad” characters stored in this field.
The error goes away when NumAtCart is removed from the Browse. But we need it to formulate part of the subject line of an e-mail.
Any suggestions?
PS! We are not allowed to write to the SAP SQL Database.
As I understand: nvarchar [ ( n | max ) ]
Variable-size string data. n defines the string size in byte-pairs and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^30-1 characters (2 GB). The storage size is two times n bytes + 2 bytes.
So I suppose in Clarion it should be CSTRING(203)
and you had no problem before because data was not reached max length
Thank you for the suggestion.
In clarion the Characters of CSTRING was updated to 203, recompiled, but still error persists.
What I meant above was that the Browser never had NumAtCart in the list before. Only when we added that field, did the error appear when we click on PRINT button.
The report also accesses that same table.
Good idea. Thank you for your input.
SELECT NumAtCard, len(NumAtCard)
FROM [MyDB].[dbo].[OINV] where len(NumAtCard) >30
Only two records appear. Both: LENGTH 33.
Removing them from the browse and adding them as a hot field, produces the same error.
Thank you all for responding.
Tried: “/IGNORETRUNCATION=TRUE”, still an issue.
Let me paint a bigger picture. 2 X list boxes 3 tables.
List box 1: Invoice header DBO.OINV joined to Customer Table DBO.OCRD on CARDCODE
List Box 2: Invoice line items DBO.INV1
Once we added the OINV:.NUMATCARD field, the error presented itself.
Making a new browse out of the wizard did not present a problem-it appears DBO.OCRD join provoked the issue.
Back to original browse: Removing the DBO.OCRD and returning the field OINV:.NUMATCARD. It works! YES!! yes !! why?? I have no idea. but
How would one bypass the clarion libraries? I have no idea!
(We try not to fiddle to much with drivers settings, considering they have been working for a few year)
Bypassing the template clarion browse / view engine I think is required, I think I also ended up hand coding the small update form for this table. Using the turbosql switch to talk direct to sql in the end.
Richard
you might be right but TBH I wouldn’t use a CSTRING in a key (because I would be nervous about crud after the terminating null), and if I ever did I certainly wouldn’t have a key over 1000 characters.
The error you are referring to could be due to storing the position in a variable which is too small? This one:
Error 78 is different to what I’ve been seeing, and whilst there is no harm in being nervous about the crud after the cstrings null terminator, if the code processing the key is using that crud, then the code processing the key has a bug, it shouldnt matter whether the cstring is 10 characters long or more.
Up to 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. The limits are 16 columns and 900 bytes for versions before SQL Database and SQL Server 2016 (13.x).
Columns that are of the large object (LOB) data types ntext , text , varchar(max) , nvarchar(max) , varbinary(max) , xml , or image can’t be specified as key columns for an index. Also, a view definition can’t include ntext , text , or image columns, even if they are not referenced in the CREATE INDEX statement.
Oracle is working to 6,398 bytes, which would be using the ODBC driver.
So I think the cstring(1000) might be the internal limit for Clarion looking at the key which is a single field unique key, where the single field is a cstring(1000). As soon as I specify a cstring(1001), I get the error message, what ever it was.
The column in SQL is a varchar. SQL handles using that as an index very well. I have 100s of indexes on varchar columns.
Perhaps that was an issue with the TopSpeed driver? I always used STRING for character columns back then.
I seem to recall reading about a CSTRING key problem way back in the early CW days but thinking about it perhaps that person was using a GROUP that contained a CSTRING and so in that case the crud would be used. To get around it some people “cleaned” their cstrings by clearing the crud after the null terminator to either spaces or <0>.