Postgresql ole db provider for windows

27 product(s) were found in this category.

AspSQLProvider

DBD::Pg

dbExpress driver for PostgreSQL

dotConnect for PostgreSQL

Excel Add-in for PostgreSQL

libpqxx

LinqConnect

Lua binding for PostgreSQL

Npgsql

ODBC for PostgreSQL

pgfe

pgintcl

pgtcl

pgtcl-ng

PostgresClientKit

PostgresDAC

PostgreSQL Data Access Components

postgresql-kit

PostgreSQL Native OLEDB Provider (PGNP)

PostgreSQL-SDBC

psql-api

psqlJDBC

psqlODBC

psycopg

slurf

SQL Relay

UniDAC

Note: The PostgreSQL Global Development Group do not endorse or
recommend any products listed, and cannot vouch for the quality or reliability
of any of them.

Подключение к источнику данных PostgreSQL (мастер импорта и экспорта SQL Server)

В этом разделе показано, как подключаться к источникам данных PostgreSQL со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server.

Подробные требования и необходимые условия для подключения к базе данных PostgreSQL выходят за рамки этой статьи Майкрософт. В ней предполагается, что у вас уже установлено клиентское программное обеспечение PostgreSQL и вы можете успешно подключиться к целевой базе данных PostgreSQL. Для получения дополнительных сведений обратитесь к администратору базы данных PostgreSQL или к документации по PostgreSQL.

Получение драйвера ODBC для PostgreSQL

Установка драйвера ODBC с помощью построителя стека

Запустите построитель стека, чтобы добавить драйвер ODBC для PostgreSQL (psqlODBC) в вашу установку PostgreSQL.

Скачивание последней версии драйвера ODBC

Или скачайте установщик Windows для последней версии драйвера ODBC для PostgreSQL (psqlODBC) прямо с этого FTP-сайта — https://www.postgresql.org/ftp/odbc/versions/msi/. Извлеките содержимое ZIP-файла и запустите файл MSI.

Подключение к PostgreSQL с помощью драйвера ODBC для PostgreSQL (psqlODBC)

Указываемые параметры (драйвер ODBC для PostgreSQL)

Параметры подключения для этого поставщика данных и драйвера ODBC одинаковы независимо от того, является ли PostgreSQL источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.

Чтобы подключиться к PostgreSQL с помощью драйвера ODBC для PostgreSQL, соберите строку подключения, используя указанные ниже параметры и их значения. Полный формат строки подключения приведен после списка параметров.

Вы можете получить помощь в построении строки подключения. Кроме того, вместо указания строки подключения вы можете предоставить существующее имя DSN (имя источника данных) или создать новое. Дополнительные сведения об этих возможностях см. в разделе Подключение к источнику данных ODBC.

Драйвер
Имя драйвера ODBC — PostgreSQL ODBC Driver(UNICODE) или PostgreSQL ODBC Driver(ANSI).

Server
Имя сервера PostgreSQL.

порт.
Порт, используемый для подключения к серверу PostgreSQL.

База данных
Имя базы данных PostgreSQL.

Uid и Pwd
Uid (идентификатор пользователя) и Pwd (пароль) для подключения.

Формат строки подключения

Ниже приведен формат типичной строки подключения.

Ввод строки подключения

Введите строку подключения в поле ConnectionString либо введите имя DSN в поле Dsn на странице Выбор источника данных или Выбор назначения. После того как вы введете строку подключения, мастер проанализирует ее и отобразит отдельные свойства и их значения в списке.

В приведенном ниже примере используется следующая строка подключения:

Ниже показан экран, который появляется после ввода строки подключения.

Другие поставщики данных и дополнительные сведения

Сведения о подключении к PostgreSQL с помощью поставщика данных, не представленного в этом списке, см. в разделе Строки подключения PostgreSQL. Этот сторонний сайт также содержит дополнительные сведения о поставщиках данных и параметрах подключения, описанных на этой странице.

Источник

27 product(s) were found in this category.

AspSQLProvider

AspSQLProvider is a PostgreSQL implementation of the ASP.NET 2.0 Membership, Role, Profile and Session-State Store Provider.

DBD::Pg

PostgreSQL database driver for the Perl DBI module

dbExpress driver for PostgreSQL

