Database for win32 app?

[mitch_m]

The program I am writing is fairly simple. It will be storing data as it aquires it (during the run), and the data may never be the same length.

I know how to use fstream, but that seems to only be good for adding new entries to a flat file. I may need to modify/update certain things i stored, or even delete them.

I can use a flat file or a full-on db. Problem is, what to use. Anyone have any suggestions on a way to store my data (Access bd, mysql-with mysql++, etc..?

I want to keep it as simple as possible. I could write one using fstream, but who really wants to re-invent the wheel.

thanks

[kirants]

What kinda data ? Binary, text ?

[mitch_m]

each "entry" in the database will have 2 corresponding parts. one string for an ID_NAME, and another string for data (which will be html). I really dont care how its saved, as long as its saved.

in short, text. haha :)

[mitch_m]

Actually i wanted to save the data as an XML file.
i have a class, but for some reason i cant get it to work it withmy win32 application (no MFC).

the class can be downloaded @ the codeproject:

http://www.codeproject.com/file/logxml.asp

[Marc G]

What problems are you experiencing?
Can you get it to compile?
Does compile give an error? Which one?
Is there an error at runtime?

[mitch_m]

My program compiles fine. It seems, whenever i try to write to a file whether it is using fstream, win32 API (createfile()), or with the LogXML class, my app crashes. After i debugged line by line, i always narrow it down to the line where the app writes the data to the file.

I have never been able to figure out why. Oddly enough, if you look at the LOGXML class, when i delare a new log, the fn should create the .xml file, and write some lines to it. It creates the file, but no lines are written. I dont get an error there though. Any othertime i try to write, i do.

[YourSurrogateGod]

I've already written a C command-line program that makes a connection with mysql version 4.0.18-nt. Since with WinAPI it's quite similar, you can probably use the same thing(assuming that you are creating a windows App and writing in C as Petzold in his book does). It should work fine, I've yet to try it in a Windows app, since I'm as advanced in WinAPI as I'd like to be :rolleyes: . But it's definitely worth a look at ;) .

[mitch_m]

Hm.. If you have any tutorials on how to implement it the way you did, i'd like to take a look @ it. if you want my email its mmatuson@yahoo.com


Id still like to get to the bottom of my problem though.

[Marc G]

Originally posted by mitch_m
It creates the file, but no lines are written. I dont get an error there though. Any othertime i try to write, i do.
What error exactly?

[YourSurrogateGod]

This is how I did it. I downloaded this stuff (http://dev.mysql.com/downloads/mysql/4.0.html). Go to the section labeled windows. From there you should probably select the second option from the top, labeled "Without installer (unzip in C:\)". Install this on your computer.

/*The below paragraph describes how to make it work only in Visual Studio 6.0, I don't know how to do it Borland*/
Whenever you make a project and wish to make a connection with a database, you have to go to Project -> Settings -> Link. There in the part where it says "Object/library modules:" type this in...

c:\mysql\lib\opt\libmysql.lib

... or wherever the necessary file is located on your hardrive.

When you did that, you have to use the necessary api in order to write into the file and read out of it, here's sample code...

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#if defined __WIN32__ || _MSC_VER
#include <windows.h>
#endif

#include "c:\mysql\include\mysql.h"

void check_error();
void connect_to_database();
void close_connection();
void database_select(char *);
void filmdata_insert();
void filmdata_delete();
void filmdata_change();
void output_all_info();
void search_for_actor(char *);
void print_line(MYSQL_RES *);

MYSQL *mysql;

int main()
{
int selection;

printf("Setting up a connection to the database.........\n\n");
connect_to_database();

database_select("dvd_archive");

do
{
printf("\n-1- Add film information\n");
printf("-2- Delete film information\n");
printf("-3- Change film information\n");
printf("-4- Output all film information\n");
printf("-5- Search for an actor\n");
printf("-6- Exit program\n");
printf("Your selection: ");
scanf("%d", &selection);

switch(selection)
{
case 1:
filmdata_insert();

break;

case 2:
printf("Not yet implemented...\n");

break;

case 3:
printf("Not yet implemented...\n");

break;

case 4:
printf("Not yet implemented...\n");

break;

case 5:
printf("Not yet implemented...\n");

break;

case 6:
printf("The connection to the database is being closed...\n");

break;

default:
printf("Wrong input, please try again\n\n");
}
}while(selection != 6);

close_connection();

return 0;
}

// rudimentary setup functions
void check_error()
{
if(mysql_errno(mysql) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_error(mysql));

exit(0);
}
}

