How to display the output from the SQL

Archive
,
1 
package fm_db

import java.sql.DriverManager

import java.sql.ResultSet

import java.sql.Statement

import org.stringtemplate.v4.compiler.CodeGenerator.region_return

import com.kms.katalon.core.annotation.Keyword

import com.mysql.jdbc.Connection

public class fm_db {

private static Connection connection = null;

/**

 * Open and return a connection to database

 * @param dataFile absolute file path

 * @return an instance of java.sql.Connection

 */

//Establishing a connection to the DataBase

@Keyword

def connectDB(String Link, String dbName, String PortNumber, String UserName, String Password){

//Load driver class for your specific database type

String conn = "jdbc:mysql://" + Link + ":" + PortNumber + "/" + dbName

//Class.forName("org.sqlite.JDBC")

//String connectionString = "jdbc:sqlite:" + dataFile

if(connection != null && !connection.isClosed()){

connection.close()

}

connection = DriverManager.getConnection(conn, UserName, Password)

return connection

}

/**

 * execute a SQL query on database

 * @param queryString SQL query string

 * @return a reference to returned data collection, an instance of java.sql.ResultSet

 */

//Executing the constructed Query and Saving results in resultset

@Keyword

def executeQuery(String queryString) {

Statement stm = connection.createStatement()

ResultSet rs = stm.executeQuery(queryString)

**println rs**

return rs

}

//Closing the connection

@Keyword

def closeDatabaseConnection() {

if(connection != null && !connection.isClosed()){

connection.close()

}

connection = null

}

/**

 * Execute non-query (usually INSERT/UPDATE/DELETE/COUNT/SUM...) on database

 * @param queryString a SQL statement

 * @return single value result of SQL statement

 */

@Keyword

 

def execute(String queryString) {

 

Statement stm = connection.createStatement()

 

boolean result = stm.execute(queryString)

 

return result

}

}
2

hello,
ok, so …
result set is not an actual data bearer … you can imagine it as an pointer that point BEFORE 1st row of result
you need to have cycle to access data something like:

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);
        }

example is taken from https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

1 Like
3

Hi Andrej,

Thanks for the quick response.

I tired with the below code, still it seems not to display,

public static void executeQuery(String queryString)
throws SQLException {

Statement stmt = null;
String query = "**************"
    try {
    stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
        int sql_result = rs.getint(1);
        System.out.println(sql_result);
    }
} catch (SQLException e ) {
    JDBCTutorialUtilities.printSQLException(e);
} finally {
    if (stmt != null) { stmt.close(); }
}

}

4

what you expect from
String query = "**************"
statement?
what it should return?
try at least

SELECT 1 FROM DUAL
5

why don’t you simply use the groovy way instead of java resultset?

http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html

see also:
http://groovy-lang.org/databases.html#_reading_rows

1 Like
6

Thank you, i got it

7

System.out.println : where can i see the result of the Query ?

8

where is result is displayed ?

9

in the console

1 Like