dbExpress Driver for PostgreSQL is a database-independent layer that defines common interface to provide fast access to PostgreSQL from from Delphi and C++Builder, including Community Edition, on Windows and macOS (32-bit and 64-bit) and Linux (64-bit) platforms. For this server, dbExpress provides a driver as an independent library that implements the common dbExpress interface for processing queries and stored procedures.

As data-access layer is thin and simple, dbExpress provides high performance database connectivity and is easy to deploy. You can use dbExpress driver for PostgreSQL in the same way as the one supplied by Embarcadero.

Direct Mode

Allows your application to work with PostgreSQL directly without involving PostgreSQL connectivity solutions, thus significantly facilitating deployment and configuration of your applications.

DB Compatibility

Our product supports PostgreSQL server versions from 8 to 12, Microsoft Azure Database for PostgreSQL, Amazon RDS for PostgreSQL; Amazon Aurora, and Google Cloud for PostgreSQL.

IDE Compatibility

Our product is compatible with the latest IDE versions and editions including Community Edition: Embarcadero RAD Studio 10.4 Sydney, Delphi 10.4 Sydney, and C++Builder 10.4 Sydney. It is also compatible with the previous IDE versions since Delphi 6 and C++Builder 6.

Learn more about dbExpress Driver for PostgreSQL on Devart website.

dotConnect for PostgreSQL

dotConnect for PostgreSQL is a high-performance ORM enabled data provider for PostgreSQL that builds on ADO.NET technology to present a complete solution for developing PostgreSQL-based database applications. It introduces new approaches for designing application architecture, boosts productivity, and facilitates the development of database applications.

Our ADO.NET PostgreSQL provider supports a wide range of PostgreSQL-specific features, such as secure SSL connections, PostgreSQL notifications, PostgreSQL bulk data loading, GEOMETRY, PostgreSQL ARRAY types, and others.

Key Features

Advanced Visual Studio Integration. dotConnect for PostgreSQL provides a vast number of GUI tools that increase your productivity. For example, it includes DataSet tools, such as DataSet Wizard, DataSet Editor, DataSet Manager, provide you an easy way to create and edit typed and untyped DataSets.

PostgreSQL support: from 7.1 to 13

Visual Studio support: 2008 – 2019

.NET Core 1.x, 2.x, and 3.0

.NET Compact Framework 2.0 and above

Entity Framework Core 1.x, 2.x, 3.1, and 5.0

Excel Add-in for PostgreSQL

Devart Excel Add-in for PostgreSQL allows you to quickly and easily connect Microsoft Excel to PostgreSQL, load data from PostgreSQL to Excel, instantly refresh data in an Excel workbook from the database, edit these data and save them back to PostgreSQL.

Use Excel to work with PostgreSQL data, easily perform data cleansing and de-duplication. Apply all the Excel’s powerful data processing and analysis capabilities to these data.

Connect to PostgreSQL Directly from Excel

To connect Excel to PostgreSQL you just need to specify its host, port, and your credentials. As for security, PostgreSQL Excel Add-in supports secure SSL connections, secure authentication with single sign-on, etc. Connection information may optionally be stored in the Excel file, or you may reenter it whenever necessary.

Use Query Builder or SQL to Import Data You Need

Visual Query Builder allows you to choose a table to import data from, select the columns from this table and related tables, and set filter conditions and data ordering options visually. If you need to select data more precisely, you can use SQL to select data you need.

Instantly Refresh Data Whenever You Need

It’s very convenient to always have fresh data from the database right in your workbook. Imagine, you have performed data analysis in your Excel workbook, built some charts for your PostgreSQL data, and you can re-read data from the source and update all this to the current state by just clicking a button on the ribbon whenever you need.

Edit PostgreSQL Data Like Usual Excel Spreadsheets

Instant Data Validation

Excel Add-in for PostgreSQL instantly validates entered data and highlights errors, such as incorrect dates, duplicate primary key values, etc.

Learn more about Excel add-in for PostgreSQL on Devart website.

libpqxx

libpqxx is the official C++ client API for PostgreSQL, the enterprise-strength open-source database software.

LinqConnect

LINQ to SQL Compatible.

LinqConnect was developed closely to LINQ to SQL and retains full compatibility with it. Interface of the LinqConnect classes is compatible with LINQ to SQL ones. If you are a LINQ to SQL developer, you don’t need to learn much and can start developing with LinqConnect immediately.

