| 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: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51 | OS: | Windows (Win 2000) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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".