Database Tutorial

From PX Documentation

The following is a tutorial for all database-related commands within NetDog. This tutorial assumes use of an ODBC databse. In the tutorial, users will learn the following:

1. Initialization and suggested variables
2. Retrieving data from database:
   1. Retrieval of Longs from database records
   2. Retrieval of Strings from database records
3. Writing data to database
4. Opening a database connection
5. Executing a query on the database
6. Freeing NetDog memory from the executed query

        /*
        * quickdb.cpp
        * database demo
         *
         * Copyright 2007-2008 PX Interactive. All rights reserved.
         *
         * Create the following tables in a database (see nddb.sql):
         * 
         * Accounts:
         * UserName Password StartZone CurZone
         * 
         * Servers:
         * IPAddress Port CurLoad NumZones ServerId
         * 
         * Zones:
         * ZoneId ZoneName Server AreaNum InstanceNum UseInstances
         */
        

1. Initialization and database variables

        #ifdef WIN32
        // this example requres Windows XP or later
        #define _WIN32_WINNT 0x0501
        #define snprintf _snprintf
        #endif
      
        #include <stdlib.h>
        #include <string.h>
        #include <stdio.h>
        
        #include "ND.h"
        
        #define kMaxNameLen 32
        #define kMaxQueryLen 2048
        
        NDDBRef db;
        

2. Retrieving data from database:

2.1 Retrieval of Longs from database records using NDDBGetFieldData() to select specific data from query result

        int GetLongFromRow(NDQueryRef q, int col, long *data){
           char *buffer;
           int  resSize;
           char *endPtr = NULL;
        
           int result = NDDBGetFieldData(q, 1, col, (void **)&buffer, &resSize);
           if ((result != 0) || (resSize <= 0))
              return -1;
       
           buffer[resSize-1] = 0; // prevent buffer overflow bugs
           *data = strtol(buffer, &endPtr, 10);
           if ((endPtr == NULL) || (*endPtr != 0))
              return -1;
        
           return 0;
        }
        

2.2 Retrieval of Strings from database records using NDDBGetFieldData() to select specific data from query result

        int GetStringFromRow(NDQueryRef q, int col, char* buf, int bufLen){
           char *buffer;
           int resSize;
        
           int result = NDDBGetFieldData(q, 1, col, (void **)&buffer, &resSize);
           if (resSize == 0)
              return 1; // Size of zero means the string is NULL
        
           if ((result != 0) || (resSize < 0))
              return -1;
        
           if (resSize >= bufLen){
              dbg2("%s: BufSize[%d] too small for dataSize[%d]\n", __FUNCTION__, bufLen, resSize);
              return -2;
           }
        
           buffer[resSize-1] = 0; // prevent buffer overflow bugs
           memcpy(buf, buffer, resSize);
       
           return 0;
        }
        

3. Writing data to database using NDDBStoreQuery()

        void generateAccounts(int numAccts, int numZones){
           char qStr[kMaxQueryLen];
           char userName[kMaxNameLen];
           char password[kMaxNameLen];
        
           for (int i=1;i<=numAccts;i++){
              int startZone = (i % numZones)+1;
              snprintf(userName, kMaxNameLen, "c%d", i);
              snprintf(password, kMaxNameLen, "p%d", i);
              snprintf(qStr, kMaxQueryLen, "REPLACE into Accounts values ('%s', '%s', %d, 0, NULL)",            userName, password, startZone);
              NDDBStoreQuery(qStr, db, NDObjectIDDefault, -1);
           }
        }
        

4. Opening a database connection using NDDBOpen()

        int main(int argc, char **argv){
        
           NDSetNetDebug(0);
           NDSetDebugLevel(2);
        
           char userName[kMaxNameLen];
           char password[kMaxNameLen];
        
           memset(userName, 0, kMaxNameLen);
           memset(password, 0, kMaxNameLen);
        
           if (argc < 3){
              dbg0("Usage: quickdb userName password\n");
              return 0;
           }
           
           snprintf(userName, kMaxNameLen, argv[1]);
           snprintf(password, kMaxNameLen, argv[2]);
        
           db = NDDBOpen((void *)"nddb");
           if (db == NULL){
              dbg1("Could not access database... exiting...\n");
              return 1;
           }
        
           // Uncomment this to generate test accounts
           // generateAccounts(1000, 4);
        

5. Executing a query on the database using NDDBLoadQuery()

and then 6. Freeing NetDog memory from the executed query using NDDBFreeQuery()

           // Query DB for this username/password
           char qStr[kMaxQueryLen];
           snprintf(qStr, kMaxQueryLen, "SELECT UserName, StartZone, StartTime FROM Accounts WHERE userName='%s' and password='%s'", userName, password);
           NDQueryRef q = NDDBLoadQuery(qStr, db);
        
           // Read startZone from DB results
           long startZone;
           int result = GetLongFromRow(q, 2, &startZone);
           if (result < 0){
              NDDBFreeQuery(q);
              dbg0("User[%s] pass[%s] not found!\n", userName, password);
              return 0;
           }
        

           // Check for duplicate logins for clients only
           if (strncmp(userName, "servershell", kMaxNameLen) != 0){
        
              // Read startTime field from DB query results
              char startTime[kMaxNameLen];
              result = GetStringFromRow(q, 3, startTime, kMaxNameLen);
              NDDBFreeQuery(q);
              if (result < 0){
                 	 dbg2("User[%s] pass[%s] has invalid startTime!\n", userName, password);
                 	 return 0;
              }
        
              if (result != 1){
                 	 dbg3("%s: User[%s] already logged in at %s!\n", __FUNCTION__, userName, startTime);
              }
        
              // Update DB with startTime for this user account
              snprintf(qStr, kMaxQueryLen, "UPDATE Accounts SET StartTime=current_timestamp WHERE userName='%s'", userName);
              NDDBStoreQuery(qStr, db, NDObjectIDDefault, -1);
           }
        
           dbg0("User[%s] pass[%s] has startZone[%d]\n", userName, password, startZone);
        
           return 0;
        }