Beginners Tutorial on Embedding SQL

In this post, I am going to share ideas on embedding SQL in any host Program. You can embed SQL in any program like Python, Java, C#, COBOL, PL/1 and so on.


How to Embed SQL in Python

Python supports my SQL database. In the Python code, you need to establish a connection by using import Connector.

MySQL Connector Establishes Connection.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)
print(mydb)

Any host-program, need host variables, that can be created in Host-program. When you get the data from SQL, you can pass to host-variables. So that you can use in your program.

How to Embed SQL in JAVA

Like Python, in JAVA also you need Connector and Host Variables.

public static void viewTable(Connection con, String dbName)
    throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, " +
                   "SALES, TOTAL " +
                   "from " + dbName + ".COFFEES";
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String coffeeName = rs.getString("COF_NAME");
            int supplierID = rs.getInt("SUP_ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
            System.out.println(coffeeName + "\t" + supplierID +
                "\t" + price + "\t" + sales +
                 "\t" + total);
        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

How to Close Connection in Java. You need to call the close method to close the connection.

} finally {
    if (stmt != null) { stmt.close(); }
}

How to Embed SQL in C#

SqlConnection myConnection = new SqlConnection("user id=username;" +
"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");

Once the connection is established you can put SQL queries to SQL Server. Then you need host-variable to handle the data.

Summary

  1. In any host program, you need to establish connection
  2. Create Host Variables
  3. Call SQL Query
  4. Close the connection

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.