Thread: Inventory System

Just starting out in the C language. Learning it as my first language, and I'm really enjoying it. My first major program is now 95% complete, a little cheat program for people in school for maths and science work, it taught me a lot, but it's time to move on.

I want to have a go at an inventory system, now I'm thinking of using a mySQL database to store various product information, then use a C command line program as an interface. How easy is this for a beginner. I'd also to generate a word document with various product information or receipts, again how easy is this.

Any information greatly received.

Probably not that easy. Do you already know SQL? How about the Word document format?

If you are already familiar with SQL and Word, then that will help quite a bit. If not, then you may find this project a little overwhelming.

Originally Posted by headshot119

Not particularly familiar with SQL, could Open Office Access be used for the database part, I'm familiar with that.

I've never used Open Office Access, so I'm not sure.

There are C libraries for dealing with SQL db's. I haven't used one (in C) but probably they are pretty simple, so it would be a good first API to learn.

Google "C API SQL" and you will find stuff. Eg, this is part of mySQL:

MySQL :: MySQL 5.0 Reference Manual :: 20.9.2 C API Function Overview

I would recommend going with SQL in some form, it is not hard to learn and much more portable than some Open Office thing; understanding how SQL works is a pretty useful skill and the knowledge is applicable to most other languages as well (eg. php). Here's the tutorial I used:

http://www.firstsql.com/tutor1.htm

Which is just about Structured Query Language itself.

If all this is looking like a bit much and you want more practice doing basic things in C, creating your own database would be a great idea. You do not have to use SQL or anything, the concept of "a database" is straightforward enough, you just need an idea for the implimentation.

Originally Posted by headshot119

I assume I could use some sort of multidimensional array to create the database, but how can I save the data once I exit the program? This may be the easier way to do the program initially.

You can always save it to a text file. Check into the following I/O Functions:

fopen()
fclose()
fread()
fwrite()

Originally Posted by headshot119

I assume I could use some sort of multidimensional array to create the database, but how can I save the data once I exit the program? This may be the easier way to do the program initially.

You save it to a file. I would use an array of structs for a database, since a single struct equates very well with the concept of a single record, for example:

Code:

struct record {
       int ID;
       char partname[256];
       char partnum[64];
       char *data;
}

