C++

Forum

 
SQL Statements Not Executing

Pages: 123
DSTR3A (97)
I have code that almost works. The first SQL statement runs and returns the values. The subsequent queries don't run. I am using irect ODBC, Visual Studio 2010, C++, Win7 and Access 2010. 32bit. Here is the code...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 

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

const char* DAM = "Direct ODBC";

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

main()
{
    HENV    hEnv;
    HDBC    hDbc;

    SQLRETURN  rc;

    SQLSMALLINT  iConnStrLength2Ptr;
	SQLCHAR      szConnStrOut[255];
	
    SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]='173.201.216.2' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;";
	
    /* 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 */
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);
       
       	/* Execute the query and create a record set */
        rc = SQLExecute(hStmt); 
        
		/* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc)) 
            {
                rc = SQLFetch(hStmt);
                rowCount++;
            };

            printf("%s: Total Row Count: %d\n", DAM, rowCount);
            rc = SQLFreeStmt(hStmt, SQL_DROP);
			if (rowCount >= 1)
				{
				printf("PASS\n");
				//SQLExecute ("INSERT INTO tblDownloads (DownloadIP , DownloadCount) VALUES('173.201.216.2', 2);");
				//int TOTAL;
				//TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] =  '173.201.216.2';");
				//int QUOTA;
				//QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] =  '173.201.216.2';");
				//if (TOTAL >= QUOTA)
				//	{
				//	SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP[IPAddress] = '173.201.216.2');");
					}
			else if (rowCount == 0)
				{
				printf("FAIL\n");
				rc = SQLFreeStmt(hStmt, SQL_DROP);
				}
			system("pause");
        //}
    }
    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);
}
Last edited on
freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
 
SQLExecute ("INSERT INTO tblDownloads (DownloadIP , DownloadCount) VALUES('173.201.216.2', 2);");
				int TOTAL;
				TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] =  '173.201.216.2';");
				int QUOTA;
				QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] =  '173.201.216.2';");
				if (TOTAL >= QUOTA)
					{
					SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP[IPAddress] = '173.201.216.2');");
					}


I'm at a loss in trying to understand the above code. Does that actually compile?
DSTR3A (97)
Hi Freddie1,
The first statement is an INSERT statement. The next two are SELECT statements to get the total number of rows the last statement only executes if TOTAL is equal to or greater than quota. It doesn't work, but I don't understand how to do it in the spirit of the rest of the code.
Thank you.
freddie1 (1838)
If my understanding of your code is correct DSTR3A, it looks like you are trying to use your hStmt on multiple statements. To the best of my knowledge, that doesn't work. After all, the function that gives you a handle to a statement is SQLAllocHandle() or SQLAllocStmt() - both singular. What you likely need to do is free your statement handle after your select and fetch sequence, then re-allocate new ones for each subsequent operation.
DSTR3A (97)
yowww! Can I do this?
hStmt
hStmt1
hStmt2
hStmt3
hStmt4

This sounds like a lot of back and forth. Would I need it for the Execute as well?
Thanks
DSTR3A (97)
Got the INSERT to work, Now I'm trying to get the following block to work. I'm geting an undeclared identifier error on TOTAL and QUOTA..

1
2
3
4
5
6
7
8
9
 
int TOTAL;
TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';");
int QUOTA;
QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';");
				
	if (TOTAL >= QUOTA)
	{
	SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');");
	}
freddie1 (1838)
Well DSTR3A, that is how I've been doing it for years. I taught all this stuff to myself from studying books and MS docs, and I guess that's how I always saw it done and its how I do it. You generally don't need all that many open at any one time. If all you are doing is dumping data, all you'll really need is one hStmt for the SELECT. Also, you are doing extra work with your sequence of SQLPrepare() followed by SQLExec(). SQLExecDirect() does both in one fell swoop, i.e., prepares the statement internally within ODBC, then executes it.

Cases where you'll need more than one, however, are situations where you are selecting and reading records, and then, based on your program logic and what you find in the records, executing another SQL statement on that record or another one. For example, say you are in a forgiving frame of mind, and you want to run through your buyer's table setting all balance due amounts to zero if they are something positive. In that case you would select all the records in a table, plow through them, and when you found one where somebody owed you something, you would execute an SQL UPDATE against that record.
freddie1 (1838)
In that case we're talking two hStmts. The plot does thicken though somewhat. Not all RDMSs are the same. Some allow a one to many relationship between a connection handle and a statement handle, i.e., you get a handle to a connection from SQLDriverConnect(), then obtain multiple statement handles from that hConn. However, others enforce a one to one relationship between connection handles and statement handles. Microsoft Access exemplifies the former situation and SQL Server the latter.
DSTR3A (97)
Well freddie1 I like your idea of SQLExecDirect. How would I do that on the first SQL statement. Here is what I have so far. BTW the INSERT stopped working. freddie1 your help is greatly appreciated! 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 

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

const char* DAM = "Direct ODBC";

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

main()
{
    HENV    hEnv;
    HDBC    hDbc;

    SQLRETURN  rc, TOTAL, QUOTA;

    SQLSMALLINT  iConnStrLength2Ptr;
    SQLCHAR      szConnStrOut[255];
	
    SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]='173.201.216.2' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;";
	/* 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 */
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);
       
       	/* Execute the query and create a record set */
        rc = SQLExecute(hStmt); 
        
		/* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc)) 
            {
                rc = SQLFetch(hStmt);
                rowCount++;
            };

            printf("%s: Total Row Count: %d\n", DAM, rowCount);
            rc = SQLFreeStmt(hStmt, SQL_DROP);
			if (rowCount >= 1)
				{
				printf("PASS\n");
				SQLExecute ("INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);");
				
				TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';");
				QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';");
				
				if (TOTAL >= QUOTA)
					{
					SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');");
					}
			else if (rowCount == 0)
				{
				printf("FAIL\n");
				rc = SQLFreeStmt(hStmt, SQL_DROP);
				}
			//system("pause");
        //}
    }
    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);
}
}
Last edited on
DSTR3A (97)
OK up to this point, Sometimes INSERT works, other times no. The TOTAL and QUOTA return 0.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
//Burton
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>

const char* DAM = "Direct ODBC";

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

main()
{
    HENV    hEnv, hEnv2, hEnv3, hEnv4, hEnv5;
    HDBC    hDbc, hDbc2, hDbc3, hDbc4, hDbc5;

    SQLRETURN  rc, NEW, TOTAL, QUOTA, UP;

    SQLSMALLINT  iConnStrLength2Ptr;
	SQLCHAR      szConnStrOut[255];
	
    SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]='173.201.216.2' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;";
	SQLCHAR* query2 = (SQLCHAR*)"INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1)";
	SQLCHAR* query3 = (SQLCHAR*)"SELECT SUM(DownloadCount) AS DT FROM tblDownloads WHERE tblDownloads.[DownloadIP]='173.201.216.2'";
	SQLCHAR* query4 = (SQLCHAR*)"SELECT tblIP.[IPQuota] FROM tblIP WHERE tblIP.[IPAddress] = '173.201.216.2'";
	SQLCHAR* query5 = (SQLCHAR*)"UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2'";
	
	/* Number of rows and columns in result set */
    SQLINTEGER      rowCount = 0;
    SQLSMALLINT     fieldCount = 0;
    HSTMT           hStmt, hStmt2, hStmt3, hStmt4, hStmt5;

    /* Allocate an environment handle */
    rc = SQLAllocEnv(&hEnv);
	NEW = SQLAllocEnv(&hEnv2);
	TOTAL = SQLAllocEnv(&hEnv3);
	QUOTA = SQLAllocEnv(&hEnv4);
	UP = SQLAllocEnv(&hEnv5);
    /* Allocate a connection handle */
    rc = SQLAllocConnect(hEnv, &hDbc);
	NEW = SQLAllocConnect(hEnv3, &hDbc2);
	TOTAL = SQLAllocConnect(hEnv3, &hDbc3);
	QUOTA = SQLAllocConnect(hEnv4, &hDbc4);
    UP = SQLAllocConnect(hEnv5, &hDbc5);
	
	/* 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 */
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);
       
       	/* Execute the query and create a record set */
        rc = SQLExecute(hStmt); 
        
		/* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc)) 
            {
                rc = SQLFetch(hStmt);
                rowCount++;
            };

            printf("%s: Total Legit IP Count: %d\n", DAM, rowCount);
            rc = SQLFreeStmt(hStmt, SQL_DROP);
			if (rowCount >= 1)
				{
				printf("PASS\n");
				NEW = SQLDriverConnect(hDbc2, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
				NEW = SQLAllocStmt(hDbc2,&hStmt2);
				NEW = SQLPrepare(hStmt2, query2, SQL_NTS);
				NEW = SQLExecute(hStmt2); 
				NEW = SQLFreeStmt(hStmt2, SQL_DROP);
				
				TOTAL = SQLDriverConnect(hDbc3, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
				TOTAL = SQLAllocStmt(hDbc3,&hStmt3);
				TOTAL = SQLPrepare(hStmt3, query3, SQL_NTS);
				TOTAL = SQLExecute(hStmt3); 
				TOTAL = SQLFetch(hStmt3);
				printf("%s: Total of Downloads: %d\n", DAM, TOTAL);
				TOTAL = SQLFreeStmt(hStmt3, SQL_DROP);
				
				QUOTA = SQLDriverConnect(hDbc4, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
				QUOTA = SQLAllocStmt(hDbc4,&hStmt4);
				QUOTA = SQLPrepare(hStmt4, query4, SQL_NTS);
				QUOTA = SQLExecute(hStmt4); 
				QUOTA = SQLFetch(hStmt4);
				printf("%s: Quota For IP Address: %d\n", DAM, QUOTA);
				QUOTA = SQLFreeStmt(hStmt4, SQL_DROP);
				if (TOTAL >= QUOTA)
					{
					UP = SQLDriverConnect(hDbc5, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
					UP = SQLAllocStmt(hDbc5,&hStmt5);
					UP = SQLPrepare(hStmt5, query5, SQL_NTS);
					UP = SQLExecute(hStmt5); 
					UP = SQLFetch(hStmt5);
					UP = SQLFreeStmt(hStmt5, SQL_DROP);
					}
			else if (rowCount == 0)
				{
				printf("FAIL\n");
				rc = SQLFreeStmt(hStmt, SQL_DROP);
				}
			system("pause");
        //}
    }
    else
    {
        printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
    }

    /* Disconnect and free up allocated handles */
                SQLDisconnect(hDbc);
	SQLDisconnect(hDbc2);
	SQLDisconnect(hDbc3);
	SQLDisconnect(hDbc4);
	SQLDisconnect(hDbc5);

                SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc2);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc3);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc4);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc5);
    
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv2);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv3);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv4);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv5);
}
}
freddie1 (1838)
The below procedure shows clearly what I mean. Sorry its in PowerBASIC and not C++ but I have more ODBC code in PowerBASIC than I do in C++ simply because of the way I use the two languages. The context for this code is that it executes when a user makes a selection from a combo box. It queries our main database on one of our servers for the year of all the timber sales given as input the district number chosen from the combo box. You'll se a SELECT DISTINCT query. Do you know what that is? There are no duplicates. Anyway, as you can see, you just plug into SQLExecDirect() your statement handle, a pointer to your query string you want prepared AND executed, and SQL_NTS for the length of the string - which tells ODBC its null terminated so deal with it. Also note my loop. Maybe I'm just partial to it because its mine, but I'd prefer doing it like that than what you have. No offense intended, please! You can see in the loop where I'm sending the return numbers (years) to another combo box. Glad to Help!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
Sub cboDistrict_Change(wea As ProgramData)
  Local szQuery As Asciiz*80,szDist As Asciiz*3
  Local ptrDBPaths As szPaths Pointer
  Local hStmt,hMain As Dword
  Local i,iYr,Yrind As Long
  Local szYear As Asciiz*5
  Local Sql As tagODBC

  Call SendMessage(GetDlgItem(wea.hWnd,%IDC_CBO_TRACKING_YEAR),%CB_RESETCONTENT,0,0)
  Call SendMessage(GetDlgItem(wea.hWnd,%IDC_CBO_TRACKING_TYPE),%CB_RESETCONTENT,0,0)
  Call SendMessage(GetDlgItem(wea.hWnd,%IDC_CBO_TRACKING_NUM),%CB_RESETCONTENT,0,0)
  Call SendMessage(GetDlgItem(wea.hWnd,%IDC_CBO_TRACKING_DIST),%WM_GETTEXT,3,Varptr(szDist))
  hMain=GetWindowLong(wea.hWnd,0)
  ptrDBPaths=GetWindowLong(hMain,0)
  Call ODBCConnect(@ptrDBPaths.szDBName,Sql)
  If Sql.blnConnected=%TRUE Then
     szQuery="SELECT DISTINCT Main.Yr FROM Main WHERE Main.Dist=" & szDist & " ORDER BY Main.Yr DESC;"
     Call SQLAllocHandle(%SQL_HANDLE_STMT,Sql.hConn,hStmt)
     Call SQLBindCol(hStmt,1,%SQL_C_ULONG,iYr,0,Yrind)
     Call SQLExecDirect(hStmt,szQuery,%SQL_NTS)
     Do While SQLFetch(hStmt)<>%SQL_NO_DATA         //  << <> means not equal
        szYear=Right$(Str$(iYr),4)
        Call SendMessage(GetDlgItem(wea.hWnd,%IDC_CBO_TRACKING_YEAR),%CB_INSERTSTRING,-1,Varptr(szYear))
     Loop
     Call SQLCloseCursor(hStmt)
     Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
     Call ODBCDisconnect(Sql)
     Call SetWindowLong(wea.hWnd,8,%TRUE)
  Else
     MsgBox("Couldn't Connect!")
  End If
