Bug #1150 No Rows Updated/Deleted
Submitted: 27 Aug 2003 7:34 Modified: 30 May 2013 8:16
Reporter: Miches Bianan Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 OS:Microsoft Windows (Win 2000)
Assigned to: CPU Architecture:Any

[27 Aug 2003 7:34] Miches Bianan
Description:
Hi Guys,

MysqlServer: 4.0.12 on Linux
MyODBC : 3.5.1
Visual C 6.0 on Win2k

My first time here since I cannot find a fix around.

My problem goes like this :
    
   1. SELECT books for conversion from database
   2. Do necessary conversion
   3. UPDATE row, (i tested both Positioned update and SQLSetPOS) 
         it only works on the first row

   It says, "no rows updated/deleted".

Hope you can help me on this.

Am attaching my schema and sample program for this.

Thanks,

Miches:)

How to repeat:
My problem goes like this :
    
   1. SELECT books for conversion from database
   2. Do necessary conversion
   3. UPDATE row, (i tested both Positioned update and SQLSetPOS) 
         it only works on the first row

   It says, "no rows updated/deleted".
[27 Aug 2003 7:36] Miches Bianan
Here's the schema

CREATE TABLE `BOOKS` (
  `BOOK_ID` int(11) NOT NULL auto_increment,
  `BOOK_TITLE` varchar(255) NOT NULL default '',
  `BOOK_AUTHOR` varchar(128) default NULL,
  `BOOK_AUTHOR_ALIAS` text,
  `BOOK_PRICE` float(8,2) NOT NULL default '0.00',
  `BOOK_NOTES` text,
  `BOOK_SYNOPSIS` text,
  `BOOK_CATEGORY` varchar(32) default NULL,
  `BOOK_LANGUAGE` text,
  `BOOK_COPYRIGHT` char(1) default NULL,
  `BOOK_FILE_ENTRIES` text NOT NULL,
  `BOOK_COUNT` bigint(20) NOT NULL default '0',
  `BOOK_VIEW_COUNT` bigint(20) default '0',
  `BOOK_FREE` char(1) NOT NULL default 'N',
  `BOOK_FEATURE` int(1) NOT NULL default '0',
  `BOOK_RAW_FILE` text,
  `BOOK_PICTURE_FILE` text,
  `BOOK_PICTURE_FILE_INT` text NOT NULL,
  `BOOK_PICTURE_FILE_PNG` text NOT NULL,
  `BOOK_GUTINDEX` text,
  `BOOK_STATUS` char(1) NOT NULL default 'N',
  `BOOK_ACTIVE` char(1) default 'N',
  `BOOK_ACTIVE_DATE` datetime default NULL,
  `BOOK_CONVERT` char(1) NOT NULL default 'Y',
  `BOOK_JONE` int(1) NOT NULL default '0',
  `BOOK_ENTRY_USER` varchar(32) default NULL,
  `BOOK_ENTRY_IP` varchar(16) default NULL,
  `BOOK_ENTRY_DATE` datetime default NULL,
  `BOOK_UPDATE_USER` varchar(32) default NULL,
  `BOOK_UPDATE_IP` varchar(16) default NULL,
  `BOOK_UPDATE_DATE` datetime default NULL,
  `BOOK_SYNC_USER` varchar(32) default NULL,
  `BOOK_SYNC_IP` varchar(16) default NULL,
  `BOOK_SYNC_DATE` datetime default NULL,
  PRIMARY KEY  (`BOOK_ID`)
) TYPE=MyISAM AUTO_INCREMENT=648 ;
[27 Aug 2003 7:38] Miches Bianan
Here's the Code:

