One command causes another to fail

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
jkort
Posts: 5
Joined: Mon 09 Nov 2015 12:06

One command causes another to fail

Post by jkort » Mon 04 Apr 2016 10:31

Hi,

When I run 3 UniCommands and the second one fails, the third will also fail silently eventho its a correct command.

For example

UPDATE AANHEF SET CONDITIONAL = CONDITIONAL;
UPDATE DOES_NOT_EXIST SET CONDITIONAL = CONDITIONAL;
UPDATE AANHEF SET CONDITIONAL = CONDITIONAL;

The rows affected of the first statement is 8
The second statement will fail
The rows affected of the third statmeent (which is identical to the first) is -1

What does that mean?

If I run the statments a second time I get a different error:

prepared statement "PRSTMT2864046221016346609" does not exist

Which goes away when I turn off connection pooling

But I cant fix the fact that rows affect is -1 instead of 8 for the third statement

The code I use is:

Code: Select all

                using (UniConnection connection = new UniConnection(connectionString))
                {
                    connection.Open();
                    int max = lines.Count();
                    int percent = max / 100;
                    if (percent == 0)
                        percent = 1;
                    int i = 1;
                    int errorCount = 0;
                    try
                    {
                        foreach (string line in lines)
                        {
                            if (!string.IsNullOrEmpty(line))
                            {
                                using (UniCommand command = connection.CreateCommand())
                                {
                                    command.CommandText = line;
                                    try
                                    {
                                        command.Prepare();
                                        int modified = command.ExecuteNonQuery();
                                        if (modified > 0)
                                            Trace.TraceWarning(string.Format(@"{0}
  Records aangepast: {1}",
                                                line, modified));
                                    }
                                    catch (Exception ex)
                                    {
                                        Trace.TraceError(string.Format(@"{0}
  ERROR: {1}", line, ex.Message));
                                        errorCount++;
                                    }
                                }
                            }
                            if (worker != null && i % percent == 0)
                                worker.ReportProgress(i * 100 / max, string.Format("[{0}/{1}] {2}", i, max, line));
                            i++;
                        }
                        if (errorCount > 0)
                            throw new Exception(string.Format("Er zijn fouten opgetreden ({0})", errorCount));
                        if (worker != null)
                            worker.ReportProgress(100, "Script executed succesfully.");
                    }
                    catch (Exception ex)
                    {
                        if (worker != null)
                        {
                            worker.ReportProgress(0, string.Format("ERROR: {0}", ex.Message));
                        }
                    }
                }
Regards,

Jan

jkort
Posts: 5
Joined: Mon 09 Nov 2015 12:06

Re: One command causes another to fail

Post by jkort » Mon 04 Apr 2016 11:56

I did find a workaround, but am not too happy with it:

Code: Select all

                                    catch (Exception ex)
                                    {
                                        Trace.TraceError(string.Format(@"{0}
  ERROR: {1}", line, ex.Message));
                                        errorCount++;
                                        connection.Close();
                                        connection.Open();
                                    }
I do have a "validate connection=true" flag in the connectionstring, but that only works when I open the connection I guess?

So the only way to get things to proceed after an error is to reopen the connection? Or is there another way to revalidate the connection?

Provider=PostgreSQL;Unicode=true;database=los;Pooling=false;validate connection=true;Default FetchAll=false

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: One command causes another to fail

Post by Pinturiccio » Tue 05 Apr 2016 16:23

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

As a workaround, remove "Default FetchAll=false" from your connection string.
jkort wrote:Which goes away when I turn off connection pooling
When we studied, we found that the reason is in the "Default FetchAll=false" connection string parameter. The pooling does not affect the issue, whether you use it or not, but when you use pooling, your workaround with closing and opening a connection will work much faster.
jkort wrote:I do have a "validate connection=true" flag in the connectionstring, but that only works when I open the connection I guess?
If you use "Validate Connection=true;" each time when a connection is taken from the pool, it is checked for validity. If the connection is not valid, it is destroyed and a new one is created. Using this parameter with pooling disabled does not make sense.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: One command causes another to fail

Post by Pinturiccio » Wed 04 May 2016 12:44

We have fixed the bug when a valid command fails after an error in the previous command in case "Default FetchAll=false". We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: One command causes another to fail

Post by Pinturiccio » Fri 06 May 2016 08:02

New build of dotConnect for PostgreSQL 7.5.644 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=33633

jkort
Posts: 5
Joined: Mon 09 Nov 2015 12:06

Re: One command causes another to fail

Post by jkort » Wed 17 Aug 2016 14:17

Hi,

I installed the new devart, but I still get issues for the fetchAll.

I made a program that illustrates the issue it's a combination of FetchAll=false and INITIALLY DEFERRED and 3 tables with references.

The output with the 4 errors at the end is what I would expect (FetchAll=true)

The output with only 1 error is not what I would expect (FetchAll=false)

Regards,

Jan

P.S. When installing the new version of devart it keeps telling me there is an older version of devart in the GAC, even after removing it with gacutil and restarting my computer I still get the message (there was nothing starting with devart in windows/assembly). I clicked continue and it didnt complain after that, but not sure if this works.. is there a way to check the version via UniConnection for example to make sure I have the right version?

Code: Select all

        private void RadButton_Click(object sender, RoutedEventArgs e)
        {
            UniConnectionStringBuilder builder;

            try
            {
                string queries = @"
DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
DROP TABLE IF EXISTS T3;
CREATE TABLE T1 (ID INTEGER PRIMARY KEY, T2_ID INTEGER);
CREATE TABLE T2 (ID INTEGER PRIMARY KEY, T3_ID INTEGER);
CREATE TABLE T3 (ID INTEGER PRIMARY KEY);
ALTER TABLE T1 ADD CONSTRAINT t2_idc1 FOREIGN KEY (t2_id) REFERENCES t2(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE T2 ADD CONSTRAINT t3_idc1 FOREIGN KEY (t3_id) REFERENCES t3(id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO T3 (ID) VALUES (1);
INSERT INTO T2 (ID, T3_ID) VALUES (1, 1);
INSERT INTO T1 (ID, T2_ID) VALUES (1, 1);
DELETE FROM T3;
DELETE FROM T2;
DELETE FROM T2;
DELETE FROM T2;";
                LogText.AppendText(string.Format("Script:{1}{0}{1}{1}", queries, Environment.NewLine));

                LogText.AppendText(string.Format("FetchAll = false and INITIALLY DEFERRED{0}{0}", Environment.NewLine));
                //builder.Add("validate connection", true);
                builder = CreateBuilder();
                builder.Add("Default FetchAll", false);
                using (UniConnection connection = new UniConnection(builder.ConnectionString))
                {
                    connection.Open();
                    ExecuteScript(connection, queries);
                }
                LogText.AppendText(string.Format("{0}FetchAll = true and INITIALLY DEFERRED{0}{0}", Environment.NewLine));
                builder = CreateBuilder();
                builder.Add("Default FetchAll", true);
                using (UniConnection connection = new UniConnection(builder.ConnectionString))
                {
                    connection.Open();
                    ExecuteScript(connection, queries);
                }
            }
            catch (Exception ex)
            {
                LogText.AppendText(ex.Message);
            }
        }
        private void ExecuteScript(UniConnection connection, string script)
        {
            foreach (string line in script.Split(Environment.NewLine.ToArray()))
            {
                string sql = line.Trim();
                if (!string.IsNullOrEmpty(sql))
                    ExecuteNonQuery(connection, sql);
            }
        }
        private void ExecuteNonQuery(UniConnection connection, string sql)
        {
            try
            {
                    using (UniCommand command = connection.CreateCommand())
                    {
                        command.CommandText = sql;
                        int recordCount = command.ExecuteNonQuery();
                        LogText.AppendText(string.Format("OK    {0}   Changes: {1}{2}", sql, recordCount, Environment.NewLine));
                    }
            }
            catch (Exception ex)
            {
                LogText.AppendText(string.Format("ERROR {0}   {1}{2}", sql, ex.Message, Environment.NewLine));
            }
        }

Output

Code: Select all

Script:

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
DROP TABLE IF EXISTS T3;
CREATE TABLE T1 (ID INTEGER PRIMARY KEY, T2_ID INTEGER);
CREATE TABLE T2 (ID INTEGER PRIMARY KEY, T3_ID INTEGER);
CREATE TABLE T3 (ID INTEGER PRIMARY KEY);
ALTER TABLE T1 ADD CONSTRAINT t2_idc1 FOREIGN KEY (t2_id) REFERENCES t2(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE T2 ADD CONSTRAINT t3_idc1 FOREIGN KEY (t3_id) REFERENCES t3(id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO T3 (ID) VALUES (1);
INSERT INTO T2 (ID, T3_ID) VALUES (1, 1);
INSERT INTO T1 (ID, T2_ID) VALUES (1, 1);
DELETE FROM T3;
DELETE FROM T2;
DELETE FROM T2;
DELETE FROM T2;

FetchAll = false and INITIALLY DEFERRED

OK    DROP TABLE IF EXISTS T1;   Changes: -1
OK    DROP TABLE IF EXISTS T2;   Changes: -1
OK    DROP TABLE IF EXISTS T3;   Changes: -1
OK    CREATE TABLE T1 (ID INTEGER PRIMARY KEY, T2_ID INTEGER);   Changes: -1
OK    CREATE TABLE T2 (ID INTEGER PRIMARY KEY, T3_ID INTEGER);   Changes: -1
OK    CREATE TABLE T3 (ID INTEGER PRIMARY KEY);   Changes: -1
OK    ALTER TABLE T1 ADD CONSTRAINT t2_idc1 FOREIGN KEY (t2_id) REFERENCES t2(id) DEFERRABLE INITIALLY DEFERRED;   Changes: -1
OK    ALTER TABLE T2 ADD CONSTRAINT t3_idc1 FOREIGN KEY (t3_id) REFERENCES t3(id) DEFERRABLE INITIALLY DEFERRED;   Changes: -1
OK    INSERT INTO T3 (ID) VALUES (1);   Changes: 1
OK    INSERT INTO T2 (ID, T3_ID) VALUES (1, 1);   Changes: 1
OK    INSERT INTO T1 (ID, T2_ID) VALUES (1, 1);   Changes: 1
OK    DELETE FROM T3;   Changes: 1
OK    DELETE FROM T2;   Changes: -1
ERROR DELETE FROM T2;   update or delete on table "t2" violates foreign key constraint "t2_idc1" on table "t1"
OK    DELETE FROM T2;   Changes: -1

FetchAll = true and INITIALLY DEFERRED

OK    DROP TABLE IF EXISTS T1;   Changes: -1
OK    DROP TABLE IF EXISTS T2;   Changes: -1
OK    DROP TABLE IF EXISTS T3;   Changes: -1
OK    CREATE TABLE T1 (ID INTEGER PRIMARY KEY, T2_ID INTEGER);   Changes: -1
OK    CREATE TABLE T2 (ID INTEGER PRIMARY KEY, T3_ID INTEGER);   Changes: -1
OK    CREATE TABLE T3 (ID INTEGER PRIMARY KEY);   Changes: -1
OK    ALTER TABLE T1 ADD CONSTRAINT t2_idc1 FOREIGN KEY (t2_id) REFERENCES t2(id) DEFERRABLE INITIALLY DEFERRED;   Changes: -1
OK    ALTER TABLE T2 ADD CONSTRAINT t3_idc1 FOREIGN KEY (t3_id) REFERENCES t3(id) DEFERRABLE INITIALLY DEFERRED;   Changes: -1
OK    INSERT INTO T3 (ID) VALUES (1);   Changes: 1
OK    INSERT INTO T2 (ID, T3_ID) VALUES (1, 1);   Changes: 1
OK    INSERT INTO T1 (ID, T2_ID) VALUES (1, 1);   Changes: 1
ERROR DELETE FROM T3;   update or delete on table "t3" violates foreign key constraint "t3_idc1" on table "t2"
ERROR DELETE FROM T2;   update or delete on table "t2" violates foreign key constraint "t2_idc1" on table "t1"
ERROR DELETE FROM T2;   update or delete on table "t2" violates foreign key constraint "t2_idc1" on table "t1"
ERROR DELETE FROM T2;   update or delete on table "t2" violates foreign key constraint "t2_idc1" on table "t1"

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: One command causes another to fail

Post by Pinturiccio » Mon 22 Aug 2016 10:33

Thank you for the provided example. We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: One command causes another to fail

Post by Pinturiccio » Wed 31 Aug 2016 14:50

We have fixed the bug with processing errors in the "FetchAll=True" mode for PostgreSQL. We will post here when the corresponding build of dotConnect Universal is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: One command causes another to fail

Post by Pinturiccio » Mon 05 Sep 2016 09:23

New build of dotConnect Universal 3.60.1226 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/univer ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34209

Post Reply