Foreign Data Wrappers

Databases
1

I’ve never used PostgreSQL until now. But recently I learned about PostgreSQL having implemented SQL/MED, which allows to query and update other data sources through the PostgreSQL server and this by issuing regular SQL statements. You can access CSV-Files, MongoDB, Twitter, WebServices, etc.

Is this possible with the Xojo built-in PostgreSQL database class or would I have to go through the PostgreSQL C API?

2

FDW has some requirementson the server side
http://www.postgresql.org/docs/9.3/static/postgres-fdw.html
But after you set those up you should not need any thing else
The whole point of FDW is to make it mostly invisible

3

Thanks a lot. Took me only 10 minutes to get it working. It’s fantastic - one can read CSV files without problems. And it’s fast: it reads a 10 column CSV file with 50.000 records in under 1 second. Going to try MongoDB next.

4

If anyone is interested, this is the code for reading a CSV file:

[code]Dim db As New PostgreSQLDatabase()

// code to connect to db omitted

db.SQLExecute(“DROP EXTENSION IF EXISTS file_fdw;”)
db.SQLExecute(“CREATE EXTENSION file_fdw;”)

db.SQLExecute(“DROP SERVER IF EXISTS Addresses;”)
db.SQLExecute(“CREATE SERVER Addresses FOREIGN DATA WRAPPER file_fdw;”)

db.SQLExecute(“DROP FOREIGN TABLE IF EXISTS Addresses;”)
db.SQLExecute( _
"CREATE FOREIGN TABLE Addresses ( first_name text, last_name text, …, city text ) " + _
"SERVER Addresses " + _
“OPTIONS ( FILENAME ‘/Library/PostgreSQL/Addresses.csv’, FORMAT ‘csv’, HEADER ‘true’ );” _
)

Dim rst As RecordSet = db.SQLSelect(“SELECT * FROM Addresses”)[/code]
Note that the file must be in a folder accessible by the PostgreSQL server, not by the user logged in the OS. For this test I moved the file into the PostgreSQL folder (this is on OS X), which is /Library/PostgreSQL.

1 Like