Unlike LINQ to SQL, LinqConnect is an actively developed and supported product, and it offers a number of benefits over LINQ to SQL. It supports more database servers, more development platforms, more LINQ features, more mapping kinds, provides better performance, etc.

Lua binding for PostgreSQL

Access your PostgreSQL data from the Lua programming language. luapgsql is a libpq binding for Lua.

Npgsql

ODBC for PostgreSQL

Devart ODBC Driver for PostgreSQL provides a high-performance and feature-rich connectivity solution for ODBC-compliant applications to access PostgreSQL databases from Windows, macOS, and Linux, both 32-bit and 64-bit. Full support for standard ODBC API functions and data types implemented in our driver makes interaction of your application with PostgreSQL fast, easy and extremely handy.

Direct Connection

Our connectivity solution enables various ODBC-aware applications to establish a direct connection to PostgreSQL via TCP/IP to eliminate the need for PostgreSQL libraries or clients.

Integration

The driver is compatible with 3rd-party tools for data analysis, such as Microsoft Excel, and integrates with various IDEs and systems like Visual Studio, etc.

Platforms Variety

ODBC Driver for PostgreSQL can be used with 32-bit and 64-bit applications on both x32 and x64 platforms, so there is no need to additionally configure the driver, applications or environment.

Fully Unicode Driver

With our fully Unicode-compliant driver, you can properly retrieve and modify any data in multilingual PostgreSQL databases, regardless of their character set: Latin, Cyrillic, Hebrew, Chinese, etc., and in any language environment.

Bulk Updates

Moreover, with our driver you can perform bulk updates to PostgreSQL database by combining SQL statements into batches, thus simplifying and speeding up large data modification to PostgreSQL.

Secure Connection

Work with PostgreSQL securely using SSL connection options provided in our driver along with the support for SSL protocol. You can also connect to PostgreSQL securely via SSH tunnel. SSH protocol is one of the most reliable protocols for data encryption.

Learn more about more ODBC for PostgreSQL features on Devart website.

A modern C++ API to PostgreSQL that simplifies working with PostgreSQL in C++.

pgintcl

This is a Tcl interface to PostgreSQL, which is itself written entirely in Tcl, and does not rely on libpq. It is highly but not entirely compatible with other Tcl interfaces, although it is slower.

pgtcl

This is a project to evolve and upgrade libpgtcl, the Tcl client interface to PostgreSQL. Application writers will be able to interface to PostgreSQL from Tcl using this interface.

pgtcl-ng

PostgreSQL Tcl Interface Next-Generation (pgtcl-ng) is a loadable Tcl/Tk module for accessing PostgreSQL databases. It extends the pre-8.0 libpgtcl from an early Pgtcl release and from the companion project pgintcl.

PostgresClientKit

PostgresClientKit provides a friendly Swift API for operating against a PostgreSQL database.

PostgresDAC

PostgresDAC is a component suite for direct access to PostgreSQL servers from Borland Delphi and C++ Builder applications

PostgreSQL Data Access Components

PostgreSQL Data Access Components (PgDAC) is a library of components that provides native connectivity to PostgreSQL from Delphi and C++Builder including Community Edition, as well as Lazarus (and Free Pascal) on Windows, Linux, macOS, iOS, and Android for both 32-bit and 64-bit platforms.

PgDAC is designed to help programmers develop really lightweight, faster and cleaner PostgreSQL database applications without deploying any additional libraries.

Native Connectivity to PostgreSQL

PgDAC-based DB applications are easy to deploy, do not require installation of other data provider layers (such as BDE or ODBC), and that’s why they can work faster than the ones based on standard Delphi data connectivity solutions. Moreover, PgDAC provides an additional opportunity to work with PostgreSQL directly via TCP/IP without involving PostgreSQL Client.

Key Features:

Read more about PostgreSQL Data Access Component Editions.

postgresql-kit

postgresql-kit is a set of frameworks, tools, applications and examples for deploying the PostgreSQL database on Mac OS X and iOS platforms.

On the server-side, it provides an embedded server framework which allows you to put a postgresql database right in your Cocoa application. On the client-side, it helps you interact and use all the features of libpq, including converting data seemlessly between Foundation objects and Postgresql data types.