IMO, the best way to save the data is save it in text form. The other alternative is to serialize the struct array and write it to a binary file. This will be faster and POSSIBLY slightly easier BUT there are some potentially difficult complications if you are not comfortable with structs, pointers and serialization (don't eat too much at once!).

With a text file, it is very easy to verify that your program works, because you can visually inspect the database. You can simply use one line per record -- even if the line is several hundred (or even a thousand) characters long, that's fine. So you need two functions, one to take a struct/record and turn into a line written into the text file, and another one to read a line from the file and arrange the data properly into a struct. That will probably mean using fgets(), sscanf(), and/or strtok().

The first thing you should do is get a handle on how to create and populate a struct, how to create an array of structs, and how to pass a pointer to a struct around and a pointer to an array of structs around. After that, you incorporate reading/writing to a file, which is very easy.

You could use a linked list here but that is not necessary, an array will be fine.

My other note would be that using an int ID with database records will come in handy later -- this has nothing to do with the record data, it is just a unique number for each record, eg, 1, 2, 3, 4, etc. making sure no two records have the same ID.

I found MySQL pretty straight forward. I wrote this some time ago. It worked on my Mac.

Code:

#include <stdio.h>
#include <stdlib.h> 
#include <string.h>
#include <memory.h>  
#include </usr/local/mysql/include/mysql.h> 

#define DBNAME "todd" 
#define TBNAME "products" 

MYSQL * mysql ; 

int do_sql(char * sqltext ) { 
	if (mysql_query(mysql, sqltext) ) { 
		fprintf(stderr, "SQL Statement failed: >%s<\nError %d: %s\n", 
			sqltext, mysql_errno(mysql), mysql_error(mysql) ) ; 
		return 0 ;  // error 
	}
	return 1 ; // all is OK 
}


int main (int argc, const char * argv[]) {

	int i ; 
	char create_db[] = "CREATE DATABASE " DBNAME " ; " ; 
	char create_tb[] = "CREATE TABLE " DBNAME "." TBNAME " ( " 
		"col1 INTEGER NOT NULL" 
		") ; " ; 
	char insert[] = "INSERT INTO " DBNAME "." TBNAME " (col1) values ( %d ) ; " ; 
	char drop_db[] = "DROP DATABASE " DBNAME " ; " ;  
	char select[] = "SELECT * FROM " DBNAME "." TBNAME " ORDER BY col1 DESC ; " ; 
	char temp_stmt[81] ;  
	MYSQL_RES * result_set ; 
	MYSQL_ROW row ; 
	int numcols ; // , numrows ; 
	
	
	mysql = calloc(1, sizeof(MYSQL)) ; 
	mysql_init(mysql) ; 

	if (!mysql_real_connect(mysql,"localhost", "root", "changeme", NULL , 0, NULL ,0)) { 
		fprintf(stderr, "Unable to connect. Error %d: %s\n", mysql_errno(mysql), mysql_error(mysql)) ; 
		return -1 ; 
	}
	else printf("Connected!\n") ; 

	// Drop the Database if it exists 
	if (do_sql(drop_db)) {  
		printf("Database " DBNAME " dropped!\n") ; 
	} 

	// Create the Database 
	
	if (!do_sql(create_db)) {  
		fprintf(stderr, "exiting.\n") ;  
		return -1 ; 
	} 
	else { 
		printf("Database " DBNAME " created!\n") ; 
	} 
	
	if (!do_sql(create_tb)) {  
		fprintf(stderr, "exiting.\n") ;  
		return -1 ; 
	} 
	else { 
		printf("Table " TBNAME " created!\n") ; 
	} 
	// Insert into the table a few rows 
	for (i=0 ; i < 10 ; i++ ) { 
		sprintf(temp_stmt, insert, i+1) ; 
		printf("Insert = >%s<\n", temp_stmt) ; 
		if (!do_sql(temp_stmt)) { 
			fprintf(stderr, "exiting insert loop...\n") ;  	
			break ; 
		} 
	}
	
	
	printf("Inserted %d rows.\n", i) ; 
	
	// Now, select the Rows.   
	
	if (!do_sql(select)) {  
		fprintf(stderr, "exiting.\n") ;  
		return -1 ; 
	} 
	else { 
		printf("SELECT was OK!\n") ; 
	} 
	
	
	// Get the handle for the result table 
	
	result_set = mysql_use_result(mysql) ; 

	if (!result_set) { 
		fprintf(stderr, "use_result failed.  Error: %d, %s\n", 
			mysql_errno(mysql), mysql_error(mysql) ) ;  
		return -1 ; 
	} 
	else { 
		printf("Use Result worked!\n") ; 
	} 
	
	numcols = mysql_num_fields(result_set) ; 
	printf("There are %d columns in the result table.\n", numcols );

	while  ( row = mysql_fetch_row(result_set) ) {
		for ( i = 0 ; i < numcols ; i++) {
			printf("Row %d value = %s\n", i+1, row[i] );
		}
	}
	
    return 0;
}

Code:

ptr->data[64] = '\0';

This is overwriting the array, and will probably cause a crash. A much safer way of doing this is:

Code:

ptr->data[sizeof(prt->data)-1] = '\0';

Originally Posted by bithub

Code:

ptr->data[64] = '\0';

This is overwriting the array, and will probably cause a crash. A much safer way of doing this is:

Code:

ptr->data[sizeof(prt->data)-1] = '\0';

Yeah check that...

Looking at it further, I'm not really a big fan of the double pointer design. It makes the code unintuitive (in my opinion). I would rather use the approach of keeping the records stored in a separate database structure. I think it makes the design a bit cleaner (although opinions may vary).

Here is an example of what I mean. Note that this is based on MK27's code, and also I didn't do any checking to see if malloc() or realloc() failed (to keep the code as clean as possible).

Code:

#include <stdio.h>
#include <stdlib.h> /* malloc, realloc, free */
#include <string.h>

#define DATA_SIZE 64
struct record {
    int ID; 
    char data[DATA_SIZE];
};

struct database {
    struct record* records;
    unsigned int num_records;
};

struct database* create_database(void) {
    struct database* db = malloc(sizeof(*db));
    db->records = NULL;
    db->num_records = 0;
    return db; 
}

void free_database(struct database* db) {
    if(db)
    {   
        if(db->records)
            free(db->records);
        free(db);
    }   
}

int add_record(const char* data_string, struct database* db) {
    static int id = 0;
    db->records = realloc(db->records, sizeof(struct record) * (db->num_records+1));
    if(strlen(data_string) >= DATA_SIZE)
        return -1; /* Error, data too big */
    db->records[db->num_records].ID = ++id;
    strcpy(db->records[db->num_records++].data, data_string);

    return 0;  
}

void show (struct record *ptr) {
    printf("ID: %d Data: %s\n",ptr->ID,ptr->data);
}

int main() {
    unsigned int i;
    struct database* db = create_database();
    add_record("first record", db);
    add_record("second record", db);
    for (i=0; i<db->num_records; i++) 
        show(&db->records[i]);
    free_database(db);
    return 0;
}

Originally Posted by bithub

Looking at it further, I'm not really a big fan of the double pointer design. It makes the code unintuitive (in my opinion). I would rather use the approach of keeping the records stored in a separate database structure. I think it makes the design a bit cleaner (although opinions may vary).

There are definitely more than a few ways to skin this cat. Really, using a global array of predefined size would be fine, and in fact, that might be the best idea since there will only be one database and this will hardly make the program somehow "unmanageable"; the central conceit is the db, making it a global object makes sense.

But I think it is important to understand and feel comfortable with double pointers ASAP, esp. with something like this, which is partially why I brought that in. Being able to do something in many specific ways may be better than having many ways of avoiding one specific method. I dunno if I agree using the outer container (struct database) is less confusing or more intuitive, but I do agree being able to conceive of many alternative methods is essential.

Pointer arrays are common and useful. A dynamic pointer array is a often a double pointer, so they cannot be totally written off.

@headshot119: notice bithub uses free(). This is not really necessary here, since the OS will do that at the end anyway, but it is a good illustration of the principle.