There is a great blog post on Connecting to a PostgreSQL Instance Using SQL Server Integration Services which you’ll want to read. I will recap the steps directly from this post:

Installation of both drivers (32-bit & 64-bit) is identical. Here are the basic steps to get the driver working:

1. Download the latest .msi file for Windows from this location: http://www.postgresql.org/ftp/odbc/versions/msi/
2. Run the msi file on your SQL Server.
3. Launch the ODBC Administrator Utility and choose the type of data source you need; File, System or User and click Add.
4. The ODBC Administrator will present a list of drivers. Scroll to the bottom and you will see two options for PostreSQL; ANSI and Unicode. Select the version you need and click Finish.
5. The Administrator will present a screen on which you must supply a database name, server name, user name and password.
6. After you have supplied values for these fields, click the Datasource button and make sure the Use Declare/Fetch box is checked. The driver will fail to retrieve larger datasets if you do not check this box. I have not yet found a satisfactory answer for why this is so.

Now you are ready to build a new connection manager in SSIS and hook it to the PostGRES data source you just created. Use the following settings when building out the connection manager:

1. Select the .Net ProvidersODBC Data Provider.
2. Select the “Use connection string” radio button. Using the values you configured in the ODBC Administrator, build a connection string as follows: Dsn=PostgreSQL35W;uid=User1
3. Enter the User name and Password in the fields provided.
4. Test the connection and you should be ready to go.

Here are a few bits of information I’d like to add to this excellent blog post, mainly for my own purposes should I run into this again:

I noticed on the PostgreSQL msi page that there were several versions of the drivers. I took the most recent of the 32bit, psqlodbc_09_01_0100-1.zip. The 64 bit drivers have “64 bit” in the name.

Harry potter full movie hindi. Watch the Full Movie here: Support me on Patreon: https://www.patreon.com.

The 32-bit ODBC drivers for PostgreSQL get installed here C:Program Files (x86)psqlODBC

After installing the 32 bit driver per the instructions, in your Visual Studio solution be sure to change your Project Properties – Debugging – Run64BitRuntime property to False. This will force it to run in 32-bit mode.

If you are triggering your package from SQL Server Agent, set the SQL Server Agent job to run in 32-bit mode

For SQL2012, be sure to put the password in a Project Parameter so it is retained in the package on deployment to Integration Services, and doesn’t get stripped out due to package protection.

The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. But it supports only a small subset of the SQL standard and it also provides no support for database specific features.

So what shall you do, if you need to use a database-specific query feature or your DBA gives you a highly optimized query that cannot be transformed into JPQL? Just ignore it and do all the work in the Java code?

Of course not! JPA has its own query language but it also supports native SQL. You can create these queries in a very similar way as JPQL queries and they can even return managed entities if you want.

How to call native SQL queries with #JPAhttps://t.co/Hj5VZ1zDpR

— Thorben Janssen (@thjanssen123) January 19, 2017

Don’t want to read? You can watch it here!

Watch this video on YouTube

Create dynamic native queries

Creating a dynamic native query is quite simple. The EntityManager interface provides a method called createNativeQuery for it. This method returns an implementation of the Query interface which is the same as if you call the createQuery method to create a JPQL query.

The following code snippet shows a simple example in which I used a native query to select the first and last names from the author table. I know, there is no need to do this with a native SQL query. I could use a standard JPQL query for this, but I want to focus on the JPA part and not bother you with some crazy SQL stuff 😉

The persistence provider does not parse the SQL statement, so you can use any SQL statement that is supported by your database. In one of my recent projects for example, I used it to query PostgreSQL specific jsonb columns with Hibernate and mapped the query results to POJOs and entities.

As you can see, the created Query can be used in the same way as any JPQL query. I didn’t provide any mapping information for the result and so the EntityManager returns a List of Object[] which need to be handled afterwards. Instead of mapping the result yourself, you can also provide additional mapping information and let the EntityManager do the mapping for you. I get into more details about that in the result handling section at the end of this post.

Parameter binding

Similar to JPQL queries, you can and should use parameter bindings for your query parameters instead of putting the values directly into the query String. This provides several advantages:

• you do not need to worry about SQL injection,
• the persistence provider maps your query parameters to the correct types and
• the persistence provider can do internal optimizations to provide a better performance.

JPQL and native SQL queries use the same Query interface which provides a setParameter method for positional and named parameter bindings. But the use of named parameter bindings for native queries is not defined by the JPA specification. Positional parameters are referenced as “?” in your native Query and their numbering starts at 1.

Hibernate also supports named parameter bindings for native queries but as I already said, this is not defined by the specification and might not be portable to other JPA implementations.
By using named parameter bindings, you define a name for each parameter and provide it to the setParameter method to bind a value to it. The name is case-sensitive and needs to be prefixed with a “:” symbol.

Result handling

As you have seen in the previous code snippets, your native Query returns an Object[] or a List of Object[]. You can change that, if you provide additional mapping information to the EntityManager. By doing this you can tell the EntityManager to map the result into managed entities, scalar values of specific types or POJOs. The simplest way to map the result of a native query into a managed entity is to select all properties of the entity and provide its as a parameter to the createNativeQuery method.

All other mappings, like the following one which maps the query result into a POJO, need to be defined as SQLResultSetMappings.

To use this mapping, you need to provide the name of the mapping as a parameter to the createNativeQuery method.

These mappings are quite powerful and you can even combine them to map a query result into multiple entities, POJOs and scalar values. Have a look at the following posts to dive deeper into SQLResultMappings:

Create named native queries

You will not be surprised, if I tell you that the definition and usage of a named native query is again very similar to a named JPQL query.

In the previous code snippets, I created 3 dynamic native queries to select the names of all authors, to select all authors as entities and to select all authors and map them into AuthorValue POJOs. I used the same queries in the following code snippet and created named queries for them.

As you can see, the definition looks very similar to the definition of named JPQL queries and you can even provide the result class or the name of an SQLResultSetMapping. The named native queries are used in exactly the same way as named JPQL queries. You only need to provide the name of the named native query as a parameter to the createNamedQuery method of the EntityManager.

Conclusion

JPQL provides an easy way to query data from the database but it supports only a small subset of the SQL standard and it also does not support database specific features. But this is not a real issue. You can use all these features by creating native SQL queries via EntityManager.createNativeQuery(String sql) which are send directly to the database.

Do you have any questions or comments? Do you use native SQL queries often in your projects or do you try to avoid them?
Tell me about it in the comments below or on twitter.