void connect_to_database()
{
mysql = mysql_init(mysql);
check_error();

mysql_real_connect(mysql, "localhost", "root", NULL, NULL, 0, NULL, 0);
check_error();
}

void close_connection()
{
mysql_close(mysql);
}

void database_select(char *db)
{
mysql_select_db(mysql, db);
check_error();
}

// executing functions
void filmdata_insert()
{
char *title = malloc(sizeof(char) * 255);
char *main_role = malloc(sizeof(char) * 255);
unsigned int fsk;
unsigned int released;
int i;
int size = 0;
char *str[9];
char *query;

printf("\nFilm title: ");
scanf("%254s", title);

printf("\nMain role: ");
scanf("%254s", main_role);

printf("\nFSK: ");
scanf("%d", &fsk);

printf("\nReleased: ");
scanf("%d", &released);

str[0] = "INSERT INTO film_info (title, main_role, fsk, released) VALUES ('";
str[1] = malloc(strlen(title) + 1);
strcpy(str[1], title);
str[2] = "','";
str[3] = malloc(strlen(main_role) + 1);
strcpy(str[3], main_role);
str[4] = "','";
str[5] = malloc(sizeof(int) * 3);
sprintf(str[5], "%2d", fsk);
str[6] = "','";
str[7] = malloc(sizeof(int) * 5);
sprintf(str[7], "%4d", released);
str[8] = "');";

for(i = 0; i < 9; i++)
size += strlen(str[i]);

query = (char *)malloc(size + 1);
strcpy(query, str[0]);

for(i = 1; i < 9; i++)
strcat(query, str[i]);

printf("%s", query);

mysql_query(mysql, query);
check_error();

free(title);
free(main_role);

for(i = 1; i <= 7; i += 2)
{
free(str[i]);
}
}

void filmdata_delete()
{
char *del = malloc(sizeof(char) * 255);
char *temp = malloc(sizeof(char) * 255);
char *item[] = {"title", "main_role", "fsk", "released"};
char *ptr;
char *str[5];
char *query = '\0';
int selection;
int i;
int size = 0;
unsigned long affected;

printf("In which criteria would you like to delete\n");
printf("[1] = Title\n[2] = Main Role\n[3] = FSK\n[4] = Date\n");

fgets(temp, 3, stdin);
if((ptr = (char *)strchr(temp, '\n')) != NULL)
{
*ptr = '\0';
}

sscanf(temp, "%d", &selection);

str[0] = "DELETE FROM film_info WHERE ";

if(selection > 0 && selection < 5)
{
str[1] = (char *)malloc(strlen(item[selection - 1]) + 1);
strcpy(str[1], item[selection - 1]);
}
else
{
printf("These criteria do not exist.\n");
return;
}

str[2] = " = ";

printf("Please select \"%s\" you would like to delete: ", item[selection - 1]);
fgets(del, 254, stdin);

if((ptr = (char *)strchr(del, '\n')) != NULL)
*ptr = '\0';

str[3] = (char *)malloc(strlen(del) + 1);
strcpy(str[3], del);
str[4] = "'";

for(i = 0; i < 5; i++)
size += strlen(str[i]);

query = (char *)malloc(size + 1);
strcpy(query, str[0]);
for(i = 1; i < 5; i++)
strcat(query, str[i]);

mysql_query(mysql, query);
check_error();

if((affected = (unsigned long)mysql_affected_rows(mysql)) <= 0)
{
fprintf(stderr, "No such line of information has been found.\n");

check_error();
}
else
printf("From this deletion, %d %s deleted\n\n",
affected, (affected == 1) ? "entry was deleted" : "entries were deleted");

free(del);
free(temp);
free(query);
free(str[3]);
free(str[1]);
}

