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
@@Rowcount
Moderator: NorbertKrupa
Re: @@Rowcount
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
>> 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
Re: @@Rowcount
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.
The example prints the following out to the system console when run (assuming that the customers.txt file contained two million valid rows):
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());
}
}
}
Code: Select all
Number of accepted rows = 2000000
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)
DBA Specialist (Vertica/Oracle)
Re: @@Rowcount
Hi,
I know its pretty old thread but...
How can I get number of accepted rows while performing COPY from HDFS via hdfs:// protocol?
I know its pretty old thread but...
How can I get number of accepted rows while performing COPY from HDFS via hdfs:// protocol?
Re: @@Rowcount
Hi!
2. type of HDFS connector?
@micha0341. Vertica version?
2. type of HDFS connector?
Re: @@Rowcount
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;
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;
Re: @@Rowcount
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';");
...
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';");
...