Category Archives: SQL

Helpful SQL Queries for those odd situations

tPostgresqlOutput_3 org.postgresql.util.PSQLException: ERROR: length for type varchar must be at least 1


There are several reasons why you may receive this error when using Talend Open Studio, but the one I recently encountered was user error.  When I created my schema, I had placed my varchar length values under the “Precision” column instead of “Length” column.  This was the cause of my error.

Misplaced varchar lengths in Talend Open Studio schema

I prefer these errors over product bugs!

 

 

Leave a comment

Filed under SQL

Talend Data Integration – Exception in component tOracleOutput_1java.lang.ArrayIndexOutOfBoundsException: -32703


You are probably seeing this error as you attempt to transfer data with many date values from one Oracle database to another Oracle database using Talend Studio.

Well quick fix (not sure why it works) is to decrease the commit size in your OracleOutput node. The default is 10,000.  I reduced mine to 500.  See illustration below.

Changing Commit In Talend Data Integration

Comments are always welcome.

Leave a comment

Filed under SQL

Import Data from DB2 to SQL Server 2012


Below are steps that you can take to import data from IBMs DB2 database into SQL Server.  I’m using SQL Server 2012 Express edition.

Figuring this out has been one of the more frustrating tasks for me, so I hope this helps others like me.

Step 1.) Download and install DB2 Data Server Runtime Client (9.7 Fix Pack 4). Found in IBMs download page.

Step 2.) Extract the contents of your download to Program Files / IBM

Extract and paste the folder and it's contents under Program Files /IBM

Extract and paste the folder and it’s contents under Program Files /IBM

Step 3.) Open your command prompt (as Administrator).  Navigate to the “bin” folder under that folder you just copied into your directory.

Type db2oreg1 -i then type db2oreg1 -setup

Registering and setting up your DB2 ODBC Driver in Windows

Registering and setting up your DB2 ODBC Driver in Windows

Step 4.) Open up SQL Server Management Studio.  Right click on the database that you want to import DB2 data into

Right click on database name, select Tasks, select Data Import

Right click on database name, select Tasks, select Data Import

Step 5.) Select the proper driver for DB2

Select the DB2 Driver that you need to connect to the database

Select the DB2 Driver that you need to connect to the database

Step 6.) Set the connection properties to your DB2 database

Set DB2 Connection properties and test connection

Set DB2 Connection properties and test connection

Step 7 .)  Proceed with the wizard (Write a query to extract data or copy a table).  I chose to write a query.

Copy a table or write a query

Step 8.) Preview mapping of query or table copy

Destination attribute types based on SQL evaluation of values from query

Destination attribute types based on SQL evaluation of values from query

Step 9). Success!

Successful Transfer from DB2 to SQL Server 2012

If you experience any problems and would like some help, please be as specific as possible with regards to the steps you took to get where you are.

 

Best wishes!

Leave a comment

Filed under Computer - Technical, SQL

SELECT without table (FROM CLAUSE) in DB2


You are here because you are trying to generate a string using a SELECT statement in DB2, much like you would do in SQL SERVER.  In other words, you want to execute a SELECT statement without the FROM clause in DB2 as you would in SQL SERVER.  Below is how you do that.

In SQL Server, if you wanted to a string of text to be returned to you, you would simply type:

SELECT 'Whatever you want your string to say'

The results would be :

Whatever you want your string to say  

In the results tab.

If you want to accomplish the same thing in DB2, instead of SELECT, use the word VALUES.  So your statement would be typed like this:

VALUES 'Whatever  you want your string to say'

The results will be the same

This is comparable to a SELECT statement with NO FROM clause in SQL SERVER

This is comparable to a SELECT statement with NO FROM clause in SQL SERVER

Leave a comment

Filed under SQL

Installing the sample database in IBM DB2 C in Linux (Ubuntu)