... the above is far from perfect, but I'm thinking that it will give a feel for what you want to do. Note: I don't consider myself a database expert, but that's something that I whipped up myself.

Also, here are some good tutorials that deal with mysql databases...
http://www.webdevelopersnotes.com/tutorials/sql/1.php3
http://www.juicystudio.com/tutorial/mysql/index.asp
http://www.developerfusion.com/show/3998

[mitch_m]

I plan on allowing people to download the program I am writing and use it on thier pc's. Would using SQL be a smart choice? Do they have to download or intsall anything aside from my program, or do i only have to downlaod extra stuff for development?

[YourSurrogateGod]

To my current knowledge(again I'm not a pro at this, maybe you can make it somehow that the database is built into the program) is that they will have to download the mysql software. Look at mysql.com, there has to be something that allows you to somehow to build in the database.

If you're using Visual Studio in this case, then I guess you can take your existing database and turn into a custom resource. Or you might let the people download the 2 files, the executable and the database file. When the program executes, it will write into and read out of the database file. You can easily get the database file by making a backup in the mysql, using this command(this will make a back up all of the databases that you have)...

mysqldump -u root -p -opt -all- databases > my_backup.sql


Also read this, it might be helpful...

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables. See section 13.4.5 LOCK TABLES and UNLOCK TABLES Syntax. See section 13.5.4.2 FLUSH Syntax. You only need a read lock; this allows other threads to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLE is needed to ensure that the all active index pages is written to disk before you start the backup.

Starting from 3.23.56 and 4.0.12 BACKUP TABLE will not allow you to overwrite existing files as this would be a security risk.

If you want to make an SQL level backup of a table, you can use SELECT INTO OUTFILE or BACKUP TABLE. See section 13.1.7 SELECT Syntax. See section 13.5.2.2 BACKUP TABLE Syntax.

Another way to back up a database is to use the mysqldump program or the mysqlhotcopy script. See section 8.7 mysqldump, Dumping Table Structure and Data. See section 8.8 mysqlhotcopy, Copying MySQL Databases and Tables.
Do a full backup of your database:

shell> mysqldump --tab=/path/to/some/dir --opt db_name

or:

shell> mysqlhotcopy db_name /path/to/some/dir

You can also simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files) as long as the server isn't updating anything. The script mysqlhotcopy does use this method. (But note that these methods will not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)
Stop mysqld if it's running, then start it with the --log-bin[=file_name] option. See section 5.8.4 The Binary Log. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

If your MySQL server is a slave, whatever backup method you choose, when you backup your slave's data, you should also backup the `master.info' and `relay-log.info' files which are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also backup the `SQL_LOAD-*' files which may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave will need these files to resume replication of any interrupted LOAD DATA INFILE operations.

If you have to restore something, try to recover your tables using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure (this will work only if you have started MySQL with --log-bin, see section 5.8.4 The Binary Log):

Restore the original mysqldump backup, or binary backup.
Execute the following command to re-run the updates in the binary log:

shell> mysqlbinlog hostname-bin.[0-9]* | mysql
In your case you may want to re-run only certain binlogs, from certain positions (usually you want to re-run all binlogs from the date of the restored backup, possibly excepted some wrong queries). See section 8.5 mysqlbinlog, Executing the queries from a binary log for more information on the mysqlbinlog utility and how to use it. If you are using the update log (which is removed in MySQL 5.0.0) you can execute the content of the update log like this:

shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

ls is used to get all the update log files in the right order.

You can also do selective backups with SELECT * INTO OUTFILE 'file_name' FROM tbl_name and restore with LOAD DATA INFILE 'file_name' REPLACE ... To avoid duplicate records, you need a PRIMARY KEY or a UNIQUE key in the table. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record on a unique key value.

If you get performance problems in making backups on your system, you can solve this by setting up replication and do the backups on the slave instead of on the master. See section 6.1 Introduction to Replication.

If you are using a Veritas filesystem, you can do:

From a client (or Perl), execute: FLUSH TABLES WITH READ LOCK.
From another shell, execute: mount vxfs snapshot.
From the first client, execute: UNLOCK TABLES.
Copy files from snapshot.
Unmount snapshot.

As I said before you can find the above stuff online in mysql.com as well as other stuff. You can also try this theory out by installing mysql and then making a small program that writes in and reads out of mysql(becareful when you use the program that I made, it's not one of my best), enter some data, then make a backup of the specific table or database, take that and your program and try to run it on a different computer that doesn't have mysql and see what happens, if you write stuff into the database and then are able to read out of without a problem, then you're one step closer to you solution :wave: .

Seeing as how this subject is evolving into something more than juse WinAPI, I think it belongs in the database forum.

p.s.: I never really had to pull off what you want to, I've made program that played around with databases, but nothing special.

[raghupathys]

If you want your program to be downloadable from the Web, you definitely should not use a database.

Because
1. Users may not have the database software installed on their machine.
2. If they dont have a database, downloading the database software is not a feasible solution - because typically most database softwatre is bulky ( 10s even 100s of MB), and you have the licensing issues as well.
3. Databases are inherently slow - They need to perform synchronization, transaction management and a whole lot of stuff.( which you dont seem to need )

I would suggest that you use a plain flat file to store your data.
What you could do is, when your application starts up, open the file, read the data and store it in a container ( you could use STL's vector/list/map ) and then close the file. Do whatever manipulations that you would require, and when the user wishes to save, or during application shut down, reopen the file, go through your container, and start writing your data to the file.

There is a certain inefficiency in that you will need to re-write even data which has not changed, but even with that, the performance will still be miles ahead of any database.

If you want to go for a more sophisticated approach, you could go for a sparse file. Parse the file, read whatever data you need, modify whatever data you need to. And whenever you need more space than is available in your sparse file, then you can resize your sparse file.

Hope this helps.

[mitch_m]

Thanks for all of the help everyone, i appreciate it!

raghupathys- Yes, I think I'm going to go with a flat file. I think i figured out a way to do it that will suit my needs without a database. Don't you feel its better to contsantly re-write the data you want to store to the file and clear the buffer, then to hold it in memory during the entire duration of the program (or until the user wishes to save)? if the data becomes large, wouldn't that be killer on sys resources/memory?

Other than that, I still have the problem of the app crashing when i try to write with fstream. I'm going to do some more tests to try and narrow out the problem. I'll post my results later.

[mitch_m]

OK, so fstream works. The problem was the String i was writing to the file was causing an error. I'm declaring strings as LPSTR (win32 API style). When i was writing data, i was trying to concatenate strings.

LPSTR filename = NULL;
filename = name;
strcat(filename, ".txt");

something like that is what is crashing my app. Its the strcat, ctrcpy functions that are doing it. =/

[Bond]

Originally posted by mitch_m
OK, so fstream works. The problem was the String i was writing to the file was causing an error. I'm declaring strings as LPSTR (win32 API style). When i was writing data, i was trying to concatenate strings.

LPSTR filename = NULL;
filename = name;
strcat(filename, ".txt");

something like that is what is crashing my app. Its the strcat, ctrcpy functions that are doing it. =/

Well, I wouldn't say that a LPSTR is "Win32 API style." I would say it is TCHAR style. BTW, the following names are equivalent to your LPSTR:

PSTR
LPTSTR
PTSTR
TCHAR*

and probably some others I'm forgetting about, too!

First of all, if you're going to be unicode/ansi-friendly and use TCHAR's, you should always use the TCHAR functions, too. That means using lstrcat or _tcscat instead of strcat. But, that's not the reason for your error.

When using the strcat family of functions, you must make sure that your destination buffer has enough room to hold the entire concatinated string, including the null terminator. When you assign a string like so:

LPSTR p = "Meatballs"; // Actually, this should be TEXT("Meatballs")

then you have allocated a string of 10 characters (9 plus the NULL). Nothing more than what you need to hold the string is allocated. Therefore, you cannot append anymore characters to this string.

Instead you will need to allocate a buffer large enough to contain the original string, plus the string you wish to append, plus the NULL character:

TCHAR filename[MAX_PATH];
lstrcpyn(filename, name, sizeof(filename)/sizeof(TCHAR));
lstrcat(filename, TEXT(".txt"));

Or, you could use wsprintf or _stprintf to do this:

TCHAR filename[MAX_PATH];
wsprintf(filename, TEXT("%s.txt"), name);

[ADSOFT]

This is how I did it. I downloaded this stuff (http://dev.mysql.com/downloads/mysql/4.0.html). Go to the section labeled windows. From there you should probably select the second option from the top, labeled "Without installer (unzip in C:\)". Install this on your computer.

/*The below paragraph describes how to make it work only in Visual Studio 6.0, I don't know how to do it Borland*/
Whenever you make a project and wish to make a connection with a database, you have to go to Project -> Settings -> Link. There in the part where it says "Object/library modules:" type this in...

c:\mysql\lib\opt\libmysql.lib

... or wherever the necessary file is located on your hardrive.

When you did that, you have to use the necessary api in order to write into the file and read out of it, here's sample code...

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#if defined __WIN32__ || _MSC_VER
#include <windows.h>
#endif

#include "c:\mysql\include\mysql.h"

void check_error();
void connect_to_database();
void close_connection();
void database_select(char *);
void filmdata_insert();
void filmdata_delete();
void filmdata_change();
void output_all_info();
void search_for_actor(char *);
void print_line(MYSQL_RES *);

MYSQL *mysql;

int main()
{
int selection;

printf("Setting up a connection to the database.........\n\n");
connect_to_database();

database_select("dvd_archive");

do
{
printf("\n-1- Add film information\n");
printf("-2- Delete film information\n");
printf("-3- Change film information\n");
printf("-4- Output all film information\n");
printf("-5- Search for an actor\n");
printf("-6- Exit program\n");
printf("Your selection: ");
scanf("%d", &selection);

switch(selection)
{
case 1:
filmdata_insert();

break;

case 2:
printf("Not yet implemented...\n");

break;

case 3:
printf("Not yet implemented...\n");

break;

case 4:
printf("Not yet implemented...\n");

break;

case 5:
printf("Not yet implemented...\n");

break;

case 6:
printf("The connection to the database is being closed...\n");

break;

default:
printf("Wrong input, please try again\n\n");
}
}while(selection != 6);

close_connection();

return 0;
}

// rudimentary setup functions
void check_error()
{
if(mysql_errno(mysql) != 0)
{
fprintf(stderr, "Error: %s\n", mysql_error(mysql));

exit(0);
}
}

void connect_to_database()
{
mysql = mysql_init(mysql);
check_error();

mysql_real_connect(mysql, "localhost", "root", NULL, NULL, 0, NULL, 0);
check_error();
}

void close_connection()
{
mysql_close(mysql);
}

void database_select(char *db)
{
mysql_select_db(mysql, db);
check_error();
}

// executing functions
void filmdata_insert()
{
char *title = malloc(sizeof(char) * 255);
char *main_role = malloc(sizeof(char) * 255);
unsigned int fsk;
unsigned int released;
int i;
int size = 0;
char *str[9];
char *query;

printf("\nFilm title: ");
scanf("%254s", title);

printf("\nMain role: ");
scanf("%254s", main_role);

printf("\nFSK: ");
scanf("%d", &fsk);

printf("\nReleased: ");
scanf("%d", &released);

str[0] = "INSERT INTO film_info (title, main_role, fsk, released) VALUES ('";
str[1] = malloc(strlen(title) + 1);
strcpy(str[1], title);
str[2] = "','";
str[3] = malloc(strlen(main_role) + 1);
strcpy(str[3], main_role);
str[4] = "','";
str[5] = malloc(sizeof(int) * 3);
sprintf(str[5], "%2d", fsk);
str[6] = "','";
str[7] = malloc(sizeof(int) * 5);
sprintf(str[7], "%4d", released);
str[8] = "');";

for(i = 0; i < 9; i++)
size += strlen(str[i]);

query = (char *)malloc(size + 1);
strcpy(query, str[0]);

for(i = 1; i < 9; i++)
strcat(query, str[i]);

printf("%s", query);

mysql_query(mysql, query);
check_error();

free(title);
free(main_role);

for(i = 1; i <= 7; i += 2)
{
free(str[i]);
}
}

void filmdata_delete()
{
char *del = malloc(sizeof(char) * 255);
char *temp = malloc(sizeof(char) * 255);
char *item[] = {"title", "main_role", "fsk", "released"};
char *ptr;
char *str[5];
char *query = '\0';
int selection;
int i;
int size = 0;
unsigned long affected;

printf("In which criteria would you like to delete\n");
printf("[1] = Title\n[2] = Main Role\n[3] = FSK\n[4] = Date\n");

fgets(temp, 3, stdin);
if((ptr = (char *)strchr(temp, '\n')) != NULL)
{
*ptr = '\0';
}

sscanf(temp, "%d", &selection);

str[0] = "DELETE FROM film_info WHERE ";

if(selection > 0 && selection < 5)
{
str[1] = (char *)malloc(strlen(item[selection - 1]) + 1);
strcpy(str[1], item[selection - 1]);
}
else
{
printf("These criteria do not exist.\n");
return;
}

str[2] = " = ";

printf("Please select \"%s\" you would like to delete: ", item[selection - 1]);
fgets(del, 254, stdin);

if((ptr = (char *)strchr(del, '\n')) != NULL)
*ptr = '\0';

str[3] = (char *)malloc(strlen(del) + 1);
strcpy(str[3], del);
str[4] = "'";

for(i = 0; i < 5; i++)
size += strlen(str[i]);

query = (char *)malloc(size + 1);
strcpy(query, str[0]);
for(i = 1; i < 5; i++)
strcat(query, str[i]);

mysql_query(mysql, query);
check_error();

if((affected = (unsigned long)mysql_affected_rows(mysql)) <= 0)
{
fprintf(stderr, "No such line of information has been found.\n");

check_error();
}
else
printf("From this deletion, %d %s deleted\n\n",
affected, (affected == 1) ? "entry was deleted" : "entries were deleted");

free(del);
free(temp);
free(query);
free(str[3]);
free(str[1]);
}

... the above is far from perfect, but I'm thinking that it will give a feel for what you want to do. Note: I don't consider myself a database expert, but that's something that I whipped up myself.

Also, here are some good tutorials that deal with mysql databases...
http://www.webdevelopersnotes.com/tutorials/sql/1.php3
http://www.juicystudio.com/tutorial/mysql/index.asp
http://www.developerfusion.com/show/3998


Thanks, this technique worked for me but I had to put "libmysql.dll" into the debug directory. I got a message that windows couldn't find that DLL.

??? I don't know what I did wrong during the install, but where would you place this DLL so I can run the program from anywhere?