End Sub
Last edited on
freddie1 (1838)
Just so that PowerBASIC code doesn't confuse you, the 2nd parameter of SQLExecDirect() above is your SQLCHAR* of C/C++, or close to it. I note you don't use Hungarian notation. That is something closely related to Windows GUI coding and involves those 'sz' and 'i', and 'bln' prefixes you see on a lot of my variables (sz - string terminated by zero; i - int; bln - boolean). For example, in C/C++ I'll declare an 80 byte char array like so...

char szQuery[80];

or

const char szQuery[]="Some Big Long String.........";

It means zero terminated string. In PowerBASIC I do the same thing. This...

Local szQuery As Asciiz*80

is just a variable declaration local to a procedure of an 80 byte char array. Otherwise, I think you should be able to follow the above. The Hungarian notation thing comes from Charles Simonyi, a famous Hungarian software developer for Microsoft.
Last edited on
DSTR3A (97)
Thank you freddie1 I'll will try this tonite.
DSTR3A (97)
Hello freddie1, take a look at this! All is working except the QUOTA statement (query4) I'm not counting rows. I'm trying to get the value of a field. Maybe I have to do something different?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
//Burton
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>

const char* DAM = "Direct ODBC";

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

main()
{
    HENV    hEnv, hEnv2, hEnv3, hEnv4, hEnv5;
    HDBC    hDbc, hDbc2, hDbc3, hDbc4, hDbc5;

    SQLRETURN  rc, NEW, TOTAL, QUOTA, UP;

    SQLSMALLINT  iConnStrLength2Ptr;
	SQLCHAR      szConnStrOut[255];
	
    SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]='173.201.216.2' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;";
	SQLCHAR* query2 = (SQLCHAR*)"INSERT INTO tblDownloads (DownloadIP,DownloadCount) VALUES('173.201.216.2', 1);";
	SQLCHAR* query3 = (SQLCHAR*)"SELECT tblDownloads.[DownloadCount] FROM tblDownloads WHERE tblDownloads.[DownloadIP]='173.201.216.2' AND tblDownloads.[DownloadCount]=1;";
	SQLCHAR* query4 = (SQLCHAR*)"SELECT tblIP.[IPQuota] FROM tblIP WHERE tblIP.[IPAddress] ='173.201.216.2';";
	SQLCHAR* query5 = (SQLCHAR*)"UPDATE tblIP SET tblIP.IPMax WHERE tblIP.IPAddress = '173.201.216.2';";
	
	/* Number of rows in result set */
    SQLINTEGER      rowCount = 0;
	SQLINTEGER      rowCount3 = 0;
	SQLINTEGER		rowCount4 = 0;
	HSTMT           hStmt, hStmt2, hStmt3, hStmt4, hStmt5;

    /* 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 */
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);
       
       	/* Execute the query and create a record set */
        rc = SQLExecute(hStmt); 
        
		/* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc)) 
            {
                rc = SQLFetch(hStmt);
                rowCount++;
            };
			rc = SQLFreeStmt(hStmt, SQL_DROP);
            printf("%s: Total Legit IP Count: %d\n", DAM, rowCount);
            if (rowCount >= 1)
				{
				printf("PASS\n");
				NEW = SQLAllocEnv(&hEnv2);
				NEW = SQLAllocConnect(hEnv2, &hDbc2);
				NEW = SQLDriverConnect(hDbc2, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
				NEW = SQLAllocStmt(hDbc2,&hStmt2);
				NEW = SQLPrepare(hStmt2, query2, SQL_NTS);
				NEW = SQLExecute(hStmt2); 
				NEW = SQLFreeStmt(hStmt2, SQL_DROP);
				SQLDisconnect(hDbc2);
				SQLFreeHandle(SQL_HANDLE_DBC, hDbc2);
				SQLFreeHandle(SQL_HANDLE_ENV, hEnv2);
				
				TOTAL = SQLAllocEnv(&hEnv3);
				TOTAL = SQLAllocConnect(hEnv3, &hDbc3);
				TOTAL = SQLDriverConnect(hDbc3, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
				TOTAL = SQLAllocStmt(hDbc3,&hStmt3);
				TOTAL = SQLPrepare(hStmt3, query3, SQL_NTS);
				TOTAL = SQLExecute(hStmt3); 
				TOTAL = SQLFetch(hStmt3);
				while (SQL_SUCCEEDED(TOTAL)) 
					{
					TOTAL = SQLFetch(hStmt3);
					rowCount3++;
					};
				TOTAL = SQLFreeStmt(hStmt3, SQL_DROP);
				printf("Total of Downloads: %d\n",rowCount3);
				SQLDisconnect(hDbc3);
				SQLFreeHandle(SQL_HANDLE_DBC, hDbc3);
				SQLFreeHandle(SQL_HANDLE_ENV, hEnv3);
								
				QUOTA = SQLAllocEnv(&hEnv4);
				QUOTA = SQLAllocConnect(hEnv4, &hDbc4);
				QUOTA = SQLDriverConnect(hDbc4, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
				QUOTA = SQLAllocStmt(hDbc4,&hStmt4);
				QUOTA = SQLPrepare(hStmt4, query4, SQL_NTS);
				QUOTA = SQLExecute(hStmt4); 
				QUOTA = SQLFetch(hStmt4);
				QUOTA = SQLFreeStmt(hStmt4, SQL_DROP);
				printf("Quota For IP Address: %d\n",QUOTA);
				SQLDisconnect(hDbc4);
				SQLFreeHandle(SQL_HANDLE_DBC, hDbc4);
				SQLFreeHandle(SQL_HANDLE_ENV, hEnv4);
								
				if (TOTAL >= QUOTA)
					{
					UP = SQLAllocEnv(&hEnv5);
					UP = SQLAllocConnect(hEnv5, &hDbc5);
					UP = SQLDriverConnect(hDbc5, NULL, szDSN,  _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
					UP = SQLAllocStmt(hDbc5,&hStmt5);
					UP = SQLPrepare(hStmt5, query5, SQL_NTS);
					UP = SQLExecute(hStmt5); 
					UP = SQLFetch(hStmt5);
					UP = SQLFreeStmt(hStmt5, SQL_DROP);
					SQLDisconnect(hDbc5);
					SQLFreeHandle(SQL_HANDLE_DBC, hDbc5);
					SQLFreeHandle(SQL_HANDLE_ENV, hEnv5);
					}
			else if (rowCount == 0)
				{
				printf("FAIL\n");
				//rc = SQLFreeStmt(hStmt, SQL_DROP);
				}
			system("pause");
        //}
    }
    else
    {
        printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
    }

    /* Disconnect and free up allocated handles */
    rc = SQLFreeStmt(hStmt, SQL_DROP);
	SQLDisconnect(hDbc);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}
}
freddie1 (1838)
My first comment is you are making it about four times harder than it is and writting about four times more code than you need.