int convert(SQLHDBC hdbc, SQLHSTMT hstmt) { 

	SQLRETURN   rc;
	SQLUINTEGER nRowCount=0, pcColDef;
	SQLCHAR     szColName[120],szData[MAX_COLUMNS][MAX_ROW_DATA_LEN]={0};
	SQLSMALLINT nIndex,ncol, pcbScale, pfNullable, pfSqlType;
	SQLHSTMT hstmtUPDA, hstmtSEL;
	ENV_ env_ = _env(0);
	char COMMAND[_CMD_LINE_SIZE_], *ptr;
	
	FILE *fp;

    /* create new statement handle */
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtUPDA);
    mycon(hdbc, rc);

    /* create new statement handle */
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtSEL);
    mycon(hdbc, rc);

    rc = SQLSetStmtAttr(hstmtSEL, SQL_ATTR_CURSOR_TYPE,
                        (SQLPOINTER)SQL_CURSOR_DYNAMIC,0);
    mystmt(hstmt, rc);

    /* set the cursor name as 'mysqlcur' on hstmt */
    rc = SQLSetCursorName(hstmtSEL, "mysqlcur", SQL_NTS);
    mystmt(hstmt, rc);
	

	rc = SQLExecDirect(hstmtSEL,env_->DBSQL,SQL_NTS);
	mystmt(hstmt,rc);
	
	/* get total number of columns from the resultset */
	rc = SQLNumResultCols(hstmtSEL,&ncol);
	mystmt(hstmt,rc);

	/* do the row bind */
	for(nIndex = 1; nIndex <= ncol; nIndex++)     {
		rc = SQLDescribeCol(hstmtSEL,nIndex,szColName, MAX_NAME_LEN+1, NULL,
						  &pfSqlType,&pcColDef,&pcbScale,&pfNullable);
		mystmt(hstmt,rc);
		rc = SQLBindCol(hstmtSEL,nIndex, SQL_CHAR, szData[nIndex-1],
					  MAX_ROW_DATA_LEN-1,NULL);
		mystmt(hstmt,rc);
	}

	for (rc=SQLFetchScroll(hstmtSEL, SQL_FETCH_NEXT, 0);  (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO); rc = SQLFetchScroll(hstmtSEL, SQL_FETCH_NEXT, 0)) { 

		char szFile[_FILENAME_SIZE_];

		printf("\n\n%4d. ", ++nRowCount);
		printf("%s",szData[M_TIT_IND]);

		snprintf(szFile, 120, "%s", szData[M_TIT_IND]);	
		szFile[120] ='\x0';
		snprintf(env_->TITLE, _FILENAME_SIZE_, "%s", szData[M_TIT_IND]);	
		snprintf(env_->XMLFILE, _FILENAME_SIZE_, "%s%s", env_->SRCDIR, szData[M_XML_IND]);

		// Step 0. Check Existence of Files
		//    Check XML and Image Files

		if (!(fp = fopen(env_->XMLFILE, "r"))) {
			snprintf(COMMAND, _CMD_LINE_SIZE_, "XML: %s -- not found", env_->XMLFILE);
			logger(3, COMMAND);
			continue;
		}
		fclose(fp);

		snprintf(env_->IMGFILE, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_PIC_IND]); 
		if (!(fp = fopen(env_->IMGFILE, "r"))) {
			snprintf(COMMAND, _CMD_LINE_SIZE_, "IMG: %s -- not found", env_->IMGFILE);
			logger(3, COMMAND);
			continue;
		}
		fclose(fp);

		snprintf(env_->IMGFILE, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_INT_IND]); 

		if (!(fp = fopen(env_->IMGFILE, "r"))) {
			snprintf(COMMAND, _CMD_LINE_SIZE_, "IMG: %s -- not found",env_->IMGFILE);
			logger(3, COMMAND);
			continue;
		}
		fclose(fp);

		snprintf(env_->IMGFILE, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_PNG_IND]); 
		
		if (!(fp = fopen(env_->IMGFILE, "r"))) {
			snprintf(COMMAND, _CMD_LINE_SIZE_, "IMG: %s -- not found", env_->IMGFILE);
			logger(3, COMMAND);
			continue;
		}
		
		// Step 1. Check Validity of XML
		//    No, write report, continue loop
		//   Yes, Start converting 

		snprintf(env_->TMPFILE, _FILENAME_SIZE_, "file:///c:/ebookpub/source/%s", szData[M_XML_IND]);

		if (!isXmlValid2(env_->TMPFILE, M_DTD_FILE)) {
			snprintf(COMMAND, _CMD_LINE_SIZE_, "%s -- Invalid/Does not conform to DTD", env_->XMLFILE);
			logger(3, COMMAND);
			continue;
		}
		
		// Step 2. Initiate Writing Sequence

		//    2.1. Prepare DOC Writer, write report

		snprintf(env_->TMPFILE, _FILENAME_SIZE_, "%s%s.txt", M_TMP_DIR, _DUMMY_FILE_);

		if (strstr(szFile, ", An")) {
			m_str_replace(szFile, ", An", "", _FILENAME_SIZE_);
			snprintf(env_->TRGFILE, _FILENAME_SIZE_, "An_%s", szFile);
		}
		else if (strstr(szFile, ", A")) {
			m_str_replace(szFile, ", A", "",_FILENAME_SIZE_);
			snprintf(env_->TRGFILE, _FILENAME_SIZE_, "A_%s", szFile);
		}
		else if (strstr(szFile, ", The")) {
			m_str_replace(szFile, ", The", "",_FILENAME_SIZE_);
			snprintf(env_->TRGFILE, _FILENAME_SIZE_, "The_%s", szFile);
		}
		else {
			strcpy(env_->TRGFILE, szFile);
		}

		printf("\n\t --> Writing into DOC format");

		snprintf(szFile, _FILENAME_SIZE_, "%s_%s", env_->TRGFILE, _DOC_EXT_);
		
		correct_filename(szFile);
		snprintf(env_->TRGFILE, _FILENAME_SIZE_, "%s%s.%s", env_->RAWDSTDIR, szFile, _PDB_EXT_);

		XML_to_TXT(env_->XMLFILE, env_->TMPFILE);
		doc_txt2pdb(env_->TMPFILE, env_->TRGFILE, szFile);

		//    2.2. Prepare Isilo Writer, write report

		printf("\n\t --> Writing into iSilo format");
		snprintf(env_->TRGFILE, _FILENAME_SIZE_, "%s%s.%s", env_->RAWDSTDIR, m_str_replace(szFile,_DOC_EXT_,_ISILO_EXT_, _FILENAME_SIZE_), _PDB_EXT_);
		m_str_replace(env_->TMPFILE, ".txt" , ".html", _FILENAME_SIZE_);
		snprintf(env_->IMGFILE, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_INT_IND]);

		ISILO_XML_to_HTML(env_->XMLFILE,env_->TMPFILE,env_->IMGFILE);
		isilo_ixl(env_->TMPFILE,env_->TRGFILE,szFile, env_->IMGFILE);
		isilo_txt2pdb(_ISILO_CONF_FILE_);
		
		//    2.3. Prepare Peanut Writer, write report

		ptr = szFile;

		while (ptr = (char*) strstr(ptr, "'")) {
			ptr= m_str_replace(ptr, "'", "\\\'",  _FILENAME_SIZE_);
			ptr +=3;

		}

		printf("\n\t --> Writing into PalmReader format");

		snprintf(COMMAND, _CMD_LINE_SIZE_, "copy %s %s%s > 1.txt", env_->IMGFILE, M_PNT_IMG_DIR, szData[M_PNG_IND]);
		EXEC_(COMMAND);
		m_str_replace(env_->TMPFILE, ".html" , ".txt", _FILENAME_SIZE_);
		PEANUT_XML_to_PMF(env_->XMLFILE, env_->TMPFILE, szData[M_PNG_IND]);
		snprintf(env_->TRGFILE, _FILENAME_SIZE_, "%s%s.%s", env_->RAWDSTDIR, m_str_replace(szFile, _ISILO_EXT_, _PEANUT_EXT_, _FILENAME_SIZE_), _PDB_EXT_);
		peanut_txt2pdb(env_->TMPFILE, env_->TRGFILE, m_str_replace(szFile, _ISILO_EXT_, _PEANUT_EXT_, _FILENAME_SIZE_));

		//    2.4. Prepare PDF Writer, write report

		ptr = szFile;
		while (ptr= (char*) strstr(ptr, "\\\'")) {
			ptr = m_str_replace(szFile, "\\\'", "'",  _FILENAME_SIZE_);
			ptr +=3;

		}
		printf("\n\t --> Writing into PDF format");
	
		sprintf(env_->IMGFILE, "%s%s", M_IMG_DIR, szData[M_PIC_IND]);
		snprintf(env_->TRGFILE, _FILENAME_SIZE_, "%s%s.%s", env_->RAWDSTDIR, m_str_replace(szFile,_PEANUT_EXT_, _PDF_EXT_, _FILENAME_SIZE_), _PDF_EXT_);

		PDF_XML_to_PDF(env_->XMLFILE, env_->TRGFILE, env_->IMGFILE);

		//    2.5. Prepare OEB Writer, write report 
		printf("\n\t --> Writing into Microsoft Reader format");
		snprintf(env_->LITIMGS.STD_COVER, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_PIC_IND]);
		snprintf(env_->LITIMGS.STD_THUMB, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_PIC_IND]);
		snprintf(env_->LITIMGS.PPC_COVER, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_PIC_IND]);
		snprintf(env_->LITIMGS.PPC_THUMB, _FILENAME_SIZE_, "%s%s", M_IMG_DIR, szData[M_PIC_IND]);
		
		m_str_replace(szFile, _PDF_EXT_, _LIT_EXT_, _FILENAME_SIZE_);

		snprintf(env_->TMPFILE, _FILENAME_SIZE_, "%s%s.%s", M_TMP_DIR, szFile, _OEB_OPF_EXT_);
		snprintf(env_->DUMFILE, _FILENAME_SIZE_, "%s%s.%s", M_TMP_DIR, szFile, _OEB_HTML_EXT_);
		snprintf(env_->TOCFILE, _FILENAME_SIZE_, "%sTOC_%s.%s", M_TMP_DIR, szFile, _OEB_HTML_EXT_);
		snprintf(env_->TRGFILE, _FILENAME_SIZE_, "%s%s.%s", env_->RAWDSTDIR, m_str_replace(szFile,_PDF_EXT_, _LIT_EXT_, _FILENAME_SIZE_), _LIT_EXT_);

		OEB_XML_to_HTMLOPF(env_->XMLFILE,env_->DUMFILE, env_->TMPFILE,env_->TOCFILE,&(env_->LITIMGS));
		OEB_OPF_to_LIT(env_->TMPFILE, env_->TRGFILE);

		// Step 3. Check if there no errors
		//   No, Do Single and Bundle Zips
		//		- Update Books set (Filename, PRODID, 2.00) (CONVERT='N')

		m_str_replace(szFile, "_LIT", "", _FILENAME_SIZE_);
		sprintf(COMMAND, "%s %s%s_%s.zip %s%s_%s.%s %s > 1.txt", M_ZIP_EXE, env_->SINDSTDIR, szFile, _DOC_EXT_, env_->RAWDSTDIR, szFile, _DOC_EXT_, _PDB_EXT_, _README_FILE_SIN_);
		EXEC_(COMMAND);
		sprintf(COMMAND, "%s %s%s_%s.zip %s%s_%s.%s %s > 1.txt", M_ZIP_EXE, env_->SINDSTDIR, szFile, _ISILO_EXT_, env_->RAWDSTDIR, szFile, _ISILO_EXT_, _PDB_EXT_, _README_FILE_SIN_);
		EXEC_(COMMAND);
		sprintf(COMMAND, "%s %s%s_%s.zip %s%s_%s.%s %s > 1.txt", M_ZIP_EXE, env_->SINDSTDIR, szFile, _PEANUT_EXT_, env_->RAWDSTDIR, szFile, _PEANUT_EXT_, _PDB_EXT_, _README_FILE_SIN_);
		EXEC_(COMMAND);
		sprintf(COMMAND, "%s %s%s_%s.zip %s%s_%s.%s %s > 1.txt", M_ZIP_EXE, env_->SINDSTDIR, szFile, _PDF_EXT_, env_->RAWDSTDIR, szFile, _PDF_EXT_, _PDF_EXT_, _README_FILE_SIN_);
		EXEC_(COMMAND);
		sprintf(COMMAND, "%s %s%s_%s.zip %s%s_%s.%s %s > 1.txt", M_ZIP_EXE, env_->SINDSTDIR, szFile, _LIT_EXT_, env_->RAWDSTDIR, szFile, _LIT_EXT_, _LIT_EXT_, _README_FILE_SIN_);
		EXEC_(COMMAND);
		sprintf(COMMAND, "%s %s%s.zip %s%s* %s > 1.txt", M_ZIP_EXE, env_->BUNDSTDIR, szFile, env_->RAWDSTDIR, szFile, _README_FILE_MUL_);
		EXEC_(COMMAND);

		logger(2, " published, zipped.");
	
		env_->reports._tok +=1;

		/* now update the fields using positioned cursor */
		snprintf(COMMAND, _CMD_LINE_SIZE_, "update BOOKS set BOOK_FILE_ENTRIES='%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;',BOOK_CONVERT='N' WHERE CURRENT OF mysqlcur",\
			szFile, _DOC_EXT_,\
			szFile, _ISILO_EXT_,\
			szFile, _PEANUT_EXT_,\
			szFile, _PDF_EXT_,\
			szFile, _LIT_EXT_);
		
		/*snprintf(szData[M_FIL_IND], _CMD_LINE_SIZE_, "%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;%s_%s.zip PRODID 2.00;",\
			szFile, _DOC_EXT_,\
			szFile, _ISILO_EXT_,\
			szFile, _PEANUT_EXT_,\
			szFile, _PDF_EXT_,\
			szFile, _LIT_EXT_);
		*/
		//SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
		rc = SQLExecDirect(hstmtUPDA, COMMAND, SQL_NTS);
		mystmt(hstmtUPDA, rc);

		rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); 
		mycon(hdbc,rc);

		// Step 4. Loop
     } 

	 /* commit the transaction */
     rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); 
     mycon(hdbc,rc);

	 SQLFreeStmt(hstmt,SQL_UNBIND);
  
     /* free the statement row bind resources */
     rc = SQLFreeStmt(hstmtSEL, SQL_UNBIND);
     mystmt(hstmt,rc);

	 /* housekeeping functions */
	 // Step 1. clear tmp folder

	 /* free the statement cursor */

     rc = SQLFreeStmt(hstmtSEL, SQL_CLOSE);
     mystmt(hstmt,rc);

	 return 1;
}
[7 Oct 2003 0:31] Venu Anuganti
Hi !!

I am not able to reproduce this using the sample you provided. Could you please send me the complete sample, so that it can used directly by changing the DSN name.

It will be even great if you can build one using the testlib provided from 3.51 BitKeeper tree under 'test' directory, so that we can add it directly into regression tests. You can find lot of tests for this positioned cursors using WHERE CURRENT OF CURSOR or SQLSetPos from mytest3.c file.

Also, does this happen with SQLSetPos as well as with CURRENT OF CURSOR syntax ?

Thanks
Venu
[30 May 2013 8:16] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.