http://www.postgresonline.com/journal/ Postgres OnLine Journal - an In depth look at the PostgreSQL open source database en Serendipity 1.4.1 - http://www.s9y.org/ Thu, 17 May 2012 13:27:22 GMT http://www.postgresonline.com/journal/templates/default/img/s9y_banner_small.png http://www.postgresonline.com/journal/ 100 21 http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#c4433 http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=249 nospam@example.com (Luc) Thank you for your answer, but this did not solve the problem. Notice : in the "Messages" tab, PG wrote the fllowing after executing te select on my foreign table : ------------------------- NOTICE: Opps! Durée totale d'exécution de la requête :32 ms. 0 ligne récupérée. ------------------------- Wed, 16 May 2012 01:50:05 -0400 http://www.postgresonline.com/journal/archives/249-guid.html#c4433 http://www.postgresonline.com/journal/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html#c4430 http://www.postgresonline.com/journal/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=51 nospam@example.com (Joseph) I prefer paying less for a better thing and not being locked into a monopoly platform. Sun, 13 May 2012 17:38:02 -0400 http://www.postgresonline.com/journal/archives/51-guid.html#c4430 http://www.postgresonline.com/journal/archives/42-REST-in-PostgreSQL-Part-2-B-The-REST-Server-service-with-PHP-5.html#c4424 http://www.postgresonline.com/journal/archives/42-REST-in-PostgreSQL-Part-2-B-The-REST-Server-service-with-PHP-5.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=42 nospam@example.com (Regina) Hmm we still use adodb for abstraction. Anyrate adodb is just a thin wrapper around the PHP postgresql drivers. Which PHP version are you using? As far as PHP database abstraction layers go, there is also Pear DB which is probably more common than ADODB. There are a couple others I can't recall, but again they are all just wrappers around the pdo_pgsql and pgsql PHP drivers so any crashing you are experiencing may be the core PHP driver. Wed, 09 May 2012 02:02:10 -0400 http://www.postgresonline.com/journal/archives/42-guid.html#c4424 http://www.postgresonline.com/journal/archives/42-REST-in-PostgreSQL-Part-2-B-The-REST-Server-service-with-PHP-5.html#c4421 http://www.postgresonline.com/journal/archives/42-REST-in-PostgreSQL-Part-2-B-The-REST-Server-service-with-PHP-5.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=42 nospam@example.com (Sara) Hi! I am working through this tutorial and have been able to implement up until the adodb abstraction library crashes. The drivers in the adodb folder support Postgres through 8.0, but not 9.0. A quick web search doesn't seem to get me any results for updated adodb drivers. Do you have any suggestions for another abstraction library? Thanks in advance, Sara Sun, 06 May 2012 21:08:40 -0400 http://www.postgresonline.com/journal/archives/42-guid.html#c4421 http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html#c4415 http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=250 nospam@example.com (Regina) Peter, Ah yes. That's a great idea. I should have thought about that one. Fri, 04 May 2012 15:55:01 -0400 http://www.postgresonline.com/journal/archives/250-guid.html#c4415 http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html#c4414 http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=250 nospam@example.com (Peter) One way to workaround the date style problem could be to declare the last_change_date as varchar as well and then build a view on top of the FDW table converting the varchar to a proper date using to_date() and the correct date format. Fri, 04 May 2012 11:03:24 -0400 http://www.postgresonline.com/journal/archives/250-guid.html#c4414 http://www.postgresonline.com/journal/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html#c4410 http://www.postgresonline.com/journal/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=38 nospam@example.com (Brasdours) A really good article, its exactly what i need but i cant connect with putty. I've installed freesshd and postgresql on machine server which run on windows server 2003. postgresql.conf is configure like this: listen_addresses = '*' and pg_hba like this: host all all 127.0.0.1/32 md5 host all all 0.0.0.0/0 md5 I want to access to postgresql from home. So, i installed putty and set like you described above with the hostname 'brasdours.no-ip.org' of my server. I configure the firewall on both machines to let the port 22 and 5432 open. And when i try to connect whith putty, it shows a message: connection refused I'll appriciate any help Thu, 03 May 2012 11:21:17 -0400 http://www.postgresonline.com/journal/archives/38-guid.html#c4410 http://www.postgresonline.com/journal/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html#c4409 http://www.postgresonline.com/journal/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=38 nospam@example.com (Regina) Vidas, Which computer are you trying to connect form? local is socket port not tcp/ip port. You should have one for host, localhost and I suspect you do, but its set to ident (which is standard for ubuntu as I recall). So its trying to authenticate admin using the ident server on your unix. Thu, 03 May 2012 07:58:19 -0400 http://www.postgresonline.com/journal/archives/38-guid.html#c4409 http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#c4407 http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=249 nospam@example.com (Regina) Luc, the ODBC FDW doesn't like different casings. I had similar issues if I had my select with mixed casing column names. Doesn't matter how it is in sql server, but when you write the select fields should be all lowercase. So try changing your foreign table to this: CREATE FOREIGN TABLE super_store_SQLServer.TestSQLServer (iid Integer NOT NULL , cnom Varchar(20) NOT NULL) SERVER TestSQLServer OPTIONS (database 'superstore_SQLServer' , schema 'dbo' , sql_query 'iid, cnom FROM dbo.testPG;' , sql_count 'select count(iId) from dbo.testPG;'); Tue, 01 May 2012 16:49:39 -0400 http://www.postgresonline.com/journal/archives/249-guid.html#c4407 http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#c4400 http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window-Part-2.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=249 nospam@example.com (Luc) Thank you for this work and the good explanations. However, I tried to apply the recommended steps, but the only result is an empty one (columns are displayed with no data lines, as if the foreign table contained no record). I’ve built a test table on SQLServer 2005, named “testPG”, containing 2 columns (iId Integer NOT NULL, cName Varchar(20) NOT NULL), and inserted 2 rows (1, “One”) and (2, “Two”). I created a system dsn named SystemSQLServerPourPG which I tested successfully using MSVisualFoxPro and MSAccess. Then I created the PostGreSQL script as follows : CREATE EXTENSION odbc_fdw ; CREATE SCHEMA super_store_SQLServer ; CREATE SERVER TestSQLServer FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'SystemSQLServerPourPG'); CREATE USER MAPPING FOR postgres SERVER TestSQLServer OPTIONS (username 'sa',password 'mdp'); CREATE FOREIGN TABLE super_store_SQLServer.TestSQLServer (iId Integer NOT NULL , cNom Varchar(20) NOT NULL) SERVER TestSQLServer OPTIONS (database 'superstore_SQLServer' , schema 'dbo' , sql_query 'iId, cNom FROM dbo.testPG;' , sql_count 'select count(iId) from dbo.testPG;'); SELECT * FROM super_store_SQLServer.TestSQLServer ; The result is an empty result as said earlier. Did I write something wrong ? NB : more tests showed that : * the same result is obtained when using a dsn which does not exist (without any error message) * the same result is obtained when using an invalid login or password (without any error message) * the same result is obtained when using an invalid SQL Query (either syntaxically correct or not) (without any error message) * using wrong parameters names in the CREATE FOREIGN TABLE options causes a crash of the server (without any error message, but you have to restart service), when using an invalid option (e.g “dsnerror” instead of “dsn”) in the CREATE SERVER query brings the following message : “option « dsnerror » invalide”. Hope you can help. Luc Fri, 27 Apr 2012 13:55:08 -0400 http://www.postgresonline.com/journal/archives/249-guid.html#c4400 http://www.postgresonline.com/journal/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html#c4399 http://www.postgresonline.com/journal/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=38 nospam@example.com (Vidas) It so simple, but i can't. I get error in pg_log: could not connect to Ident server at address "127.0.0.1", port 113 But I can connect from notebook with putty and command: vidas@vartai:~>psql -U admin db with no problems. My test pg_hba.conf: local db admin trust ... putties login name 'admin' too. username in pgAdmin 'admin' too. Fri, 27 Apr 2012 06:49:51 -0400 http://www.postgresonline.com/journal/archives/38-guid.html#c4399 http://www.postgresonline.com/journal/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html#c4393 http://www.postgresonline.com/journal/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=72 nospam@example.com (iyemwen) thanks a lot this was very helpful Tue, 24 Apr 2012 19:05:24 -0400 http://www.postgresonline.com/journal/archives/72-guid.html#c4393 http://www.postgresonline.com/journal/archives/172-Starting-PostgreSQL-in-windows-without-install.html#c4354 http://www.postgresonline.com/journal/archives/172-Starting-PostgreSQL-in-windows-without-install.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=172 nospam@example.com (Chris) I'm trying to use the script on version 8.3.18-1, and I'm getting the following error: "The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail." Has anyone here had any luck with this versions other than 9? Fri, 13 Apr 2012 10:36:29 -0400 http://www.postgresonline.com/journal/archives/172-guid.html#c4354 http://www.postgresonline.com/journal/archives/247-PostGIS-2.0.0-is-out.html#c4343 http://www.postgresonline.com/journal/archives/247-PostGIS-2.0.0-is-out.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=247 nospam@example.com (Regina) Vlad, thanks. Fixed. Mon, 09 Apr 2012 07:07:40 -0400 http://www.postgresonline.com/journal/archives/247-guid.html#c4343 http://www.postgresonline.com/journal/archives/247-PostGIS-2.0.0-is-out.html#c4337 http://www.postgresonline.com/journal/archives/247-PostGIS-2.0.0-is-out.html#comments http://www.postgresonline.com/journal/wfwcomment.php?cid=247 nospam@example.com (Vladimir Naumov) Hi! There's a typo in the second sentence from the end. Nice work! I was expecting version 2.0.0, now my colleagues can have access to shared raster datasets. It's very good for my company since we work a lot with satellite imagery and use it in sustainable forest management. Thanks for the work! Sun, 08 Apr 2012 02:42:20 -0400 http://www.postgresonline.com/journal/archives/247-guid.html#c4337