After you install DB2 on your Linux system (along with IBMs Data Studio) you may have problems connecting to the sample database.  It may be that it did not install.  If you have any questions about the above, post a comment.

To install the sample database, first be sure you log in to your instance.  By default during your first install, your instance name should be db2inst1

 

 

Login to your db2inst1 account

Once you are in you’re logged into your instance session, navigate to your sqllib/samples folder.   Execute the db2sampl file.  This will begin the Sample Database installation process.

installing sample DB2 database in Linux

 

Once the installation is complete, connect to the database by typing: db2 connect to sample.

You can list the tables in the database by typing: db2 list tables

connect to and list tables in database

 

Now that you’ve set up your playground, have fun.

Leave a comment

Filed under Linux, SQL

How to upload multiple Excel 2007 files to SQL Server 2005


If you are here, then you are probably encountering the following:

  • SQL Server 2005 Import/Export wizard is not recognizing your Excel 2007 (xlsx) file(s)
  • You have many files that you need to upload and can’t afford to do them one by one
The good news is that you don’t have to.  Also, this post applies to Excel 97-2003 files, text files and anything else you can upload to SQL Server 2005.  I figured I would begin with one of the more difficult approaches and anything simpler can be deduced from these steps.  For this post, I am using a lot of images and few words.  If you have any questions, please leave a comment.
Also, to accomplish the entire task, you will need a version of SQL Server 2005 (or later) and SQL Server Business Intelligence Development Studio (BIDS).  If you have SQL Server Express, you can download BIDS from Microsoft here.
The process is going to be simple.
  1. Use the Import/Export Wizard to import data and save the SSIS package to your computer.
  2. Open up BIDS and modify your SSIS package.
  3. Run your SSIS package.

This slideshow requires JavaScript.

Below is a narrative for each picture.

Leave a comment

Filed under Computer - Technical, SQL

Using Navicat with Kilu.de or GoDaddy MySql – Navicat und Kilu.de oder GoDaddy


A great place to get free hosting with PHP and MySQL support is kilu.de.  To access MySQL they provide access through phpMyAdmin.  However, if you’re a Navicat user, you like to make use of it.  Here are the steps you take to interface Navicat with your MySQL instance on kilu.de.  The following steps also apply when using Navicat with GoDaddy

 

1.) Download the following php tunnel file (zipped) and upload to the root (or any folder of your server).  If you upload to a place other than your root, be sure to note the path.

Laden Sie die folgenden php-Tunnel-Datei (gezippt) und laden Sie die root (oder einen beliebigen Ordner Ihres Servers). Wenn Sie laden zu einem anderen Ort als root, müssen Sie den Pfad zur Kenntnis.

2.) Find your FTP connection settings in kilu.de.  You will use the same ones for your Navicat connection

Finden Sie Ihren FTP-Verbindung Einstellungen in kilu.de. Sie werden die gleichen, die für Ihre NavicatVerbindung verwenden

3) Open your user Navicat, create a new connection.  Supply it with your FTP Server, leave the default port (3306), and supply your username and password as noted in the FTP settings.

Öffnen Sie Ihren Benutzernamen Navicat, eine neue Verbindung erstellen. Versorgung mit Ihrem FTP-Server, lassen Sie den Standard-Port (3306), und liefern Sie Ihren Benutzernamen und Ihr Passwort ein, wie in der FTP-Einstellungen angegeben.

4.)  Go to the HTTP tab of your connection properties window.  Type in the URL path of your tunnel.php file.

Gehen Sie auf die Registerkarte HTTP Ihrer Verbindung Eigenschaften-Fenster. Geben Sie den URL-Pfad Ihres tunnel.php Datei.

 

5.) Test your connection, you will see the following prompts appear if your connection is successful.

Testen Sie Ihre Verbindung, sehen Sie die folgenden Eingabeaufforderungen angezeigt, wenn die Verbindung erfolgreich ist.

     

 

 

Leave a comment

Filed under Computer - Technical, SQL