ODBC Function Name Is Missing error

[booler]

Hi!

I am having a problem getting the length of data fields using ODBC. I am simply trying to count the number of occurrences of a specified number of characters within a column. So far I have been using:

SELECT COUNT(*) FROM mydb WHERE len({ fn CONVERT(TESTNUM, SQL_VARCHAR} )) = 5

So I am trying to cast the TESTNUM values to varchar tye, and then count the number of 5 digit numbers that occur.

When I run this, ODBC returns the error: 'function name is missing'. Does anybody know what this means? Am I using the right syntax? Or does anyone know of a better way I can get this to work?

Thanks for your help!

Adam

 

[lupien1]

You could try this:

select count(*) from mydb where (myfield/10000) >= 1

hope this help

 

[mirtheil]

What Database are you using? Perhaps the "CONVERT" function isn't supported by your DBMS.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[booler]

mirthell- I think you're right. I am using Visual Foxpro 6.0, and it seems that there is very little functionality that its ODBC driver does support beyond simple SELECT, DELETE and UPDATE queries.

My problem arose because I needed to get the length of every single field in a foxpro dbf file, regardless of datatype, and count the number of occurrences of each length. This is why I could not do what lupien suggested (because I need to provide for every possible length, and not just 5 characters).

In the end, I persevered with ODBC for a bit- I got back every row as an object array, and converted the objects to strings, then measured their lengths, but this proved way too slow.

Finally, I abandoned ODBC and created a custom DBF reader that opens the DBF file as binary, reads the field information from the DBF header, and then reads through the file measuring the length of each field.

Thanks for your help!

Adam