1
1350
Question Augusto Estefan · Jul 24, 2020

Export data to SQL format

#Caché #Databases #Data Import and Export #Red Hat Enterprise Linux (RHEL)
Go to the original post@Augusto Estefan

Hi! 

I need to export some data from a table on CACHE  and then import this data to a POSTGRESQL table.

Is this possible? I know I can export this data as a CSV format, but I prefer if there is a way to export that data as SQL format.

Thanks!!!

Go to the original post written by @Augusto Estefan
Discussion (16)1
Log in or sign up to continue

Comments

Augusto Estefan  Jul 28, 2020 to Robert Cemper

Thanks Robert, but I cant get from PostgreSQL server to the Cache Server thats why I need to export the data, but this information help me with another problem.

Thanks a lot.

0
Robert Cemper  Jul 28, 2020 to Augusto Estefan

The so-called SQLformat doesn't exist in Caché (at least not in the last 20 years)
But over the gateway, you can move data from Caché to PostgeSQL.  "from a table on CACHE  to a POSTGRESQL table"

So you need a trigger to do it:
- Caché has to be the active part here
you can start the upload over a webservice, an REST call, even as an SQLprocedure, or automatically by the scheduler
And if you don't want to touch your target table directly you may have a shadow-copy in PostgreSQL that
then serves as a source for your final updates.  ( some INSERT...SELECT....)

So instead of import from CSV, you do an import from a local table in PostgreSQL
Anything is better than CSV.
This would even allow filling your shadow over the day in small steps instead of a big bang (if needed)

0
Augusto Estefan  Mar 29, 2021 to Robert Cemper

@Robert Cemper Hello! Now I got the SQLGateway aimed to the PostgreSQL.

The issue I have now is that I need to migrate/copy the HSPI_Data.Patient table to PostgreSQ. What i have to do to see this table on PostgreSQL?

Thanks to much!! 

0
Robert Cemper  Mar 29, 2021 to Augusto Estefan

a few simple steps.

  • if not existing yet create a table on Postgres with the identic columns that you see in Cache or use an existing target
  • next use SMP > SQL > Wizzard > Link Table
  •  
  • It creates a class /table that reflects all columns /properties of the linked table. + storage method that can read, write, delete, insert over ODBC / JDBC on the postgreSQL table.
0
Augusto Estefan  Mar 30, 2021 to Robert Cemper

Thanks for the answer, I did what you told me but this create me a new class/table on Cache. I need to link an existant Cache class/table to a postgres table. 

Is this possible? 

Thanks Robert!!!

0
Robert Cemper  Mar 30, 2021 to Augusto Estefan

you are mixing up 2 things:

  • one is the class that stores data in Caché
  • the Class generated as LINKED TABLE that stores its data in postgreSQL.'

so the LINKED describes the structure that is used by generated SQL statements
to work on postgreSQL. Take a look at the storage definition of the class
and see the difference.
But your code in Cache acts as if data were local !!
Either by Objects or by Tables. 
if you issue a %Save() on the class it runs a INSERT OR UPDATE undercover.

0
Augusto Estefan  Mar 30, 2021 to Augusto Estefan

Now I see the table, but now I need to copy/migrate the data from HSPI_Data.patient (in cache) to PostgreSQL. 

How can I do that?

This is my recently created linked table. (i got to move the data there)

0
Augusto Estefan  Mar 31, 2021 to Robert Cemper

Now when I execute this insert give me this error: 

I read the doc you gave about inserts but I'm not getting a SQL Error that are described there, any idea of what the problem can be?

Again, thanks a lot for the help Robert!!

0
Robert Cemper  Mar 31, 2021 to Augusto Estefan

Seems just a typo in INSERT statement.
Target Columns must be in parenthesis.

INSERT INTO HSIPIData.datos_usarios2 (addresses)
       SELECT Addresses FROM HSPI_Data.patient

0
Augusto Estefan  Mar 31, 2021 to Robert Cemper

Yes, I was thinking the same but happens too when I add the parenthesis.

0
Robert Cemper  Mar 31, 2021 to Augusto Estefan

as you are in SMP anyhow just try to do an insert manually.
Just 1 row.
you seem to require something else <UNDEFINED>
in addition, if your record exists already You may need an 
INSERT OR UPDATE   (if this is possible in postgreSQL)
It could be OK, but I wonder that you insert just 1 value
Eventually, there is something required that is missing?
Check the definition of the generated Caché class,
 

0
Evgeny Shvarov · Jul 24, 2020

Augusto, what do you mean by “sql format”?

0
Augusto Estefan  Jul 28, 2020 to Evgeny Shvarov

Hi Evgeny, I dont know if this exist on cache, but on PostgreSQL you can export the data as SQL format (on a dump file). 

And I was thinking there is maybe a way to export data from cache as this format (SQL format).

This exists on Cache? 

Thanks and sorry for the delay to answer.

0
Evgeny Shvarov  Jul 28, 2020 to Augusto Estefan

And this "SQL format" could be imported then into PostgreSQL only or to any SQL-driven DBMS?

There is no such feature in IRIS, but if you share an example I think it could be baked shortly.

IRIS can export data into Globals format in Global output file (GOF), or XML. which could be imported then into any IRIS.

And you can export CSV file from IRIS class/table.

0