Beginner’s Guide to E.T.L (Extract, Transform and Load) – Connection Set-Up

Connection Setup for connection type: PostgreSQL

[We are now setting up a connection to A database i.e if your source is a database]
There are 3 ways of access provided for Connections using PostgreSQL
a. Native (JBDC)
b. ODBC
c. JNDI

a. Native (JBDC)
I. Enter the Connection Name: Anyname
II. Select the connection type: PostgreSQL
III. Host Name: localhost [This can be an ip address]
Database Name: LearnETL[Name of the database you are using]
Port Number: 5432 or your required Port Number
User Name: Your database user name
Password: Your database password
IV. Test Connections and OK.

b. JNDI
Here we need to go to the data-integration folder and open up the sub-folder “simple-jndi” and edit jdbc.properties

Here we need to write the following code:

ETL_Connection/type=javax.sql.DataSource
ETL_Connection/driver=org.postgresql.Driver
ETL_Connection/url=jdbc:postgresql://localhost:5432/LearnETL
ETL_Connection/user=postgres
ETL_Connection/password=postgres

ETL_Connection: name of the connection
localhost:5432/LearnETL: localhost is the host name, 5432 is the port number and LearnETL is the Database name.
user: username
password: Password

Save and back to the Database connection
Restart your PDI.

and in the Setup, select JNDI and for
JNDI Name : name of your connection [ETL_Connection]

c. ODBC

This is not commonly used but what will be needed

are as follows:

1.Install the PostgreSQL ODBC driver which can be downloaded.
2.Select the PostgreSQL ODBC Unicode and then
3. Setup
enter Data source Source, Name, Server, Username, Password and Port. Test and Save if Connection is OK.!
4. a bundle of JAR files to be copied in your Java folder as this ODBC bundle files has been discontinued in JAVA 8 Bridge

Thank You
Sohail Izebhijie

MySQL C – API programming

MySQL C – API programming

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web.

Basic Structure of C Programs that uses MySQL C API 1. All programs must include <mysql/mysql.h> as the last include.2. Define MYSQL type variable. NOTE: THERE CAN BE ONLY ONE MYSQL VARIABLE.3. Initialize MYSQL type variable with mysql_init()4. Load any options, if required, by using mysql_options(). If you don’t need don’t call. You can call this fuction multiple times if you require. If you call this, call this before mysql_real_connect() and after mysql_init().5. Connect by calling mysql_real_connect()

6. Call the business logic and MySQL API’s

7. Close the MYSQL type variable.

mysqlwithc

 

Template of program

#include <mysql/mysql.h><return_type> <function_name>(parameters)

{

MYSQL mysql; mysql_init(&mysql);

mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);      

/*call only if required otherwise omit*/

mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,”Pushpraj”);        

 /*call only if required otherwise omit*/

mysql_real_connect(….);      

/* now call other API’s*/

mysql_close(&mysql);

}

 Compiling and Running in UNIX :- 

To Compile :-  $gcc mysql_app.c -o mysql_app -I/usr/local/include -L/usr/local/lib/mysql –lmysqlclient

To Run :-  $./mysql_app

Examples:-

1. Test the connection.

#include </usr/include/mysql/my_global.h>

#include </usr/include/mysql/mysql.h>

int main(int argc, char **argv)

{

printf(“MySQL client version: %s\n”, mysql_get_client_info());

  exit(0);

}

Description:-  “mysql_get_client_info()”, this mysql function collects the version information of mysql.

2. Create table and insert data into table.

#include <my_global.h>

#include <mysql.h>

void finish_with_error(MYSQL *con){

  fprintf(stderr, “%s\n”, mysql_error(con));

  mysql_close(con);

  exit(1);       

}

 

int main(int argc, char **argv)

{

  MYSQL *con = mysql_init(NULL);

  if (con == NULL) {

      fprintf(stderr, “%s\n”, mysql_error(con));

      exit(1);

  } 

if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)

  {

      finish_with_error(con);

  }   

 

  if (mysql_query(con, “DROP TABLE IF EXISTS Dell”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “CREATE TABLE Dell(Id INT, Name TEXT, Price INT)”)) {     

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(1,’vostro14′,26042)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(2,’inspiron15′,37000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(3,’inspiron16′,40000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(4,’ vostro15′,29000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(5,’express’,69000)”)) {

      finish_with_error(con);

  }

  mysql_close(con);

  exit(0);

}

Description:- mysql_init, allocates/intialises a Mysql object suitable for mysql_real_connect()establishes a connection to the database.We provide connection handler, host name, username ,password ,database name, port number , unix socket and client flag.

3. Create connection with Database & fetch the records.

#include <my_global.h>

#include <mysql.h>

void finish_with_error(MYSQL *con)

{

  fprintf(stderr, “%s\n”, mysql_error(con));

  mysql_close(con);

  exit(1);       

}

int main(int argc, char **argv)

{     

  MYSQL *con = mysql_init(NULL);

  if (con == NULL)

  {

      fprintf(stderr, “mysql_init() failed\n”);

      exit(1);

  } 

 

  if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)

  {      finish_with_error(con);  }  

 

  if (mysql_query(con, “SELECT * FROM Dell”))

  {      finish_with_error(con);  }  

  MYSQL_RES *result = mysql_store_result(con);

  if (result == NULL)

  {      finish_with_error(con);  }  

  int num_fields = mysql_num_fields(result);

  MYSQL_ROW row;

  while ((row = mysql_fetch_row(result)))

  {

      for(int i = 0; i < num_fields; i++)

      {

          printf(“%s “, row[i] ? row[i] : “NULL”);

      }

      printf(“\n”);

  }

  mysql_free_result(result);

  mysql_close(con);

  exit(0);

}

Description:-

  • Create a connection:: mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0)
  • Execute query:: mysql_query(con, “SELECT * FROM Dell”)
  • Get the result set:: MYSQL_RES *result = mysql_store_result(con)
  • Fetch all available rows one by one :: row = mysql_fetch_row(result)
  • Free the result set:: mysql_free_result(result)
  • Close the connection:: mysql_close(con);

Thanks

Pushpraj Kumar