VB.NET 2010 & ODBC SQL92 LEN() Function

[AlexTheDroog]

I have successfully queried Progress from VB.NET 2010 express using a system DSN (MERANT 3.60 32 BIT Progress SQL92 V9.1D) connection. I also have a linked database in Access 2007 in which I can run similar queries as well. There is on function LEN() which I can run in access to Progress with the same DSN but not in VB.NET. In access I am able to run the following

SELECT LogText from Pub_ChgLog where LEN(LogText) >50;

When I attempt run the following IN vb.net

cmd.CommandText = "SELECT " + Chr(34) + "ChgLog" + Chr(34) + "." + Chr(34) + "LogText" + Chr(34) + _
" FROM PUB." + Chr(34) + "ChgLog" + Chr(34) + _
" WHERE LEN(" + Chr(34) + "ChgLog" + Chr(34) + "." + Chr(34) + "LogText" + Chr(34) + ") > 50"

I get ERROR [42000] [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Syntax error (7587)

 

[RealHeavyDude]

Your string concatenation is not correct. Did you try to display the text in a message box. From your code I can't - I am getting a syntax error immediately.

"SELECT " + Chr(34) + "ChgLog" + Chr(34) + "." + Chr(34) + "LogText" + Chr(34) + _
" FROM PUB." + Chr(34) + "ChgLog" + Chr(34) + _
" WHERE LEN(" + Chr(34) + "ChgLog" + Chr(34) + "." + Chr(34) + "LogText" + Chr(34) + ") > 50"

The sole underline at the end of the first line is somewhat offending.

Heavy Regards, RealHeavyDude.

 

[Marian EDU]

use simply LEN(LogText) not LEN(ChgLog.LogText)... and you don't need to quote everything, unless it has non-friendly SAL characters (like '-' for instance)

 

[AlexTheDroog]

Your string concatenation is not correct. Did you try to display the text in a message box. From your code I can't - I am getting a syntax error immediately.



The sole underline at the end of the first line is somewhat offending.

Heavy Regards, RealHeavyDude.

I put the results in a text box.



The underscore (_) at the end of the lines is for VB.NET. It tells the program that the string/sql query continues on the next line. It allows the programmer to break up what would be a long continuous line of code to multiple short lines. In any case I put it all on one line removing the underscores but I get the same error....

 

[AlexTheDroog]

use simply LEN(LogText) not LEN(ChgLog.LogText)... and you don't need to quote everything, unless it has non-friendly SAL characters (like '-' for instance)

I modified the query to the following

"SELECT " + Chr(34) + "ChgLog" + Chr(34) + "." + Chr(34) + "LogText" + Chr(34) + _
" FROM PUB." + Chr(34) + "ChgLog" + Chr(34) + _
" WHERE LEN (" + Chr(34) + "LogText" + Chr(34) + ") > 50"

This is what vb.net sends

SELECT "ChgLog"."LogText" FROM PUB."ChgLog" WHERE LEN("LogText") > 50

I actually played around with the quotes and such...whole bunch of combinations but same erroe every time....Am I attempting to do something that's not supported? I would think if I can use the LEN() in Access 2007 talking to the same database using the same DNS connections i would be able to do it in VB as well. Only thing is getting the syntax right...maybe LEN needs to be in quotes or whatever.....

 

[TomBascom]

It could be a typo, or maybe I'm just not very familiar with VB (I'm not) but, in your most recent post, it looks to me like you have a space between LEN and "(". In most languages that I work with that would be a syntax error.

 

[AlexTheDroog]

It could be a typo, or maybe I'm just not very familiar with VB (I'm not) but, in your most recent post, it looks to me like you have a space between LEN and "(". In most languages that I work with that would be a syntax error.

Yeah....I removed the space but same error. That was one of my attempts to make a subtle change to see it it made a difference. I did however replace LEN with CHARACTER_LENGTH. Now I get the following error

ERROR [HY000] [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Column LogText in table PUB.ChgLog has value exceeding its max length or precision.

Looks like LEN is not recognized? But what's strange is when I run a straight up Select I can return values in the LogText field with no issue

 

[AlexTheDroog]

I got it to acknowledge the command but I have to use LENGTH or CHARACTER_LENGTH OR CHAR_LENGTH rather than LEN. However now its telling me the following

ERROR [HY000] [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Column LogText in table PUB.ChgLog has value exceeding its max length or precision.

This is what I'm running

cmd.CommandText = "SELECT " + Chr(34) + "LogText" + Chr(34) + _
" FROM PUB." + Chr(34) + "ChgLog" + Chr(34) + _
" WHERE LENGTH(" + Chr(34) + "LogText" + Chr(34) + ") > 30" + _
" AND " + Chr(34) + "ChgLog" + Chr(34) + "." + Chr(34) + "DateStamp" + Chr(34) + " BETWEEN '" + cbStartDate.Text + "' And '" + cbEndDate.Text + "'"

I put a date range in there to avoid returning too much. In any case I change the column from LogText to a shorter field called TableName and it works. The thing is I have it returning LogText. So it can return logtext but its too long to preform a LENGTH calculation on it? Anyway to do this without messing with the database?

 

[Marian EDU]

ERROR [HY000] [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Column LogText in table PUB.ChgLog has value exceeding its max length or precision.
So it can return logtext but its too long to preform a LENGTH calculation on it?

Not really, it sees that the there are records having fields with more data that the column max length... the (in)famous SQL_WIDTH issue, the SQL engine of Progress DBMS uses the column FORMAT as base for maximum column length but the FORMAT in ABL affects only how data is displayed (by default) not how the data is stored. The SQL however does enforce the maximum length, if you try to insert data in that field the exceds the maximum length it will raise an error while in ABL you can add up to the 32k limit for character fields.

Anyway to do this without messing with the database?

Again, not really... you have to 'fix' the SQL WIDTH for offending records and if you plan to use the Progress DBMS for SQL access then consider having that on your agenda right next to update statistics

You can use dbtool command line and pick option 2. <SQL Width Scan w/Fix Option> or you can do it from data administration tool.