Page 1 of 1

ADO.Net transaction not working

Posted: Wed Sep 25, 2013 1:49 pm
by sushanth
i have a table called transtest which contain only two fields called id and name
i was trying to test transaction in vertica through
initially i have filled the table with 2 records like

id name
----- -----------
1 sushanth
2 jayesh


my test has two steps
1. first i will truncate the table transtest.
2. then i will insert one record to transtest table and this time i will provide a wrong syntax for insert into command
3. when the exception raises i will rollback the transaction.

please view the below code for testing the transaction

private void btnTransTest_Click(object sender, EventArgs e)
VerticaConnection _conn = new VerticaConnection(builder.ToString());
VerticaTransaction _vTrans = null;


_vTrans = _conn.BeginTransaction();

VerticaCommand objCommand = new VerticaCommand();
objCommand.Connection = _conn;
objCommand.Transaction = _vTrans;

objCommand.CommandText = "TRUNCATE TABLE transtest";


objCommand.CommandText ="Insert into transtest value (" + 1 + ","+"sumesh)";
objCommand.ExecuteNonQuery(); //error will raise at this point since the insert command is wrong

//after this rollback the table is truncated which should not happen. what is wrong here


transaction is not applied here, please guide me if iam doing any thing wrong.

Re: ADO.Net transaction not working

Posted: Wed Sep 25, 2013 2:02 pm
by id10t

May be it's copy/paste error, but what do you wanna insert in this line?

Code: Select all

objCommand.CommandText ="Insert into transtest value (" + 1 + ","+"sumesh)";

2. Expanding and substitution of values gives:

Code: Select all

"Insert into transtest values (  1  , sumesh)";
Vertica knows what is sumesh? Because after expand&substitute it isn't a string(string should be enclosed by single quotes) its identifier. If you want to insert sumesh as string it should look like follow (after expand&substitute):

Code: Select all

"Insert into transtest values (  1  , 'sumesh')"
3. You can't rollback TRUNCATE TABLE statement, just open a documentation:

Removes all storage associated with a table, while preserving the table definitions. TRUNCATE TABLE auto-commits the current transaction after statement execution and cannot be rolled back.
Do you know what is this SQL injection? Try to google it!

Re: ADO.Net transaction not working

Posted: Wed Sep 25, 2013 2:53 pm
by sushanth
i know
objCommand.CommandText ="Insert into transtest value (" + 1 + ","+"sumesh)";

is a wrong insert statement , but i have deliberately given this in order to check whether the transaction is rolled back.

but now after reading the documentation i came to know that "TRUNCATE" is auto commit and cannnot be rolled back.

how about delete ?
i need to clear millions of records.
my requirement is like the following.

1. first statement will be clearing the table
2. second statement will be inserting values in to the table.

by inserting values i mean using copy statement i want to insert values to table.

so during the copy command execution if any error occurs then i need to roll back the transaction.

consider the scenario
if my table consists of 1000 records and first i will be clearing the values in the table and later i will be inserting values using copy command.
some times the data will be same or there will be more data for insertion say 1500 records.
so during the course of copy command if there is any error occurs while copying or inserting i must not loose already deleted data from my table.

ie if any error occurs during inserting then i need to role back alll my 1000 records which i have already cleared.

As per the documentation if we use truncate command it is not rolled back. Suppose if i use delete command then i think it will be slow for millions of records.

please suggest a method inorder to do this scenario.


Re: ADO.Net transaction not working

Posted: Fri Mar 18, 2016 7:11 am
by nicolbiden
Check here to know more about....ADO.Net Transaction