使用随机sql命令时查询保持返回相同的结果 [英] Query keeps return same results while use random sql command

问题描述

我必须使用C程序来查询我的Access数据库

sql命令是

Hi, i must use C program to query my Access Database

The sql command is

SELECT Top 1 WordID FROM [Word] WHERE [Level] = ''Easy'' ORDER BY Rnd([WordID])

我在访问中测试了查询，它工作正常，但是当我在测试程序中使用它时，它会不断返回相同的结果(似乎结果不是随机的)

请帮忙，我搜索了许多网站，但没有任何线索，我对编程非常陌生，因此，

我从Microsoft示例中获取了代码.代码是

I tested the query in access and it works fine but when I use it in the test program it keeps return the same result (It seems like it result''s not ramdom)

Please help, I searched many websites but there''s no clue and Im very new for programming so,

I took the code from the microsoft example. The code is

/* This source code demonstrates how to connect to Access 2007
   .accdb database from C using the ODBC Data Access Technology.

   Copyright (c) Microsoft Corporation.  All rights reserved.

   This sample code is provided to illustrate a concept and 
   should not be used in applications or Web sites, as it 
   may not illustrate the safest coding practices. Microsoft 
   assumes no liability for incidental or consequential damages 
   should the sample code be used for purposes 
   other than as intended.
*/

#include <windows.h>
#include <stdio.h>
#include <sqlext.h>

/* Data Access Method used in this sample */
const char* DAM = "Direct ODBC";

/* Connection string for Direct ODBC */
char szDSN[256] = 
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='''';DBQ=C:\\Users\\hp\\Downloads\\AceSampleCode\\AceSampleCode\\WordSwapDB.accdb;";

main()
{
    HENV    hEnv;
    HDBC    hDbc;

    /* ODBC API return status */
    RETCODE rc;

    int     iConnStrLength2Ptr;
    char    szConnStrOut[256];

    unsigned char* query = "SELECT Top 1 WordID FROM [Word] WHERE [Level] = ''Easy'' ORDER BY Rnd([WordID]);";

    SQLCHAR         chval1[128], chval2[128], colName[128];
    int             ret1;
    int             ret2;

    /* Number of rows and columns in result set */
    SQLINTEGER      rowCount = 0;
    SQLSMALLINT     fieldCount = 0, currentField = 0;
    HSTMT           hStmt;

    /* Allocate an environment handle */
    rc = SQLAllocEnv(&hEnv);
    /* Allocate a connection handle */
    rc = SQLAllocConnect(hEnv, &hDbc);

    /* Connect to the ''Northwind 2007.accdb'' database */
    rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
        SQL_NTS, (unsigned char*)szConnStrOut, 
        255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
    if (SQL_SUCCEEDED(rc)) 
    {
        printf("%s: Successfully connected to database. Data source name: \n  %s\n", 
           DAM, szConnStrOut);

        /* Prepare SQL query */
        printf("%s: SQL query:\n  %s\n", DAM, query);

        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);
       
        /* Bind result set columns to the local buffers */ 
        rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, chval1, 128, (SQLINTEGER*)&ret1);
        rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, (SQLINTEGER*)&ret2);
       
        /* Excecute the query and create a record set */
        rc = SQLExecute(hStmt); 
        if (SQL_SUCCEEDED(rc)) 
        {
            printf("%s: Retrieve schema info for the given result set:\n", DAM);
            SQLNumResultCols(hStmt, &fieldCount);
            if (fieldCount > 0)
            {
                for (currentField=1; currentField <= fieldCount; currentField++)
                {
                    SQLDescribeCol(hStmt, currentField,
                        colName, sizeof(colName), 0, 0, 0, 0, 0);
                    printf(" | %s", colName);    
                }
                printf("\n");
            }
            else
            {
                printf("%s: Error: Number of fields in the result set is 0.\n", DAM);
            }

            printf("%s: Fetch the actual data:\n", DAM);
            /* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc)) 
            {
                printf(" | %s | %s\n", chval1, chval2);
                rc = SQLFetch(hStmt);
                rowCount++;
            };

            printf("%s: Total Row Count: %d\n", DAM, rowCount);
            rc = SQLFreeStmt(hStmt, SQL_DROP);
        }
    }
    else
    {

        printf("%s: Couldn''t connect to %s.\n", DAM, szDSN);

    }

    /* Disconnect and free up allocated handles */
    SQLDisconnect(hDbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

    printf("%s: Cleanup. Done.\n", DAM);
}

推荐答案

RND将继续返回相同的随机数，直到更改种子为止.尝试在调用中使用GETDATE之类的函数来更改此种子.

RND will continue to return the same random number until the seed is changed. Try to use a function like GETDATE in your call to change this seed.

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )<br />
           + (DATEPART(ss, GETDATE()) * 1000 )<br />
           + DATEPART(ms, GETDATE()) );

知道了！我用

Got it!! I use

unsigned char* query = "SELECT TOP 1 WordID FROM Word WHERE Level=''Easy'' ORDER BY Rnd(-10000000*TimeValue(Now())*[WordID]);";

很棒！！！

谢谢您的回答.

It works awesome!!!

Thank You for the answer.

投票总是有帮助的； P.

Voting for the answer always helps ;P .

这篇关于使用随机sql命令时查询保持返回相同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！