Not Getting a Number Returned

Question

2.00/5 (1 vote)

See more:

I'm connecting to an Access 2010 database. The problem is that my query is not returning a number. Any help is appreciated. Thank you.

C++

/* Connect to the 'Fileber.accdb' database */
rc = SQLDriverConnect(hDbc, NULL, szDSN,  _countof(szDSN), 
szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc)) 
	{
	printf("%s: Successfully connected to database. Data source name: \n  %s\n",					DAM, szConnStrOut);
	const char* TOTL;
	TOTL = "SELECT COUNT(*) FROM tblClients";
	printf("%s: Total of Clients %s.\n", TOTL);
		}
		else
		{
		printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
		}

Posted 17-Oct-11 20:22pm

Member 7766180

Add a Solution

Comments

Stefan_Lang 18-Oct-11 6:31am

Where is the code that issues the query string to the database?

Member 7766180 18-Oct-11 6:55am

Ok. Obviously I wandered down the wrong path here! Using what I am using to make the direct odbc connection with ms access what do I actually need to query my database?

Chuck O'Toole 18-Oct-11 12:14pm

As I said to you before, google "SQLExecDirect". You said you would do that. Did it give you any information?

Member 7766180 18-Oct-11 12:37pm

Reading it now.

3 solutions

Solution 1

All I see is you sticking a "string of characters that looks suspiciously like an SQL Query" into a string variable and printing that out.

I see nothing that issues an SQL Query on the string itself so why would you expect a result?

Posted 17-Oct-11 20:26pm

Chuck O'Toole

Comments

Member 7766180 18-Oct-11 2:37am

It is an SQL Query. Why am I not getting a result? I want a result not a printout as you say and as I am getting. What do I need to do? Thank you.

Member 7766180 18-Oct-11 2:44am

Did this...
char queryString[1024];
sprintf(queryString,"SELECT COUNT(*) FROM tblClients");
mysql_query(conn, queryString);
my_ulonglong i = 0;
res_set = mysql_store_result(conn);
my_ulonglong numrows = mysql_num_rows(res_set);
LEGIT = mysql_fetch_row(res_set);
printf("%s: Total of Good %s.\n", queryString);

However; I don't think I can use sprintf with Microsft Access and ODBC? In any case its not returning the answer.

Chuck O'Toole 18-Oct-11 2:47am

Your reply shows "mysql" calls yet the code you posted in the question shows Microsoft "SQL" APIs. The fact that some query worked in another program you wrote doesn't make some new program right. Nobody is going to help you if you cannot show the relevant code in your questions.

Chuck O'Toole 18-Oct-11 2:50am

In addition, your printf's in both programs do not make sense, I do not see how you would have printed any results. "printf("%s: Total of Good %s.\n", queryString);" has TWO '%s' specifiers yet you have only ONE string variable so the printf will stop when it runs out of things in the "I/O List".

Chuck O'Toole 18-Oct-11 2:45am

No, "it" is a string of characters that forms a statement that would be interpreted as an SQL Query had you issued the SQLExecDirect() API call. Only then is it a query and only then will you get results. Did you issue the SQLExecDirect() call? If so, you don't show it in the code you posted.

Member 7766180 18-Oct-11 2:52am

No I didn't issue a SQLExecDirect() call. I will Google to find out. So mysql is not relevant I take it! Thank you.

Chuck O'Toole 18-Oct-11 3:27am

Your mysql program is not relevant when asking questions about why a program that *DOES NOT USE* mysql doesn't work.

Albert Holguin 18-Oct-11 13:40pm

+5 for effort chuck... must say... got a good giggle out of this...

Member 7766180 18-Oct-11 15:16pm

:)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chuck O'Toole · Accepted Answer · 2011-10-18T08:46:00

Solution 3

Get out the big hammer and typcast it to the desired datatype

SQLCHAR* query = (SQLCHAR *)"SELECT tblIP.[IPAddress], tblIP.[IPStatus], tblIP.[IPType] FROM tblIP ORDER BY tblIP.[IPAddress] ASC;";

Posted 18-Oct-11 8:46am

Chuck O'Toole

Comments

Member 7766180 18-Oct-11 15:14pm

Thank you Chuck. That was an easy enough solution! I think that my compiler was complaining and that fixed that! I love big hammers! My 100!

Member 7766180 · Accepted Answer · 2011-10-18T06:55:00

This works except that I am getting one error, however; it does still compile and returns info. The code then the error.

C++

#include <windows.h>
#include <stdio.h>
//#include <sql.h>
#include <sqlext.h>
//#include <odbcss.h>
//#include <string>
/* Data Access Method used in this sample */
const char* DAM = "Direct ODBC";

/* Connection string for Direct ODBC */
SQLCHAR szDSN[256] = 
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\FILEBLOCK\\Fileblocker.accdb;";

main()
{
    HENV    hEnv;
    HDBC    hDbc;

    /* ODBC API return status */
    SQLRETURN  rc;

    SQLSMALLINT  iConnStrLength2Ptr;
    SQLCHAR      szConnStrOut[255];
	
    
	SQLCHAR* query = "SELECT tblIP.[IPAddress], tblIP.[IPStatus], tblIP.[IPType] FROM tblIP ORDER BY tblIP.[IPAddress] ASC;";
    
    SQLCHAR         chval1[128], chval2[128], chval3[128], colName[128];
    SQLINTEGER      ret1, ret2, ret3;

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

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

    /* Connect to the 'Fileblocker.accdb' database */
    rc = SQLDriverConnect(hDbc, NULL, szDSN,  _countof(szDSN), 
		szConnStrOut, 255, &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, &ret1);
        rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, &ret2);
		rc = SQLBindCol(hStmt, 3, SQL_C_CHAR, chval3, 128, &ret3);

        /* Execute 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 (column = 1; column <= fieldCount; column++)
                {
                    SQLDescribeCol(hStmt, column,
                        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, chval3);
                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);
}</string></odbcss.h></sqlext.h></sql.h></stdio.h></windows.h>

The Error, It's on the SELECT Statemnet.
1 IntelliSense: a value of type "const char *" cannot be used to initialize an entity of type "SQLCHAR *"

Not Getting a Number Returned

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us