Tag Archive for 'sql'

Using SQL WITH clause to create temporary static tables at query time

A few days ago, I came across the following problem: I currently work on a project where I am the responsible of an application which generates entries to a log table every time a job is executed. This table contains a lot of information on statuses of jobs, possible problems, exceptions, duration, aso. I was working on some analytics on this data and needed to enrich the data by the version of the software which generated the log entry (since we were not capturing this in the log table). From our configuration management tool, I was able to extract the dates when which versions of the software was deployed in production

Problem

My intention was to create a temporary table to join onto the  logged entries, but I didn´t want to create the tables on the Oracle server (mainly because they would have been just temporary tables and because the schema-user I was using didn´t have the rights to create tables).

Continue reading ‘Using SQL WITH clause to create temporary static tables at query time’

SQL Connection Strings

In the last couple of weeks I have been working a lot with different databases which I had to connect to from Java. It is sometime stressful to look up the right format of the connection string to the database. Even though, these strings should, or are meant to be standardized, they are not.

I found this very helpful website ConnectionStrings.com which lists the connection strings for open-source as well as professional commercial databases. This list includes among others, the connection strings for Microsoft SQL Server 2008, MySQL, Oracle, IBM DB2, Informix, Postgre SQL, Caché, SQLite, …

As an example, if you want to connect to a server in a replicated server configuration without concern on which server to use, use the following connection string:

Server=serverAddress1, serverAddress2, serverAddress3;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;

I want to share this information with you, because it can save you a lot of time, looking up those strings in tutorials or in the documentation of the different databases. Continue reading ‘SQL Connection Strings’