|Subject:||Re: Best way to Add from Paradox Table to AWS Postgres Table
|Date:||Thu, 14 May 2020 14:10:10 +1000
|From:||Tom Krieg <REMOVEtomkCAPITALS@sdassociates.com.au>
If you''re using Paradox and PostgreSQL, Postgres functions are your
best friend. For example, you can send a csv file path in a parameter to
a function, then build the import statement into a temp table and
execute it in the function. Then you can do whatever you want with the
data in the table, update or insert into other table(s) ar anything
else you can think of. It will be fast because the query is one
transaction. Maybe set your commits at the beginning of the query to
every 1000 rows.
My Appliance ServiceManager application only ever passed one single
statement to PostgreSQL.
"SELECT * FROM "function name" (param1, param2, param3 ... paramN);"
All parameters were "char" and were converted in the function. If the
function returned data, I would include a priv paradox table in the
execute sql command, if an in-memory tcursor ditto. So the BDE only ever
had to parse one command. I had a library method that parsed a string
and checked for quotes slashes etc and added escapes.
No sql injection here, folks.
Also remember that triggers and views are your equal 2nd best friends.
I could rave on about the benefits of transaction security, consistency,
referential integrity that works, scheduled execution of tasks etc etc
but you probably know all that already.
On 11/05/2020 11:02 pm, Mark Bannister wrote:
> 2.) send data as a function parameter such that Postgresql( PG) parses
> the data in a function. I actually sent it to function that creates a
> temporary table in PG and then parses the data appropriately to the
> proper table(s).