Announcement

Collapse
No announcement yet.

SQL_STMT Not Counting Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL_STMT Not Counting Rows


    I Cant figure out whats happened here. My program has been working for years but now the SQL_STMT isn't counting rows.

    The following output (new_data has 1 row) returns 1 and zero

    If I replace with SQL_ResSetArray I get 1 and 1
    Code:
    ' SED_PBCC
    
    
    
    '----------------------------------------------------------------------------(')
    
    
    
    #INCLUDE "C:\SQLTOOLS\SQLT3.INC"        'All SQL Tools programs use this INC file.
    #LINK "\SQLTOOLS\SQLT3Pro.PBLIB"        'SQL Tools Pro, using PBLIB
    
    
    
    '----------------------------------------------------------------------------(')
    
    
    
    FUNCTION PBMAIN( ) AS LONG
        '
        LOCAL TL1 AS LONG
        LOCAL SQL_String AS STRING
        '
        TL1 = SQL_AUTHORIZE( %MY_SQLT_AUTHCODE )
        '
        IF TL1 <> %SQL_SUCCESS THEN
            '
            PRINT "SQL_AUTHORIZE Error :" + STR$( TL1 )
            '
            EXIT FUNCTION
            '
        END IF
        '
        TL1 = SQL_INITIALIZE( 1, 1, 100 )
        '
        IF TL1 <> %SQL_SUCCESS THEN
            '
            PRINT "SQL_INITIALIZE Error :" + STR$( TL1 )
            '
            EXIT FUNCTION
            '
        END IF
        '
        CALL SQL_OPENDB( "Driver={MySQL ODBC 5.3 ANSI Driver};Server=XXXX;Database=XXXX;User=XXXX;Password=XXXX;Option=3;" )
        '
        IF SQL_ERRORPENDING <> %FALSE AND SQL_ERRORNUMBER THEN
            '
            PRINT "SQL Error : OpenDB" + $CRLF + _
              "[SQL_ERRORPENDING=" + FORMAT$( SQL_ERRORPENDING ) + "]" + $CRLF + _
              "[SQL_ErrorFuncName=" + SQL_ERRORFUNCNAME + "]" + $CRLF + _
              "[SQL_ERRORNUMBER=" + FORMAT$( SQL_ERRORNUMBER ) + "]" + $CRLF + _
              "[SQL_ErrorText=" + SQL_ERRORTEXT + "]" + $CRLF + _
              "[SQL_ErrorTime=" + SQL_ERRORTIME + "]" + $CRLF + _
              "[SQL_ERRORQUICKALL=" + SQL_ERRORQUICKALL + "]"
            '
            EXIT FUNCTION
            '
        END IF
        '
        CALL SQL_STMT( %SQL_STMT_IMMEDIATE, "SELECT COUNT(*)FROM new_data" )
        '
        IF SQL_ERRORPENDING = %TRUE THEN
            '
            PRINT SQL_ERRORQUICKALL
            '
            EXIT FUNCTION
            '
        END IF
        '
        PRINT
        PRINT SQL_RESCOLCOUNT
        PRINT SQL_ResColString( 1 )
        '
        WAITKEY$
        '
    END FUNCTION
    Code:
    CREATE TABLE `new_data` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `company` int(4) DEFAULT NULL,
      `data_type` int(2) DEFAULT NULL,
      `filespec` text,
      `filespec_rename` text,
      `data_blob` mediumblob,
      `tid` char(40) DEFAULT NULL,
      `created` bigint(20) DEFAULT NULL,
      `complete` tinyint(1) DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CHECKSUM=1;

  • #2
    Forgot to perform a fetch. Dohh

    Comment


    • #3
      I always use the long form of the calls, so don't remember the short version of SQL_ResultRowCount(). This function returns the number of row affected by your last query. With most of the ODBC drivers I've used in the past few years (MS/SQL, MySQL, Sybase, Orable), this will also tell you how many rows came back from a SELECT query, without first doing a SQL_FetchResult() then testing SQL_EndOFData().
      Real programmers use a magnetized needle and a steady hand

      Comment

      Working...
      X