uCanAccess - cStr in join not working , but Val does work - Possible bug

[Derek Visch]

Using ucanaccess-3.0.4.jar and jackess-2.1.3.jar

Returns Null for the Application Type Name due to the CStr conversion on the Customer Card ID field in the second join.

SELECT DISTINCT
Customer.[Customer Card Id],
Customer.[Account Balance],
[Eligibility Group].[EligibilityGrou],
[Application Type].[Application Type Name]
FROM Customer
INNER JOIN [Eligibility Group] on [Eligibility Group].[Eligibility Group Id] = Customer.[Eligibility Group Id]
LEFT JOIN Applicant ON Applicant.[Student Id] = CStr(Customer.[Customer Card Id])
LEFT JOIN Application ON Application.[Application Id] = Applicant.[Application Id]
LEFT JOIN [Application Type] ON [Application Type].[Application Type Id] = Application.[Application Type Id]
WHERE ((Customer.[Customer Card Id]) Is Not Null AND (Customer.Status=0) AND Customer.[Customer Card ID]=someNumber);

This sql statement below does not return null and returns the correct value for the Application Type Name field for this user. The pervious sql comand returns null for the Application Type Name column

SELECT DISTINCT
Customer.[Customer Card Id],
Customer.[Account Balance],
[Eligibility Group].[Eligibility Group Name],
[Application Type].[Application Type Name]
FROM Customer
INNER JOIN [Eligibility Group] on [Eligibility Group].[Eligibility Group Id] = Customer.[Eligibility Group Id]
LEFT JOIN Applicant ON Val(Applicant.[Student Id]) = Customer.[Customer Card Id]
LEFT JOIN Application ON Application.[Application Id] = Applicant.[Application Id]
LEFT JOIN [Application Type] ON [Application Type].[Application Type Id] = Application.[Application Type Id]
WHERE ((Customer.[Customer Card Id]) Is Not Null AND (Customer.Status=0) AND Customer.[Customer Card ID]=someNumber );

Also this database does return this when running

WARNING:Detected Unique constraint breach, table App Activity,
WARNING:Error in the metadata of the table App Activity: table's row count in the metadata is xxxx but (about 30% less) records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.

I'm not sure why this happens.

Thanks in advance!

[Marco Amadei]

Hi Derek, it's not a best practice to compare strings whose the format may depend on the jvm current locale, so the second query is correct, whilest the first one is, ehm..., challenging. It may give something like a decimal rappresentation.
What if you print out the result of
select CStr([Customer Card Id]) from customer?

[Derek Visch]

Select [Student Id] from Applicant returns results like
(All made up numbers of course)
8965215
6431253

Meta Data for the column returned

ColumnIndex	getColumnName	getColumnTypeName	getPrecision	getScale	isNullable	getTableName	getSchemaName	getCatalogName	getColumnClassName	getColumnDisplaySize	getColumnLabel	getColumnType	isAutoIncrement	isCaseSensitive	isCurrency	isDefinitelyWritable	isReadOnly	isSearchable	isSigned	isWritable
1	Student Id	VARCHAR	30	0	1	Applicant	PUBLIC	PUBLIC	java.lang.String	30	Student Id	12	false	false	false	false	false	true	false	false

And Select CStr([Customer Card ID]) from Customer

returns things like
8,965,215
6,431,253

Meta Data for returned column

ColumnIndex	getColumnName	getColumnTypeName	getPrecision	getScale	isNullable	getTableName	getSchemaName	getCatalogName	getColumnClassName	getColumnDisplaySize	getColumnLabel	getColumnType	isAutoIncrement	isCaseSensitive	isCurrency	isDefinitelyWritable	isReadOnly	isSearchable	isSigned	isWritable
1	C1	VARCHAR	16777216	0	2	<null>			java.lang.String	16777216	C1	12	false	false	false	false	true	false	false	false

Looks like you're onto something

[Derek Visch]

Seems odd that both the types get returned as VARCHARs but had different String outputs from the jdbc driver. The addition of the commas is throwing me off a bit as a conversation to VARChar should only add the commas if you specefically ask right?

[Marco Amadei]

Your first statement doesn't make sense but we'll check again for numeric rappresentation consistency within functions.

[Derek Visch]

Let me know if I can provide anything, sorry I didn't make sense in my previous comment.