Sample applications for the Macintosh and iOS devices is included in the project, which is licensed with the Apache open-source license.

PostgreSQL Native OLEDB Provider (PGNP)

PostgreSQL Native Provider (PGNP) is an OLEDB Provider for PostgreSQL database.

PostgreSQL-SDBC

Native PostgreSQL driver for LibreOffice and Apache OpenOffice. It is faster, seamlessly integrated and there is no need to install and setup a JDBC or ODBC driver separately. Compatible with PostgreSQL version 8.4 and later.

Not entirely feature-complete, but the basics work.

psql-api

A PostgreSQL RESTful API for SOA applications.

Source code is available under MIT license at:

psqlJDBC

The PostgreSQL JDBC driver. It allows Java programs to connect to a PostgreSQL database using standard, database independent Java code. It is a pure Java (Type IV) implementation, so all you need to do is download a jar file and you’re on your way.

The driver provides are reasonably complete implementation of the JDBC 3 specification in addition to some PostgreSQL specific extensions.

psqlODBC

psqlODBC is the official PostgreSQL ODBC Driver.

psycopg

Psycopg is the most popular PostgreSQL adapter for the Python programming language. At its core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

slurf

slurf is an easy to use postgresql frontend. You cannot use it to create tables and such, just to input,update and delete data. It is very good for end user data input (phonebook,invoices etc.).

SQL Relay

SQL Relay is a persistent database connection pooling, proxying, throttling, load balancing and query routing/filtering system for PostgreSQL on Linux, Unix and Windows.

Command line clients and extensive documentation are also provided.

SQL Relay is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated or clustered databases and throttling database access.

UniDAC

Universal Data Access Components (UniDAC) is a library of components that provides direct access to multiple databases from Delphi and C++Builder including Community Edition, as well as Lazarus (and Free Pascal) on Windows, Linux, macOS, iOS, and Android for both 32-bit and 64-bit platforms.

UniDAC provides access to PostgreSQL, Oracle, Microsoft SQL Server, MySQL, InterBase, Firebird, SQLite, Microsoft Access, DB2, DBF, Sybase Advantage Database Server, etc.

The tool is compatible with the latest IDE versions and editions including Community Edition: Embarcadero RAD Studio 10.4 Sydney, Lazarus (and FPC). It is also compatible with the previous IDE versions since Delphi 6 and C++Builder 6.

Key features:

Note: The PostgreSQL Global Development Group do not endorse or recommend any products listed, and cannot vouch for the quality or reliability of any of them.

Источник

Problem

We need to have PostgreSQL and SQL Server database platforms communicate in our environment. We need to access the PostgreSQL data from SQL Server in an efficient manner. Based on the steps from your first tip, how can we take the next steps to setup the data access? Can we create a Linked Server from SQL Server to PostgreSQL to access the data?

Solution

In part 1 of this series, we rolled out a simple database infrastructure with a PostgreSQL instance and a SQL Server instance. We have seen how both the servers could communicate with each other at network level. Then we restored a sample database in PostgreSQL and created two of its table structures in SQL Server.

In this tip, we will show how SQL Server can access Postgres data and populate those tables.

Install PostgreSQL ODBC Driver

Although the de-facto data access library for any modern database should be based on OLE DB, PostgreSQL’s official site doesn’t list any freely available x64 bit OLE DB providers. As mentioned in part 1, there’s a 64-bit OLE DB provider available from a third-party vendor. However, that driver comes with a price tag. The free OLE DB version available from Postgres site is for 32-bit only.

However, PostgreSQL also provides a 64-bit ODBC driver that’s downloadable from its official site. In our example, we will download and install this 64-bit ODBC driver (psqlODBC) on our SQL Server.

Step 1: Remote desktop to SQL Server

Step 3: Double-click to start the psqlodbc_x64.msi installer. The next few images show the straightforward installation process.

Create ODBC Data Source

Once the driver has been installed, it’s time to create a System DSN from it. So let’s start the ODBC Data Source (64 bit) application from the Server Manager applet (see below).

In the next few screenshots, we can see how an ODBC data source is created.

Step 1: First let’s choose the System DSN tab and then click Add.

