Temporary tables

Morning everyone,

I have a function in my system that imports EDI sales orders in xml format. As a first step I validate a file, then I run through the file again, creating sales orders (on two tables - a header and a details table). It's not a one-to-one process, i.e., I may have to create more than one order on my system per order received.

I used the idea of creating what I want to be temporary tables in my HFCS database. I create and then modify new orders on these temporary tables, and then at the end of the function copy them to my system's normal order tables.

At the start of each cycle I have code that checks for the existence of these "temporary" tables; if they're found I delete them. Here's the code for one of the tables.

IF HFileExist(gctmyconnection,"dsTradaBaseSalesOrder2.fic") THEN
IF NOT HDeleteFile(gctmyconnection,".\dsTradaBaseSalesOrder2.fic") THEN ExceptionThrow(1,"Unable to delete temporary SalesOrder file - contact Support")
END

dsTradaBaseSalesOrder2 is Data Source <description=SALESORDER>

IF NOT HAlias(SALESORDER,dsTradaBaseSalesOrder2) THEN ExceptionThrow(1,"Unable to create SalesOrder Alias - contact Support")

HCreationIfNotFound(dsTradaBaseSalesOrder2)

HChangeConnection(dsTradaBaseSalesOrder2,gctmyconnection)

This has all worked fine for a few months, but last night I put in an upgrade that included the addition of new column on the table shown above - SALESORDER. The function now fails, The error occurs on the HCreationIfNotFound statement shown above, and the message states that the definition of the "temporary" table in the analysis does not match that of the original table. I don't understand why this is happening given that the temporary table is created in each run using the HAlias statement, and is then cancelled at the end of each run using the HCancelAlias statement.

I've got around it for now by giving the "temporary" table a different name - hence the 2 suffix you see in the names - but I want to come up with a proper solution.

The key problem seems to be that in creating tables using HAlias, they are permanently created, i.e., they're not actually temporary?

Anyway, can anyone point me in the direction of a solution for this?

Cheers
Reg

Hello Reg

Instead of creating files, it seems like you could use memory tables or views to hold the data. they would also be quicker.

Regards
Al

Hi Al,

A memory table would be a pain as I need to have access to every column in the tables - lots of additional coding?

Views are a thought, but they seem to be permanent in nature as well when I read about them. I was hoping there was a truly temporary method of creating a copy of an HFCS table that would be completely disposed once it's no longer required...

Hello Reg

I use memory tables all the time so I supposed I am just used to them and the coding required.

If I need a temporary view I just create a blank one with no records
A view can be treated exactly the same as a file

Quote

OrdlinesView is Data Source
HCreateView(OrdlinesView, OrdLines, "*", "OTCLEUNIK", "OTCLEUNIK=0", hViewDefault)
//This creates a blank view of the ordline file because the OTCleunik field is the primary key and will never be blank
HDeleteView(OrdlinesView)

Regards
Al

Thanks Al, very helpful

Hi, I just wanted to add that assoative arrays [help.windev.com] are here which can be used just like a Memory Zone which is still there but limited compared to an associative array.

Kind regards,
Guenter Predl
office@windev.at

Hi Al,

The temporary view construct you kindly gave me works perfectly, thanks very much!

norfolk-reg Wrote:
-------------------------------------------------------
> Hi Al,
>
> The temporary view construct you kindly gave me
> works perfectly, thanks very much!

Pleased you have found what is for you a workable solution.

I feel obliged to point out however that this is not what views were intended for and were depricated in V20 (only kept for 'backward compatability') and replaced by 'materialized views'.
Not a critisism of you or indeed @Al but possibly important to new less experienced users to be aware of.
IMHO and experience the most efficient and versatile method of importing bulk data, typically from web services, is to use structures and arrays as all operations, until writing to disc is required, are carried out in memory.

Thanks for that Derek,

First of all, I understand and totally accept what you're saying.

I personally don't find structures and arrays very efficient when multiple records are involved, although I do use structures for cloning records. You can't use HReadSeekFirst etc functions on them for example. I know there are alternatives, but they seem clumsy to me, especially when composite key values are needed.

I've just looked at Materialized Views, and their intended purpose really seems to be to provide more efficient access to regularly required views of data, e.g., for statistics, whereas my requirement was for a totally temporary image of a table layout to be used solely to handle a small amount of incoming data in a single process run by a single user.

The HAlias feature might be what PCSoft intended to be the way to handle temporary database tables, but they're not temporary; the physical file seems to remain even after an HCancelAlias command. Not only that, they're common to all users so you can get clashes if two people run a routine at the same time.

Cheers
Reg