clarion / tps database via ODBC Query Strings into .Net

Can someone point me to some manual/examples of querys against a clarion / tps database via ODBC.

I'm trying to do a like querey eg.
"SELECT Field1, Field2 FROM ATable WHERE Field1 like 'abc%' "
and keep getting a [ ERROR [S0000] [TopSpeed Corp.][TopSpeed ODBC Driver]Invalid operand for operator: LIKE ]
& a Top Records eg.
"SELECT Top 100 * FROM ATable"
and keep getting a [ERROR [42000] [TopSpeed Corp.][TopSpeed ODBC Driver]Expected lexical element not found: FROM]
LVL 11
coopzzAsked:
Who is Participating?
 
tonydemarcoCommented:
replace;    "SELECT Field1, Field2 FROM ATable WHERE Field1 like 'abc%' "


with:

SELECT Field1
FROM ATable
WHERE (((Field1) Like "abc"));
0
 
tonydemarcoCommented:

sorry, last line S/B

WHERE (((Field1) Like "abc*"));
0
 
coopzzAuthor Commented:
that didn't actually work.. I did figure it out that the % wild card worked on a text column but not a numeric column??
eg. "SELECT Field1, Field2 FROM ATable WHERE Field1 like 'abc%' " <== this worked on a text column but not a Numeric??

Also do you know how to do that TOP statement as well or where a reference info may be??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
coopzzAuthor Commented:
Well I think I found part of me answers, Oh well if anyones trying the find the answers to this or need some help with like querys  on any type of non string columns
 "SELECT Field1, Field2 FROM ATable WHERE {fn convert(Field1,SQL_CHAR)} LIKE 'abc%'"

or even a not case sensitivity where clause
"SELECT Field1, Field2 FROM ATable WHERE {fn lcase(Field1)} like 'abc%'"

of course there is performance hit's with these methods also.

Thanks anyway
0
 
Jon-GCommented:
Where did you get an ODBC driver for Clarion ?
I am trying to get data from an old clarion system from the dark ages but I don't have the actual clarion application ...
Any idea ?
0
 
coopzzAuthor Commented:
From memory it's not free heres what I believed we used. cheap enough though if it's data you really need.
http://www.softvelocity.com/
download
http://www.softvelocity.com/Drivers/TSODBC.htm
0
 
coopzzAuthor Commented:
depending on how big the db and amount of data tables etc.. there is I could probably do it for ya.into a access/excelll
0
 
Jon-GCommented:
There are about 60 tables so that may be a bit of a job to do as a favour for someone you don't know
 :)

I found a cheap application called clarion viewer (http://www.scalabium.com/)
which costs 20 euros and seems like it will do the trick.
Thanks so much for the offer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.