Step 2: Next we choose the PostgreSQL Unicode (x64) version and click Finish.

Step 3: In the dialog box that pops-up, provide a name and description for the data source, specify the database name, server’s IP address, port, user name and password as connection parameters. Once done, test the details by clicking on the Test button.

If the test is successful, click Save and then click OK in the ODBC Data Source Administrator.

Create a SQL Server Linked Server to PostgreSQL

Step 1: Start SQL Server Management Studio and connect to the SQL Server instance as «sa» or a sysdmin role member. Expand the Server Objects folder, right click on the Linked Servers node, and then choose «New Linked Server. » option from the pop-up menu.

On the General tab of the New Linked Server dialog box, choose the «Other data source» option, select «Microsoft OLE DB Provide for ODBC Drivers» option from the Provider drop-down list, provide a name for the Product and specify the Data Source name. The data source should be the one we just created: in this case it’s world_db_postgres.

Step 2: In the Security tab, choose the fourth option («Be made using this security context») and provide a login name and password to connect to the remote PostgreSQL instance. In this case we have used the built-in Postgres super user account to keep things simple.

Step 3: In the Server Options tab, choose the following options:

Click OK. If the connection is successful, the Linked Server will be created without any error.

Expanding the Linked Server node in SQL Server Management Studio would show us the tables in the world database in PostgreSQL.

Access PostgreSQL Data from SQL Server

Now that we can see the remote data, let’s fetch it into SQL Server. Open a new query window in Management Studio, select the world database and execute the following commands:

The result should show 4079 rows have been copied. Next, execute this command:

This should show 239 rows have been copied.

To be sure, you can count the number of rows in the local tables.

Conclusion

So now we have it. We have created an ODBC connection against the remote PostgreSQL instance, created a linked server on top of it and then executed two commands to copy across the data. There was no need to export the source data into text files and importing them using BCP or BULK INSERT.

This process can obviously be automated via scripts and stored procedures that are called by SQL Server Integration Services Packages or SQL Server Agent Jobs. SQL Server doesn’t give us any option to create push or pull replication subscription against PostgreSQL databases.

We haven’t discussed data access speed via ODBC, nor have we discussed any migration pitfalls like data type mismatches. The idea was to show how SQL Server can access PostgreSQL data seamlessly. Executing any PostgreSQL functions or stored procedures from SQL Server is another area your data migration team may have to consider.

Next Steps

Источник

Connecting SQL Server Management Studio to PostgreSQL via ODBC Driver

Requirements

In order to avoid incorrect integration with MS SSMS, the working environment must meet the following conditions:

Connecting to PostgreSQL from SQL Server Management Studio using ODBC Driver for PostgreSQL

You can use the Microsoft SQL Server Management Studio to connect your PostgreSQL data to an SQL Server instance. Linked Server is a tool of MS SQL Server that allows to execute distributed queries to refer tables stored on non-SQL Server datbase in a single query. With linked servers, you can execute commands against different data sources such as PostgreSQL and merge them with your SQL Server database. You can create a linked server with one of these methods: by using the options in the Object Explorer or by executing stored procedures.

Below are major advantages of using SQL Server Linked Servers to connect to PostgreSQL:

How to configure a SQL Server Linked Server to connect to PostgreSQL

You can follow the steps to create a linked server for PostgreSQL in SQL Server Management Studio by using Object Explorer:

The linked server will appear under the Linked Servers in the Object Explorer Pane. You can now issue distributed queries and access PostgreSQL databases through SQL Server.

Retrieving Data From PostgreSQL

Disable the Allow inprocess option of MSDASQL OLE DB Provider for ODBC Drivers. For this, find the MSDASQL provider in the list of Linked Servers and double-click on it

In the appeared Provider Options window, clear the Allow inprocess checkbox:

Create a new Linked Server

Make sure to select Microsoft OLE DB Provider for ODBC Drivers and specify the following parameters:

The PostgreSQL tables are already available to be fetched. To query the linked server, click New Query in the toolbar:

Enter your SQL query in the editor window and click Execute to run the query:

As a result, you can see the contents of the selected table retrieved directly from the PostgreSQL account you are connected to.

Attention

If the Linked Server was created with the Allow inprocess option enabled, then you should delete this Linked Server and create it again with the Allow inprocess option disabled.

Источник

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.