@@Rowcount

Moderator: NorbertKrupa

rajesh.pu
Newbie
Newbie
Posts: 4
Joined: Mon Jul 15, 2013 5:53 am

@@Rowcount

Post by rajesh.pu » Tue Jul 23, 2013 11:30 am

Hello,

do we have a similar function for @@rowcount in vertica?

i've a statement in my sql server query " WHILE @@ROWCOUNT > 1". i would like to perform similar action in vertica aswell

Kindly suggest

Rgds
Rajesh

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: @@Rowcount

Post by id10t » Tue Jul 23, 2013 1:21 pm

Hi!

>> do we have a similar function for @@rowcount in vertica?
No, Vertica has no.

>> i would like to perform similar action in vertica aswell
programmatically only

User avatar
BoMBaY
Beginner
Beginner
Posts: 26
Joined: Tue Jul 16, 2013 5:45 am

Re: @@Rowcount

Post by BoMBaY » Thu Jul 25, 2013 6:20 am

As I know @@ROWCOUNT is a Built-in System Function (Transact-SQL) of SQL Server that returns the number of rows affected by the last statement. And Vertica currently doesn't support programming language in Database like PL/SQL (Oracle) or Transact-SQL (SQL Server).
So If you want to know the number of rows affected by the last statement. It will depend on a language that you use to write you program.

The following example demonstrates using Java with the JDBC to load a file name customers.txt into a new database table. then use getUpdateCount() to get the number of rows affected by the COPY statement.

Code: Select all

import java.sql.*;
import java.util.Properties;
import com.vertica.jdbc.*;

public class COPYFromFile {
    public static void main(String[] args) {
        try {
            Class.forName("com.vertica.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("Could not find the JDBC driver class.");
            e.printStackTrace();
            return;
        }
        Properties myProp = new Properties();
        myProp.put("user", "ExampleAdmin"); // Must be superuser
        myProp.put("password", "password123");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",myProp);
            // Disable AutoCommit
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            // Create a table to hold data.
            stmt.execute("DROP TABLE IF EXISTS customers;");
            stmt.execute("CREATE TABLE IF NOT EXISTS customers (Last_Name char(50) "
                            + "NOT NULL, First_Name char(50),Email char(50), "
                            + "Phone_Number char(15))");

            
            // Use the COPY command to load data. Load directly into ROS, since
            // this load could be over 100MB. Use ENFORCELENGTH to reject
            // strings too wide for their columns.
            boolean result = stmt.execute("COPY customers FROM "
                            + " '/data/customers.txt' DIRECT ENFORCELENGTH");
            
            // Determine if execution returned a count value, or a full result
            // set. 
            if (result) {
                System.out.println("Got result set");
            } else {
                // Count will usually return the count of rows inserted.
                System.out.println("Got count");
                int rowCount = stmt.getUpdateCount();
                System.out.println("Number of accepted rows = " + rowCount);
            }
            
            
            // Commit the data load
            conn.commit();
        } catch (SQLException e) {
            System.out.print("Error: ");
            System.out.println(e.toString());
        }
    }
}
The example prints the following out to the system console when run (assuming that the customers.txt file contained two million valid rows):

Code: Select all

Number of accepted rows = 2000000
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)

Image
ImageImage

micha034
Newbie
Newbie
Posts: 5
Joined: Mon Feb 12, 2018 5:20 pm

Re: @@Rowcount

Post by micha034 » Mon Feb 12, 2018 5:22 pm

Hi,

I know its pretty old thread but...
How can I get number of accepted rows while performing COPY from HDFS via hdfs:// protocol?

sKwa
Newbie
Newbie
Posts: 15
Joined: Wed Aug 02, 2017 3:12 pm

Re: @@Rowcount

Post by sKwa » Mon Feb 12, 2018 9:21 pm

Hi!
@micha034
1. Vertica version?
2. type of HDFS connector?

micha034
Newbie
Newbie
Posts: 5
Joined: Mon Feb 12, 2018 5:20 pm

Re: @@Rowcount

Post by micha034 » Mon Feb 12, 2018 10:44 pm

Thanks for quick reply.
Vertica version 8.1.1
Using JDBC.

Tried following but returns 0 although data is loaded:
int numAccepted1 = copyStatement.executeUpdate();
int numAccepted2 = copyStatement.getUpdateCount();


Performing COPY command from JDBC like following:
COPY tests.table
FROM 'hdfs://hdfs-nn/path/to/data/*'
DELIMITER E'\t' NULL AS 'null' REJECTED DATA AS TABLE loader.rejected_5JE8wAoWyX DIRECT;

sKwa
Newbie
Newbie
Posts: 15
Joined: Wed Aug 02, 2017 3:12 pm

Re: @@Rowcount

Post by sKwa » Tue Feb 13, 2018 12:18 am

Hi!

1. do COPY with no COMMIT
2. with same cursor/statement run "select get_num_accepted_rows() as 'ACCEPTED', get_num_rejected_rows() as 'REJECTED';"
3. observe results and commit or rollback


Statement stmt = conn.createStatement();
stmt.execute("COPY ... FROM ...");
rs = stmt.executeQuery("select get_num_accepted_rows() as 'ACCEPTED', get_num_rejected_rows() as 'REJECTED';");
...

Post Reply

Return to “Vertica SQL”