Bug #17016 I find some problem in MySQL (bugs) - 'update' command in C++
Submitted: 1 Feb 2006 16:51 Modified: 9 Jan 2008 23:08
Reporter: Hristo Markov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[1 Feb 2006 16:51] Hristo Markov
Description:
Dear Sirs,
 
My name is Hristo Markov. I am programmer from Bulgaria / Europe /.
 
Last week I downloaded MySQL 5.0 and ODBC 3.51 and want to try how the programs are connect to the database.
 
I use Windows XP and Microsoft C++. 
 
I find some problems with “update” command and I think that it is the bugs in MySQL (or ODBC driver), because the same example program working with other databases.
 
I want to send new values on to the record and to make update.
 
The problem is that “update” command do updates on to the record, but the program receive error signal => "Query-based update failed because the row to update could not be found."
 
Below I apply simple source code of “C++” that demonstrate the problem.
 
I hope you will help me.
 
Sincerely,
 
Hristo Markov
 
 
 
/*example programm*/
#include "stdafx.h"
#include <comdef.h>
#include <stdio.h>
#include <objbase.h>
#include <odbcinst.h>
#include <direct.h>
#define INITGUID
#pragma warning (disable: 4146)
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" rename_namespace("ADOCG") rename("EOF", "EndOfFile")
using namespace ADOCG;
#include "icrsint.h"
 
void dump_com_error(_com_error &e)
{
     _bstr_t bstrSource(e.Source());
     _bstr_t bstrDescription(e.Description());
     char *str=(char *)malloc(200+strlen(e.ErrorMessage())+strlen((LPCSTR) bstrSource)+strlen((LPCSTR)bstrDescription));
     sprintf(str,"Error\n\a\tCode = %08lx\n\a\tCode meaning = %s\a\tSource = %s\n\a\tDescription = %s\n",e.Error(),e.ErrorMessage(),(LPCSTR) bstrSource,(LPCSTR) bstrDescription);
     MessageBox(NULL,str,"error",0);
     free(str);
}
int APIENTRY WinMain(HINSTANCE hInstance,HINSTANCE hPrevInstance,LPSTR lpCmdLine,int nCmdShow)
{
     _ConnectionPtr pconn;
     _CommandPtr pcmd;
     _RecordsetPtr prs;
     _variant_t vnull;
     vnull.vt=VT_ERROR;
     vnull.scode=DISP_E_PARAMNOTFOUND;
 
     int rc,ret=CoInitialize(NULL);
     if(ret!=S_OK) return 1;
 
     try{pconn.CreateInstance(__uuidof(Connection));}
     catch(_com_error &e){dump_com_error(e);exit(0);}
 
     try{rc=pconn->Open("myodbc","client","password",adOpenUnspecified);}
     catch(_com_error &e){dump_com_error(e);}
 
     try{rc=pcmd.CreateInstance(__uuidof(Command));}
     catch(_com_error &e){dump_com_error(e);}

     pcmd->ActiveConnection=pconn;
 
     try{rc=prs.CreateInstance(__uuidof(Recordset));}
     catch(_com_error &e){dump_com_error(e);}
 
     pcmd->CommandText="SELECT * FROM TABLE1";
     prs->PutRefSource(pcmd);
 
     try
     {
         prs->Open(vnull,vnull,adOpenStatic,adLockOptimistic,adCmdUnknown);
     }
     catch(_com_error &e){dump_com_error(e);}
 
     try
     {
         prs->MoveFirst();
     }
     catch(_com_error &e){dump_com_error(e);}
 
 /*****/
 //If you remove the next fragment, "update" will be ok.
 //"update" do updates on to the record, but the program receive error signal.
 
 //I can put other values on “vp”
 
     _variant_t vp=prs->Fields->GetItem("FIELD1")->Value;
     prs->Fields->GetItem("FIELD1")->Value=vp;
 
 /*****/
 
     try
     {
          prs->Update();
     }
     catch(_com_error &e)
     {
      dump_com_error(e);
     }
     prs->Close();
     prs->Release();
     prs.Detach();
    
     pcmd->Release();
     pcmd.Detach();
 
     rc=pconn->Close();
     rc=pconn->Release();
     pconn.Detach();
 
     CoUninitialize();
 
     return 0;
}

How to repeat:
E-mail me at:

hristomarkov@yahoo.com

or

hh1@abv.bg
[2 Feb 2006 14:13] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW CREATE TABLE TABLE1; statement from mysql client. Are there any data in that table?
[2 Feb 2006 15:07] Hristo Markov
Hi again,

The table from the example that I send to you, have not data. This is only to demonstrate the problem. 

The command ‘update‘do updates, but the program receives error code and the message is => “Query-based update failed because the row to update could not be found.”

The main idea for which I want to try MySQL is that we working with developed from us software technology for very fast producing of big software systems SCUS / System for Creation of Users Systems /, which base on “PROGRAMMING WITHOUT PROGRAMMERS” principle.

I want to see how SCUS will work with MySQL database.

If you are interesting about SCUS technology please visit our web site www.markovandmarkov.com

Greetings,

Hristo Markov
[3 Feb 2006 11:44] Hristo Markov
Before start the example program I executed only the next example commands

CREATE TABLE TABLE1 (FIELD1 varchar(1), FIELD2 varchar(2))
INSERT INTO TABLE1 (FIELD1,FIELD2) values ('1','2')
[7 Feb 2006 9:46] Valeriy Kravchuk
Thank you for the additional information. Please, try to add primary key to your sample table:

CREATE TABLE TABLE1 (ID int auto_increment PRIMARY KEY, FIELD1 varchar(1), FIELD2 varchar(2));
INSERT INTO TABLE1 (FIELD1,FIELD2) values ('1','2');

and repeat your test. Inform about the results.
[8 Feb 2006 9:01] Hristo Markov
I do the test. The problem stays the same.
[27 Feb 2006 16:28] Jorge del Conde
Thanks for your excellent bug report.  I was able to reproduce the behaviour you described using the attached code and create table statements
[26 Aug 2007 19:15] Kent Boortz
Manager trace log

Attachment: SQL.LOG (application/octet-stream, text), 298.42 KiB.

[9 Jan 2008 23:08] Jess Balint
I've tested this with Connector/ODBC 5.1 rev990 and I don't see any problem. I've tested both with and without primary key and the "Return matched rows instead of affected rows" option.