Search code examples
c#odbcclarion

Expected lexical element not found on Softvelocity ODBC driver

I have C# program with SQL that looks like this:

INSERT INTO PDRIVERP
(POLICY
,DRIVERNO
,POINTRECNO
,DRIVERPOINTS
,POINTDESCRIPTION
,DATEOFVIOLATION
,ADDOPERATOR
,DATEADDED
,TIMEADDED
) VALUES (
,0             
,1            
,37       
,2            
,'my desc'  
,'75000'
,'34'
,{d '2020-07-29'}
,0
)

I have a table definition that looks like this:

PDRIVERPOINTS       FILE,DRIVER('TOPSPEED'),NAME(SourceName),RECLAIM,PRE(IN)
POLDRIVERKEY          KEY(+IN:POLICY,+IN:DRIVERNO,+IN:POINTRECNO),OPT,NOCASE
POINTDESCRIPTIONKEY   KEY(+IN:POINTDESCRIPTION),DUP,OPT,NOCASE
DRIVERPOINTSKEY       KEY(+IN:DRIVERPOINTS),DUP,OPT,NOCASE
ADDEDBYKEY            KEY(+IN:ADDOPERATOR,+IN:DATEADDED,+IN:TIMEADDED),DUP,OPT,NOCASE
DATEADDEDKEY          KEY(+IN:DATEADDED,+IN:TIMEADDED,+IN:ADDOPERATOR),DUP,OPT,NOCASE
UPDATEDBYKEY          KEY(+IN:UPDATEOPERATOR,+IN:DATEUPDATED,+IN:TIMEUPDATED),DUP,OPT,NOCASE
DATEUPDATEDKEY        KEY(+IN:DATEUPDATED,+IN:TIMEUPDATED,+IN:UPDATEOPERATOR),DUP,OPT,NOCASE
RECKEY                KEY(+IN:RECNO),OPT,NOCASE
REMARKS               MEMO(1000)
RECORD                RECORD
POLICY                  DECIMAL(13,0)
DRIVERNO                DECIMAL(3,0)
POINTRECNO              DECIMAL(3,0)
DRIVERPOINTS            DECIMAL(3,0)
POINTDESCRIPTION        STRING(80)
DATEOFVIOLATION         STRING(8)
ADDOPERATOR             STRING(15)
DATEADDED               DATE
TIMEADDED               TIME
UPDATEOPERATOR          STRING(15)
DATEUPDATED             DATE
TIMEUPDATED             TIME
RECNO                   DECIMAL(13,0)

I also see a definition that looks like this:

CLEAR(cDRP:Record)
cDRP:REMARKS = IN:REMARKS
cDRP:POLICY = IN:POLICY
cDRP:DRIVERNO = IN:DRIVERNO
cDRP:POINTRECNO = IN:POINTRECNO
cDRP:DRIVERPOINTS = IN:DRIVERPOINTS
cDRP:POINTDESCRIPTION = IN:POINTDESCRIPTION
cDRP:DATEOFVIOLATION = FORMAT(IN:DATEOFVIOLATION,@D2)
cDRP:ADDOPERATOR = IN:ADDOPERATOR
cDRP:DATEADDED = FORMAT(IN:DATEADDED,@D2)
cDRP:TIMEADDED = FORMAT(IN:TIMEADDED,@T1)
cDRP:UPDATEOPERATOR = IN:UPDATEOPERATOR
cDRP:DATEUPDATED = FORMAT(IN:DATEUPDATED,@D2)
cDRP:TIMEUPDATED = FORMAT(IN:TIMEUPDATED,@T1)
cDRP:RECNO = IN:RECNO

I get this error:

ERROR [42000] [SoftVelocity Inc.][TopSpeed ODBC Driver]Expected lexical element not found: <identifier>

Some sample data read looks like this:

Sample TPS data

Note the inconsistency between DATEOFVIOLATION and DATEADDED in the table. The display is different so I was trying to insert as a string, but it just hit me that I should try to insert as a date.

What is wrong with my sql?

What explains that DATEOFVIOLATION and DATEADDED display different in Softvelocity Database Scanner?

How can I get better error messages out of ODBC. Perhaps something that tells me which field is broken or a number of characters into the string that caused the syntax error?

Solution

  • Clarion dates are stored as a long (INT). 75000 is a valid clarion date. Im not sure why that column is marked as a string, but using '75000' should work. The date is showing as 75000 in topscan in dateofviolation because its defined as a string, or because the picture is unformatted (you can change that on the topscan menu).

    I suspect the insert error is coming from the DateAdded column. I've not used the TOPSPEED ODBC driver in a decade or more (though I suspect it hasnt changed). I dont recall if the driver supported ,{d '2020-07-29'} as a value. I guess if you have the docs, that would tell you.

    I would first try leaving the dateadded column out and see if the insert works. Use that same technique to figure out whats causing the insert to fail and then you can get home from there.

    Also, re: timeadded, 0 means "no time" in Clarion. 1 = midnight. Not withstanding that, an interval of 100 in that field means 1 second, easy to do the math after that.

    BTW, the best set of overall Clarion resources is at https://clarionhub.com, in Skype, and in the Clarion NNTP newsgroups (news.softvelocity.com - you'll need credentials from SoftVelocity).