NetTalkCentral POST - compound code table, how to handle for NetTalk API

[RichCPT]

How should I go about handling a compound code table when building a NetTalk API for a legacy application?  What I mean by "compound code table" is that the application has around 70 minor dropdown / lookup fields where the descriptions are loaded from a single table.  The entries are segregated by a lookup field number or "table ID" (it is a 4 character string).  In the legacy application the dropdowns were coded by using the PDDrops template.  I want to do the lookups in the API, so the client-side API user does not have to worry about performing the lookups.  When building a "read only"  NetTalk API for this legacy app, what would be the recommended way to go about performing all these lookups? 

If it is any help, I have the "In-Memory" database driver.

[Bruce]

Hi Rich,

I'm not really sure what you are asking here, so perhaps a bit of clarification is needed?

First question:

>> when building a NetTalk API for a legacy application?

Ok, building an API I get - but what do you mean "For a Legacy Application"? You mean a Clarion template app? Or something else? what would the API do exactly? (ie what is the goal of the API?)

>> I want to do the lookups in the API, so the client-side API user does not have to worry about performing the lookups.

I _think_ you're saying that you want the secondary description fields to be included in the data sent to the client? So the client doesn't have to do additional requests to translate the "Code" into "Description".
If so, that's easy enough, just add a JOIN and PROJECT to the VIEW in your API.
(ie your API returns a VIEW, use JOIN and PROJECT to add additional items to the view.)

Cheers
Bruce

[RichCPT]

I should have said this legacy clarion application uses TopSpeed files and switching the files to SQL is not an option at this point.
The goal of the API is to provide an outside development team a way to read data from our topspeed files via a web browser application that they are developing.  In the first version of this "outside" program, only reading of the data will be allowed. So, for all the tables I will need an api method that returns a list of records.  In a second version, some updating of a limited number of files will be allowed, so for that I will add get, add, put api methods for the updateable files.

My issue with the "compound code table" is that for a number of tables, it is used for lookups by multiple fields from within the same table.  For example, the Person table contains seven "code" fields that all have lookups into this common "compound code table".  The first couple of these fields are:
-Region
-County
-Ethnicity
-Education

I do not see how I can create a VIEW structure in clarion when I have to "JOIN" to the same table multiple times, unless I were to create Alias tables in the dictionary. This would add about 65 alias tables to the dictionary.  Instead of adding ALIAS files, is there a way to add a bunch of MEMORY tables so these lookups will be really fast?  I have the clarion in-memory database driver.  If so, do I have to populate these tables by hand or will the clarion templates that come with the MEMORY table driver work in the NetTalk server app?

P.S. I just noticed one of the return field types in the API is "Queue".  Should I look at that as a way of combining data from the source table with all the lookup fields that go with that source table?

[urayoan]

Hello RichCPT:
I don't know if i did understand you correctly, so if a make assumptions in my response, please clarify to further help you out with this. In my case, i have a table that like you i need to make search of different parameters for a API server. In my case (and for what i understand on your post), you need to get the data from one specific table.

First, y declare my view in local data like this (i did simplify the file structure for the example). The idea here is to apply a filter or multiple filters as needed.
Keep in mind, is not a good idea to send all the data without any filter because, if the file has much records is gonna take long time or connection timeout. Or even worse, slowdown your system

This should work with any database. Maybe there are ways to make this much simpler, but this is what works for me using Btrieve database (Actian - Formely known as Pervasive)

Please check the included example and hope it helps.

!Defined view
View:PersonView      ViewManager
PersonView              VIEW(Persons)
                            PROJECT(PER:id)
                            PROJECT(PER:PersonName)
                            PROJECT(PER:eMail)
                            PROJECT(PER:Telephone)
                            PROJECT(PER:Region)
                            PROJECT(PER:County)
                            PROJECT(PER:Ethnicity)
                            PROJECT(PER:Education)
                          END

!Then in the Service Method Embed point do the following

DO OpenFiles
CLEAR(PER:Record) !Reset the file for the sake of cleaness
FREE(Q:ClientsSearchResult) !this queue is the return Queue in the method
CLEAR(LocSortOrder) !CString variable

GlobalErrors.Init                                     !initialize GlobalErrors object

View:CustomersView.Init(CustomersView,Relate:Persons)  !initialize View:Customer object
LocSortOrder = View:CustomersView.AddSortOrder(PER:ByID)         !add sort ByKey or anything else
View:CustomersView.SetSort(LocSortOrder)

IF (LEN(Region) > 0) !API Parameter asuming is string
  View:CustomersView.SetFilter('LOWER(PER:Region) = <39>' & LOWER(Region) & '<39>','1')
END!IF

IF (LEN(County) > 0) !API Parameter asuming is string
  View:CustomersView.SetFilter('LOWER(PER:County) = <39>' & LOWER(County) & '<39>','2')
END!IF

IF (LEN(Ethnicity) > 0) !API Parameter asuming is string
  View:CustomersView.SetFilter('LOWER(PER:Ethnicity) = <39>' & LOWER(Ethnicity) & '<39>','3')
END!IF

IF (LEN(Education) > 0) !API Parameter asuming is string
  View:CustomersView.SetFilter('LOWER(PER:Education) = <39>' & LOWER(Education) & '<39>','4')
END!IF

!set the defined filters above
View:CustomersView.ApplyFilter()

View:CustomersView.Open()
View:CustomersView.Reset(1)

LOOP UNTIL View:CustomersView.Next()
  Q:ClientsSearchResult.AccountNumber       = PER:id
  !and so on with the other colums in the View
  ADD(Q:ClientsSearchResult)
END!LOOP

View:CustomersView.Close()
View:CustomersView.Kill()
DO CloseFiles

[urayoan]

I forgot to mention, the example was made using the latest and greatest NetTalk version 11.23 and Clarion 11

[Bruce]

Hi Rich,

>> P.S. I just noticed one of the return field types in the API is "Queue".  Should I look at that as a way of combining data from the source table with all the lookup fields that go with that source table?

Yes - it sounds like populating a queue is probably the way you want to go for now...

cheers
Bruce

[RichCPT]

urayoan,
Thanks for the example on using a QUEUE as the return value.  That gets me going in the right direction.   Although, I'm not sure about having to open/close the data files on each and every API call, but that is a topic for another thread. 

Putting in all the queue structures could be tedious.  But if I don't mind foregoing the self-documenting aspect of the API then it looks like I can hand-code the QUEUE like you had hand-coded the VIEW.  I can copy the record structures from the Clarion generated file structures into the various QUEUE definitions and keep a copy of them in a document that I deliver to the API consumers (people building apps to access my data).  This actually gives them better documentation because the Clarion generated record structures include the "Description" for each field from the .DCT.  The self-documenting NetTalk API doesn't include these descriptions. 

[Bruce]

Hi Richard,

>> Although, I'm not sure about having to open/close the data files on each and every API call, but that is a topic for another thread.

I'm not sure what you think "opening" a table "does" - but this overhead is insignificant.

>> But if I don't mind foregoing the self-documenting aspect of the API

This is the very essence of technical debt. You decide to do a half-baked job now, instead of delivering a quality product. The auto-generated documentation, and auto-generated Try-It features are integral to good API services. Taking a few minutes to add your Queue to the data pad seems like a worthwhile option to me.

>> The self-documenting NetTalk API doesn't include these descriptions. 

There's nothing wrong with supplementing the descriptions.

Cheers
Bruce