Yesterday I told you that some relational Database management systems (RDMS) specify a one to one relationship between connection handles and statement handles. Others allow multiple active statement handles per connection handle. Microsoft Access is of this latter type. All that stuff you are doing with all those environment handles, connection handles, multiple calls to SQLDriverConnect, is completely unnecessary!

All you need inn your program above is one environment handle, one connection handle, one statement handle, and one call to SQLDriverConnect to open the connection and one call to SQLDisconnect when you are done.

When you are done executing a statement leave everything open and valid except the statement handle. Close your statement handle and cursor, then use the very same variable, i.e., hStmt, to acquire a new statement handle for your next operation.
Last edited on
freddie1 (1838)
I really think you would do very well by dropping what you are doing temporarily and putting some effort into understanding that code I provided to Lamblion (which he now has working I believe since he is running it from directories without spaces in hiis file paths), and looking at how I modularized it through various procedures to open the connection, reterieve results, etc. I'm saying this because your general code layout and orgganization will soon become unmanageable if you keep going the way you are. I don't mean to be critical because what you are doing is EXACTLY what I did when I first started years ago. I'm just trying to steer you to a better way of handling it.

The general philosophy of what you must do is get all that miserable grunge code necessary to open a connection, i.e., SQLAllocEnv(), SQLSetEnvrAttribute(), SQLAllocConn(), SQLDriverConnect(), out of the way in functions or classes somewhere, preferably in header files you;ll never want to ever look at again, so you can get to the business at hand, which is using one active connection and one or two statement handles to do what you need to do.
freddie1 (1838)
In terms of that query4 you are executing a SELECT statement that likely is returning a recordset/cursor containing multiple rows, but all you do is execute a fetch on the first one. If you want to count them then iterate through the cursor with a loop.
DSTR3A (97)
Thnak you freddie1. All is working now. Just one problem. Its the if(rowCount3 <= chval1) The problem is rowCount3 is an SQLINTEGER and chval1 is a SQLCHAR. I have two optons either convert chval1 to an int or changing QUOTA = SQLPrepare(hStmt4, query4, SQL_NTS); How would I change the SQLPrepare() the query4 part is set as an SQLCHAR, if I can change that to an SQLINTEGER, then I can change it from the statement way at the top and start out with it as an integer. I am going to work with your soution after I get this to work, I am so close, I just want to put it to bed, then I'll try your way which is probably better. Thank you freddie1.
Lamblion (642)
DSTR3A: If you don't already know about it, you may find this link helpful --

http://msdn.microsoft.com/en-us/library/aa198053(v=SQL.80).aspx

freddie1: Thanks for all the diligent help. You have got me started so that I can now begin to feel my way along. Much appreciated.
Last edited on
DSTR3A (97)
Thank you Lamblion. Great link! Helped me a lot!
Last edited on
Pages: 123