SQL Related Articles: Structuring Clarion Views for Clarion files to retrieve data from SQL tables
2003-08-25 -- Arnor Baldvinsson
Newsgroups: TopSpeed.Products.Drivers
Hi Guy,
On Mon, 25 Aug 2003 09:34:11 +0200, "Guy Cauwenbergh"
wrote:
>Could you explain more about this ?
Sure:)
>I thought that the order of the fields in the view could be different from
>the order of the fields in the table.
>Does that depend on the backend ?
Not sure, but in my experience with ASA 7.0, the Clarion view MUST
have the fields in the same order as they are in the Clarion file
structure. If not, the view is simply dead and returns nothing at
all, not even errorcodes
Here is basically what I had:
cxSumTable FILE,DRIVER('SQLAnywhere'),OWNER(cDCTOwner),|
NAME(cSumTableName),PRE(cSUMTx),THREAD
Record RECORD,PRE()
DecField DECIMAL(12,2)
LongField LONG
LongField1 LONG,NAME('LONGFIELD')
ByteField BYTE
StringField CSTRING(1025)
StringField1 CSTRING(1025),NAME('STRINGFIELD')
END
END
This is based on a table in the database called SumTable, which is
declared as:
SumTable FILE,DRIVER('SQLAnywhere'),OWNER(Glo:DCTOwner),|
NAME(Glo:SumTableName),PRE(SUMT),THREAD
Record RECORD,PRE()
DecField DECIMAL(12,2)
LongField LONG
ByteField BYTE
StringField CSTRING(1025)
END
END
This is a temp table that I use for various things and base other
tables on. It is not designed to hold data, but to be a container or
a structure for retrieving results from aggregate functions etc.
Now, please notice the order of the long and string fields in the
cxSumTable structure above, which is: Long, Long, String, String!
The original view was like this:
ColumnView View(cxSumTable)
Project(cSUMTx:LongField)
Project(cSUMTx:StringField)
Project(cSUMTx:LongField1)
Project(cSUMTx:StringField1)
End
Notice the order of the fields? Long, String, Long, String!
Nada. Nothing happened. No data, no errors, no nothing After
Andy had prodded my brain a few times (yeah, that's what it took!)
I finally saw the light and changed the view to:
ColumnView View(cxSumTable)
Project(cSUMTx:LongField)
Project(cSUMTx:LongField1)
Project(cSUMTx:StringField)
Project(cSUMTx:StringField1)
End
- Notice the order is now the same as in the file structure Long,
Long, String, String instead of Long, String, Long, String.
This of course worked like a charm! Usually it makes no difference in
what order the fields are in the table compared to the SQL backend, as
long as they have the same name and for some backends the fields need
external names. But the order of fields in the view must be in the
same order as they are in the field declaration in Clarion or you are
in trouble at least with SQL back ends, and at least with SQL Anywhere
7;)
I'm adding this to my own FAQ so I don't forget it myself!
Best regards,
Arnór Baldvinsson
Printed May 7, 2024, 3:18 am
This article has been viewed/printed 35116 times.
Google search
has resulted in 96 hits on this article since January 25, 2004.
|