Handling of LIKE with constant length char fields
Locked
 
|
Hi again,
I have a problem with readManyOf:where:.. Say I have a DB field that is of type char(11) and do a query like this: readManyOf: SomeClass where: [:row| row charField like: 'test%'] Then GLORP fills up the like string with Null characters up to the length of the field. In this case it would be like SELECT ...FROM ... WHERE charField like 'test%\0\0\0\0\0\0' It took me quite a while to find this, just wondered why the results are wrong, All is good if I fill the search string up to 11 characters. The results are correct then. In retrospect, I guess it's a question of luck whether this SQL statement works at all, maybe some DBs would get a hickup. Or they may work because the driver stops at the first NULL character and all is well again. Funnily, GLORP even counts the % character and really fills up to 11 chars including the percent ;-) I tried to find the place where the like subclause is being constructed but haven't had any luck so far. I think the like argument shouldn't be filled but rather be the length of the actual string (I mean 'test%') in that case. Or am I getting something wrong and can't expect SQL databases to handle LIKE for constant length CHAR fields? Joachim
-- You received this message because you are subscribed to the Google Groups "glorp-group" group. To view this discussion on the web visit https://groups.google.com/d/msg/glorp-group/-/cLO3OdjumuQJ. To post to this group, send email to [hidden email]. To unsubscribe from this group, send email to [hidden email]. For more options, visit this group at http://groups.google.com/group/glorp-group?hl=en. |
|
|
I forgot to mention: I am still working on the ancient GLORP version that ship with VA, so maybe the problem cannot be reproduced in current versions. I am really searching for the place to look at. Once I've fixed it, I'd post my results back here so that the changes can be integrated into newer versions if necessary.
--
You received this message because you are subscribed to the Google Groups "glorp-group" group. To view this discussion on the web visit https://groups.google.com/d/msg/glorp-group/-/vFJ-628qjMMJ. To post to this group, send email to [hidden email]. To unsubscribe from this group, send email to [hidden email]. For more options, visit this group at http://groups.google.com/group/glorp-group?hl=en. |
Re: Handling of LIKE with constant length char fields
|
|
In reply to this post by jtuchel
That would be coming from
the type conversion. So, LIKE would be a FunctionExpression, presumably
an InfixFunction. When that prints it will print using
printSQLOn:WithParameters: and that will do a convertValueOf:in: that
will eventually come down to the type of the attribute and the type of
the field and finding an appropriate converter. For a fixed length
string it's going to pad it with nulls, because it thinks that's what
the database requires, and it's probably correct if you're trying to
write it. Or maybe it's not, I'm not sure that the database wouldn't
accept a shorter string in a fixed-length field. And it might make sense
in an equality comparison, but certainly not in a LIKE.
Unfortunately, I don't have any brilliant ideas for how to do a good fix. If you're not doing writes, and if your comparisons are all LIKE, you could tell Glorp it was a variable length field and it might work. Otherwise it seems like we'd need to introduce the context in which the thing is being printed to the conversion, which we don't have right now. Or have the function expression somehow know that it needs to modify the type of its argument in some cases. -- You received this message because you are subscribed to the Google Groups "glorp-group" group. To post to this group, send email to [hidden email]. To unsubscribe from this group, send email to [hidden email]. For more options, visit this group at http://groups.google.com/group/glorp-group?hl=en. |
|
|
Hi Alan, Thanks once again for your prompt and exact answer. I think it's a RelationalExpression (at least my Breakpointing seemed to prove it), but you pointed me exactly to the right place. I could work around my specific problem by changing DatabasePlatform>>padString:for: to padString: aString for: aType | padding trimmed | aString isNil ifTrue: [^nil]. (self usesNullForEmptyStrings and: [aString isEmpty]) ifTrue: [^nil]. trimmed := self trimString: aString for: aType. (aType isVariableWidth or: [trimmed size = aType width]) ifTrue: [^trimmed]. "Do not fill in NULL characters if aString includes wildcards" ((aString includes: $%) or: [aString includes: $_]) ifTrue: [^aString]. padding := String new: aType width - aString size. padding atAllPut: self paddingCharacter. ^aString , padding This seems okay under the assumption that only SELECTs will include wildcards. But as you say, this may not really a fix, because INSERT and UPDATE of the char field including a percent sign is absolutely correct sql code. I need to test a little mpre with this. If the updates work with unpadded shorter strings, that still seems to be correct, but I am not sure if this will be true on all DB platforms... Joachim You received this message because you are subscribed to the Google Groups "glorp-group" group. To view this discussion on the web visit https://groups.google.com/d/msg/glorp-group/-/Dx8Q0-DR83AJ. To post to this group, send email to [hidden email]. To unsubscribe from this group, send email to [hidden email]. For more options, visit this group at http://groups.google.com/group/glorp-group?hl=en. |
|
|
Hi again,
my fix is of course nonsense. 1) Like seems to show very different results on different databases for fixed length char fields 2) As Alan already said, the above-mentioned method would also be used for updates and inserts, and the characters % and _ are valid characters to save in a fixed var field. So what I'll try is to treat the field as if it was a varchar. Let's see how far I get with this. BUT : ==== The handling of LIKE subclauses is somewhat broken in GLORP because of the padding. Reasons: 1) It cuts off characters that might be important for searching. Example: I have a char(6) column and want to search for '%test_%'. The database will only be asked for '%test_' because the characters after index 6 are cut off, and therefor would not answer rows containing 'test_1' --> The results of GLORPs like query is WRONG! 2) SQL allows for functioins in a Like clause like ESCAPE. These would be cut off. In the best case, they'd be cut off completely, so that the DB can at least answer something, but cutting off cut also lead to syntax errors. This is admittedly not likely to be a very common problem. So I guess the handling of LIKE should not use the "normal" way of converting the Function argument and should not pad at all. This is of course only possible if GLORP introduces some way of using different conversion routines for INSERT/UPDATE and SELECT in conjunction with LIKE... But then, maybe this is not worth the effort... Joachim
-- You received this message because you are subscribed to the Google Groups "glorp-group" group. To view this discussion on the web visit https://groups.google.com/d/msg/glorp-group/-/pOFRqd8KoB8J. To post to this group, send email to [hidden email]. To unsubscribe from this group, send email to [hidden email]. For more options, visit this group at http://groups.google.com/group/glorp-group?hl=en. |
Re: Handling of LIKE with constant length char fields
|
|
That seems like a very
valid concern to me. And more generally, relational operators might work
with varying lengths. I can reasonably ask if a field that is a
varchar(3) is less than 'supercalifragilistic' and get a reasonable
answer, so perhaps we shouldn't be doing truncation at all in the select
clauses. I created an AR to track the issue.
-- You received this message because you are subscribed to the Google Groups "glorp-group" group. To post to this group, send email to [hidden email]. To unsubscribe from this group, send email to [hidden email]. For more options, visit this group at http://groups.google.com/group/glorp-group?hl=en. |
|
|
HI again,
-- I'm coming back to this old thread because I've come across another problem, this time in combination with VARCHAR fields. Let's say I have two columns in my table: ACCTNO VARCHAR(6), ACCTNAME
VARCHAR(150)
Then let's construct a Glorp query: session readManyOf: Account where: [:acc| (acc accountNumber , ' ', acc accountName) similarTo: '%spendings%]. This query will not work, because for some reason GLORP uses the DatabaseField ACCTNO for type conversion, and therefor truncate the search String to 6 characters, so the resulting where-clause will look like this: ... WHERE ACCTNO || ACCTNAME LIKE '%spend' So it will not find any accounts that include the word 'spendings' because it only searches for strings ending in 'spend'. So you see, Glorp makes all kinds of wrong assumptions if you use the default stringToStringConverter, even for varchar fields. I think it is quite questionnable if truncating and padding Strings on SQL creation is a good idea. I'd rather have Glorp not touch my Strings and get the native SQL errors from the Database if I do an insert or update with a String that is too long. It is not Glorp's job to do anything about this under the covers. If the DB I use does truncate silently, fine. If it throws an SQL error, also fine. So what did I do to fix the problem? I changed DatabasePlatform>>#stringToStringConverter to this: stringToStringConverter ^self nullConverter named: #stringToString And the SQL queries are now correct. I can now search for %MyExtremelyLongString% in VARCHAR(4) fields and get no results. That's correct. Previously, it was searching for '%MyE' under the covers and may even have returned results, although the initial search criteria (the long string) was not met... So there is something that has to be done about this. For now, I just use the NullConverter fro stringToString. Until I get into different troubles, I guess ;-) Joachim Am Donnerstag, 16. Februar 2012 19:38:42 UTC+1 schrieb Alan Knight:
You received this message because you are subscribed to the Google Groups "glorp-group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To post to this group, send email to [hidden email]. Visit this group at http://groups.google.com/group/glorp-group. For more options, visit https://groups.google.com/groups/opt_out. |
«
Return to GLORP
|
1 view|%1 views
| Free forum by Nabble | Edit this page |