I'm using postgres database. I want to create dblink with mysql database. What should I do?

Question

sudo PATH=/usr/lib/postgresql/9.1/bin/:$PATH make USE_PGXS=1 clean


sudo ln -s /usr/lib/jvm/jdk1.7.0/jre/lib/i386/server/libjvm.so /usr/lib/libjvm.so


CREATE EXTENSION jdbc_fdw;

– MySQL JDBC

sudo -u postgres psql postgres;

CREATE SERVER jdbc_mysql FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(
    drivername 'com.mysql.jdbc.Driver',
    url 'jdbc:mysql://192.168.1.2:3306/bookshoputem',
    querytimeout '15',
    jarfile '/home/user2010/Documents/DBLINK/lib/mysql-connector-java-5.1.22-bin.jar',
    maxheapsize '600'
    );


CREATE USER MAPPING FOR postgres SERVER jdbc_mysql OPTIONS(username 'root',password 'abc123');

CREATE FOREIGN TABLE Inventory_mysql
(int_inventoryid VARCHAR(10) ,
int_stockin int ,
int_stockout int ,
int_balancequantity int ,
int_reorderlevel int ,
bk_isbn VARCHAR(20)) 
SERVER jdbc_mysql
OPTIONS (table 'Inventory');
----------------------

While I run this command, I just get data from mysql, but it doesn't delete or update from mysql database. Please help me.

Maybe your MySQL credentials only allow read-only data access? — mvp
– mvp, Commented Dec 20, 2012 at 5:49

Community · Accepted Answer · 2017-05-23 12:06:56Z

2

Documentation says:

Currently, foreign tables are read-only. 
This limitation may be fixed in a future release.

Try to use dblink. Some helpful information is here.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Dec 20, 2012 at 10:49

sufleR

2,99320 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I'm using postgres database. I want to create dblink with mysql database. What should I do?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related