ERROR [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_RIG'. Cannot insert duplicate key in object 'dbo.DOROW'. The duplicate key value is [...] ****************** ASSERT FAILED: Assertion failed in C:\Users\GIRU1\ToolsBox\upp-2020.1\uppsrc\ODBC\ODBC.cpp, line 215 tlevel >= 0
bool InsertDoument(ValueMap * vm){ Sql query(my_mssql_db); query.ClearError(); query.Begin(); try{ query * Insert(My_mssql_table)(*vm); } catch(SqlExc) { ErrorOK(query.GetLastError()); } if(query.WasError()){ query.Rollback(); return false; } query.Commit(); return true; }
if (query.WasError())
Violation of PRIMARY KEY
upp-2020.1\uppsrc
sqlite3.Open(db) #ifdef _DEBUG sqlite3.SetTrace(); //logging queries #endif
// initialize transaction BEGIN TRAN; // transaction INSERT INTO sales VALUES ('7896', 'JR3435', 'Oct 28 1997', 25, 'Net 60', 'BU7832');
BEGIN TRY BEGIN TRANSACTION exec( @sqlHeader) COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK END CATCH
Honestly I think that you are approaching the problem in the wrong way. Found some kind of workaround can be a quick & dirty solution, but is not a long term solution. I've ton of sql queries towards a PostgreSQL rdbms in my application: never had a glitch. That it's clearly some kind of bug in the ODBC code.
I've found out that the problem arises only in debug mode. The problem is the ASSERT at line 200 and 215 in ODBC/ODC.cpp. I can live without having the capability to debug that part of code, although it's very handy when there are issues (a problem with that part of the code was the reason I turned on the debugger in the first place).
(mymssql_db is a private memeber of the class and its type is MSSQLSession). bool OpenMSSqlDB(){ String username = "sa"; String pwd = "mypassword"; String dbname = "MYDBNAME"; String server = "servername\\instancename"; String cs = "Driver={SQL Server};Server=" + server + ";UID=" + username + ";PWD=" + pwd + ";Database=" + dbname + ";"; if(!mymssql_db.Connect(cs)) { string err="Unable to connect to MSSql database due to the following error: " + mymssql_db.GetLastError().ToStd(); this->err_messages->push_back(err); return false; } mymssql_db.ThrowOnError(); mymssql_db.LogErrors(); mymssql_db.SetTrace(); return true; }
SQL = myDB;
Sql query(myDB);
#include <Core/Core.h> #include <plugin/sqlite3/Sqlite3.h> using namespace Upp; CONSOLE_APP_MAIN { Cout() << "Start" << EOL; Sqlite3Session myDB; // <<<<<<<<<<<<<< ADOPT TO YOUR MS_Sql_Server..... if(!myDB.Open(ConfigFile("DATA.db"))) { LOG("Can't create or open database file\n"); return; } #ifdef _DEBUG myDB.SetTrace(); #endif SQL = myDB; // ======================= initial data String s = "[{\"A\":\"valA1\", \"B\":\"valB1\"}, {\"A\":\"valA2\", \"B\":\"valB2\"}]"; ValueMap js=ParseJSON(s); // frankly speaking ValueArray should be perhaps, but it works for me... int jsz=js.GetCount(); // ======================= create tbl SQL.Execute("drop table IF EXISTS tbl;"); SQL.Execute("create table IF NOT EXISTS tbl (A STRING , B STRING, PRIMARY KEY (A));"); // ======================= insert into tbl Sql query(myDB); // (my_db) query.SetStatement("INSERT INTO tbl ( A, B) VALUES ( ?, ?) "); // initialize transaction query.Begin(); // loop to insert rows for(size_t i = 0; i < jsz; i++) { query.SetParam(0,~js[i]["A"]); query.SetParam(1,~js[i]["B"]); query.Execute(); } // error behavior if(query.WasError()){ Cout() << query.GetLastError(); query.Rollback(); return; // false; } //Save data & unlock db query.Commit(); Cout() << "Done" << EOL; // ======================= select to console_view if (query.Execute("SELECT * FROM tbl")) { Cout() << "Call succeeded" << EOL; for (int i = 0; i < query.GetColumnCount(); ++i) { Cout() << Format("%d: %s", i, query.GetColumnInfo(i).name) << EOL; } while (query.Fetch()) { Cout() << query[0] << " ; " << query[1] << EOL; } } else { Cout() << "Call failed" << EOL; Cout() << SQL.GetLastError() << EOL; } }
SQL logic error
Giorgio wrote on Fri, 18 December 2020 08:26I've found out that the problem arises only in debug mode. The problem is the ASSERT at line 200 and 215 in ODBC/ODC.cpp. I can live without having the capability to debug that part of code, although it's very handy when there are issues (a problem with that part of the code was the reason I turned on the debugger in the first place).
Usually ASSERT compiles to nothing in release, and to debug check under debug.
So "arises only in debug mode" means that you see the warning in debug mode, but in release the same condition, which would trigger assert, does nothing and continues further.
ASSERT usually ensures conditions which "must be true" (and there is no need to check it at runtime) and the code after them usually is written in such a way that it may often crash hard if the condition is false.
So you get assert warning only in debug mode, but most likely the real problem is happening in release too, it's just not catched early by ASSERT, but causes havoc further down the line. In some situations you may be lucky that the code after is robust enough to survive even when condition is false, and the assert is rather just information that something is seriously broken in the code, way beyond the author expectations.
mr_ped wrote on Fri, 18 December 2020 14:02Giorgio wrote on Fri, 18 December 2020 08:26I've found out that the problem arises only in debug mode. The problem is the ASSERT at line 200 and 215 in ODBC/ODC.cpp. I can live without having the capability to debug that part of code, although it's very handy when there are issues (a problem with that part of the code was the reason I turned on the debugger in the first place).
Usually ASSERT compiles to nothing in release, and to debug check under debug.
So "arises only in debug mode" means that you see the warning in debug mode, but in release the same condition, which would trigger assert, does nothing and continues further.
ASSERT usually ensures conditions which "must be true" (and there is no need to check it at runtime) and the code after them usually is written in such a way that it may often crash hard if the condition is false.
So you get assert warning only in debug mode, but most likely the real problem is happening in release too, it's just not catched early by ASSERT, but causes havoc further down the line. In some situations you may be lucky that the code after is robust enough to survive even when condition is false, and the assert is rather just information that something is seriously broken in the code, way beyond the author expectations.
I think I found the problem. In the ODBC there is a member called tmode of type "TransactionMode". TransactionMode can be NORMAL or IMPLICIT. There is also the related method SetTransactionMode to modify tmode. For some reason it was set to NORMAL and that caused the issue. Setting it to IMPLICT fixed the problem.
Unfortunately I did not found any reference to SetTransactionMode in the forum so the only way to understand the problem was inspecting the ODBC.h and ODBC.cpp code. The problem was specifically related to MSSQLSession, so nothing in the query itself was wrong and changing it would not have helped. I do not think that my code was "seriously broken in the code, way beyond the author expectations" and I do think that who wrote that piece of code could have easily given some hint about the origin of the problem.
#include "app.h" #include <Sql/sch_schema.h> #include <Sql/sch_source.h> using namespace Upp; CONSOLE_APP_MAIN { MSSQLSession mssql; if(!mssql.Connect("Driver={SQL Server Native Client 11.0};Server=localhost;Database=master;Trusted_Connection=Yes;")) { Cout() << "Connect failed: " << mssql.GetLastError() << '\n'; return; } SQL = mssql; SqlSchema sch(MSSQL); StdStatementExecutor se(SQL.GetSession()); All_Tables(sch); ODBCPerformScript(sch.Upgrade(), se); ODBCPerformScript(sch.Attributes(), se); #ifdef _DEBUG mssql.SetTrace(); mssql.LogErrors(); mssql.ThrowOnError(); #endif SQL.Begin(); try { for(int i = 0; i < 10; i++) SQL * Insert(TEST)(ID, i)(TEXT, String('A' + i, 1)); } catch(SqlExc e) { DDUMP(e); } if(SQL.WasError()) { DLOG("There was ERROR!"); SQL.Rollback(); } else SQL.Commit(); S_TEST tst; Sql sql; sql * Select(tst).From(TEST); while(sql.Fetch(tst)) Cout() << tst.ID << ", " << tst.TEXT << '\n'; }
One little issue: You are supposed to use Begin/End/Commit with your session, not query - that is deprecated.
if(my_mssql_db.IsOpen()){ my_mssql_db.Commit(); my_mssql_db.Close(); }
bool InsertDocument(ValueMap * header, vector<vm*> * rows){ Sql query_header(my_mssql_db); query_header.ClearError(); Sql query_body(my_mssql_db); query_body.ClearError(); query_header.Begin(); query_body.Begin(); try{ query_heder * Insert(My_mssql_header_table)(*header); } catch(SqlExc) { ErrorOK(query_header.GetLastError()); } for(vector<vm*>::iterator it = rows->begin(); it != rows->end(); ++it) { try{ query_body * Insert(My_mssql_body_table)(*it); } catch(SqlExc) { ErrorOK(query_body.GetLastError()); } } if(query_header.WasError() || query_body.WasError(){ query_body.Rollback(); query_header.Rollback(); return false; } query_header.Commit(); query_body.Commit(); return true; }
Hello,
I made a lot of tests in the last weeks and I still cannot wrap my head around this error.
To begn with, I had some issues with DDUMP: if I put that before the assert and I try to compile in release mode I got "error C2018: unknown character '0x40'". So I used RDUMP in release. In debug mode DDUMP is ok.
bool InsertDocument(ValueMap * header, vector<vm*> * rows){ Sql query_header(my_mssql_db); query_header.ClearError(); Sql query_body(my_mssql_db); query_body.ClearError(); query_header.Begin(); query_body.Begin();
Giorgio wrote on Wed, 27 January 2021 16:44Hello,
bool InsertDocument(ValueMap * header, vector<vm*> * rows){ Sql query_header(my_mssql_db); query_header.ClearError(); Sql query_body(my_mssql_db); query_body.ClearError(); query_header.Begin(); query_body.Begin();
This is really quite ugly code: Transaction is associated with SqlSession, so the second begin starts the second level.
Hi,
I created the test case here.
Regards,
gio