×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Topspeed ODBC and SQL query
3

Topspeed ODBC and SQL query

Topspeed ODBC and SQL query

(OP)
Hi,
I'm new to Topspeed ODBC and am wirting the query: "select date, register   from cshthist where date = 6/19/01" using Access 97.  I get the error "invalid operand".  I have tried different variations trying to get the date but no go.  Date is set up in the ODBC manager to convert it to Access style date.  I can select specific other fields but always fails on Date.  I've tried "date = "#6/19/01#". "date = convert(datetime,'06/19/2001') and others.  Any ideas?  Thanks much.
Pilotone

RE: Topspeed ODBC and SQL query

The Topspeed ODBC has a problem with dates especially if you have set up the data source to identify date fields. It is better to query using the integer value of the dates and have a function to convert a Clarion Date to an Access date.
Clarion stores the date as the no of days since Dec 28, 1800 e.g. today's date Jun 26, 2003 is 73959.

P.S.: Although it is not going to work, the proper syntax for the query is to enclose the date in single quotes in the yyyy-mm-dd format e.g. '2003-06-26'

RE: Topspeed ODBC and SQL query

(OP)
Thanks JShankar,
I had a feeling this was the case.
Pilotone

RE: Topspeed ODBC and SQL query

2
Hi,

This should do the trick...
Added a bit more just to show whats possible.

Select  i.invdate, {fn dayofyear(i.Invdate)} AS B, {fn CurDate()} as Date  , OrdH.OrdNo
FROM Invh  i,{OJ OrdH LEFT OUTER JOIN Invh ON Invh.OrdNo = OrdH.OrdNo}
Where  i.Invdate = {d'2003-06-05'}

RE: Topspeed ODBC and SQL query

Thanks a lot, lvdm. You have given me the solution to a problem which has limited me from using Topspeed ODBC effectively for a long time. You do deserve a Star.

I did experiment and found out the {fn dayofyear(date)} returns the no of days from the beginning of the year, {fn dayofmonth(date)} returns the Day part of the date, {fn month(date)} returns the Month part of the date, {fn year(date)} returns the Year part of the date and can be used effectively in queries like select no,date,{fn dayofyear(date)}as dy,{fn dayofmonth(date)}as d,{fn month(date)} as m,{fn year(date)} as y from tranhdr where {fn month(date)} = 2 and {fn year(date)} = 2002.

Are these functions standard ODBC functions? Is there any way to find out the full list of supported functions by the Topspeed ODBC driver?

RE: Topspeed ODBC and SQL query

No problem.

I saw that this is a general problem with Topspeed/Clarion, they never provide any help or example when it comes to things like this. You pay a lot of money for the TPS ODBC drivers, but with NO HELP... go figure.

I used Clarion for the last 5 years successfully, but with LOTS OF LIMITATIONS like this.. so I have vowed to never use it again.
There are a lot better development tools and platforms available.

Here is another site with a bit more info ODBC.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappepr.asp

RE: Topspeed ODBC and SQL query

(OP)
Thanks Guys,
I'm passing this info to my boss in hopes that he doesn't sink a lot of money into topspeed.  There is a potential of over 500 sites that he wants to buy driver licenses for.
One more:
I offered to learn and write my own ODBC drivers for our use on our Clarion databases.  Any ideas on the reality of such an undertaking?
Later,
Pilotone

RE: Topspeed ODBC and SQL query

I have looked at the Scalar Functions for Access.

Althought it claims the return types should always be SQL_FLOAT it does not seem the case.

I For example I do something like this:

Select 22/3
from mytable

The result is just 3
There the result should be something like 3.14285714

I am just getting the interger value back, not the entire value.

This becomes and issue when I am trying to manipulate some dates.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close