Log in or sign up to continue

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.

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)

@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!! 

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.

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!!!

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.

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)

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!!

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

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

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,
 

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

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.

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.