C++

Forum

 
Using C++ to INSERT INTO MySql Database

Animegato88 (6)
Hello all! I'm having trouble getting INSERT INTO to work properly for me. At first, I kept getting the "Unknown column 'myvariablename' in 'field list', but when I changed the string, I ended up getting the error "Duplicate entry ' ' for key 'Primary' ". Also, when I check the data in the table, there is no new data in the database. My infile does pull the data, and I know the data is going into the variables I specified, but the data from the variables aren't going into the database.

This is my first time using C++ to access and enter data into a database, so please bear with me. By the way, sorry this thing is so lengthy. Any help would be GREATLY appreciated.

My code is below:
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 <mysql.h>
#include <iostream>
#include <iomanip>
#include <fstream>

using namespace std;

MYSQL *conn, mysql;
MYSQL_RES *res;
MYSQL_ROW row;

int query_state;

int main()
{
   const char *server="students";
   const char *user="*****";
   const char *password="********";
   const char *database="*****";

   mysql_init(&mysql);
   conn=mysql_real_connect(&mysql, server, user, password, database, 0, 0, 0);
   if(conn==NULL)
   {
       cout<<mysql_error(&mysql)<<endl<<endl;
      return 1;
   }
   query_state=mysql_query(conn, "select * from Instrument");
   if(query_state!=0)
   {
      cout<<mysql_error(conn)<<endl<<endl;
      return 1;
   }
   res=mysql_store_result(conn);
   cout<<"MySQL Tables in mysql database."<<endl<<endl;
   while((row=mysql_fetch_row(res))!=NULL)
   {
      cout<<left;
      cout<<setw(18)<<row[0]
          <<setw(18)<<row[1]
          <<setw(18)<<row[2]
          <<setw(18)<<row[3]<<endl;
   }
   cout<<endl<<endl;
   ifstream infile;
   infile.open("Instrument.txt");
   if(infile.fail())
   {
      cout<<"ERROR. Could not open file!"<<endl;
      return 1;
   }
   infile.seekg(0, infile.end);
   int len=infile.tellg();
   infile.seekg(0, infile.beg);
   string instnum, insttype, maker, year, plID, name, salary, startdate;
   string rating;
   string instrument="INSERT INTO Instrument (InstrumentID, InstrumentType, MakerName, YearMade) VALUES ( '"+instnum+"', '"+insttype+"', '"+maker+"', '"+year+"')";
   string player="INSERT INTO Player (PlayerID, Name, Salary, StartDate) values (plID, name, salary, startdate)";
   string plays="INSERT INTO Plays (InstrumentID, PlayerID, Rating) values (instnum, plID, rating)";
//   mysql_query(conn, "DELETE FROM Instrument WHERE MakerName='maker'");
   while(infile)
   {
      infile>>instnum;
      cout<<instnum<<endl;
      infile>>insttype;
      cout<<insttype<<endl;
      infile>>maker;
      cout<<maker<<endl;
      infile>>year;
      cout<<year<<endl;
      query_state=mysql_query(conn, instrument.c_str());
   }

   if(query_state!=0)
   {
      cout<<mysql_error(conn)<<endl<<endl;
      return 1;
   }
   cout<<endl<<endl;

   mysql_free_result(res);
   mysql_close(conn);

   return 0;
Last edited on
norm b (337)
Think about what the string instrument contains at line 71.

Also, shouldn't line 28 have "show tables" instead of "select * from Instrument".
BHX (3450)
Now, SHOW TABLES just lists all of them. SELECT * <Table> (Instrument in this case) will will return a list of everything in Instruments.

For example, just logging into MySQL and doing SHOW DATASES will print all the Databases in mysql. SHOW TABLES will show all the tables in the database. SELECT * FROM will list all the data stored in the tables.

SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| local              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
6 rows in set (0.31 sec)

*I did USE local;*
SHOW TABLES;

mysql> USE local
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_local |
+-----------------+
| website         |
+-----------------+
1 row in set (0.00 sec)

SELECT * FROM website;

mysql> SELECT * FROM website;
+----+------------+----------+-----------------------------------+------------+
| id | date       | time     | comments                          | username   |
+----+------------+----------+-----------------------------------+------------+
|  1 | 2013-05-16 | 01:53:00 | This is a comment by yours truly! | BHXSpecter |
|  2 | 2013-05-17 | 13:13:00 | COMMENT TESTING!!!!!              | BHXSpecter |
|  3 | 0000-00-00 | 00:00:00 | Comments                          | BHXSpecter |
|  4 | 2013-05-25 | 04:50:00 | I LOVE MY WIFE!!!!!!!!!!!         | BHXSpecter |
+----+------------+----------+-----------------------------------+------------+
4 rows in set (0.00 sec)

* http://prntscr.com/274jxo ** pic of localhost website when I was messing with web design a few months back.
DESCRIBE website;

mysql> DESCRIBE website;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(50) unsigned | NO   | PRI | NULL    | auto_increment |
| date     | date             | YES  |     | NULL    |                |
| time     | time             | YES  |     | NULL    |                |
| comments | text             | YES  |     | NULL    |                |
| username | varchar(50)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.22 sec)


Here is my C++ code that prints out the data from website table
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
 
#include <mysql.h>
#include <cstdio>

int main()
{
	MYSQL *conn;
	MYSQL_RES *res;
	MYSQL_ROW row;
	char *server = "server";
	char *user = "username";
	char *password = "password";
	char *database = "local";
	conn = mysql_init(NULL);
	mysql_real_connect(conn, server, user, password, database, 0, NULL, 0);
	mysql_query(conn, "SELECT * FROM website");
	res = mysql_use_result(conn);
	printf("id \t date\t time \t comments\t\t user\n");
	while ((row = mysql_fetch_row(res)) != NULL)
	{
		printf("%s \t %s\t %s\t %s\t\t %s\n", row[0], row[1], row[2], row[3], row[4]);
	}
	mysql_free_result(res);
	mysql_close(conn);
	
	return 0;
}
Last edited on by closed account z6A9GNh0
Animegato88 (6)
Yeah, I understand how to connect to the server and print tables or show the values in a specific table. The problem I'm having is with adding data to the tables via C++. I know how to insert using sql by itself (and I think I did it using php as well, not sure off hand), but I'm having trouble getting it to work using C++.
norm b (337)
You initialize the string "instruments" outside of your loop with empty strings for values. You never put the data you read from your file into the string.

Try outputting "instruments" just before your mysql_query() call.

@BHXSpecter - the OP's line 35 was the reason for the "show tables" comment:
cout<<"MySQL Tables in mysql database."<<endl<<endl;


Edit: cleaned up sloppy typing.
Last edited on
Animegato88 (6)
Ok, I'm guessing adding the instruments string in the while loop did work, because now, I have some new values in the table. Thank you sooo much! Now, I have an issue of some of the years being 0's instead of the actual years. Here's my currently edited 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
88
89
 
#include <mysql.h>
#include <iostream>
#include <iomanip>
#include <fstream>

using namespace std;

MYSQL *conn, mysql;
MYSQL_RES *res;
MYSQL_ROW row;

int query_state;

int main()
{
   const char *server="students";
   const char *user="*****";
   const char *password="*******";
   const char *database="*****";

   mysql_init(&mysql);
   conn=mysql_real_connect(&mysql, server, user, password, database, 0, 0, 0);
   if(conn==NULL)
   {
       cout<<mysql_error(&mysql)<<endl<<endl;
      return 1;
   }
   query_state=mysql_query(conn, "select * from Instrument");
   if(query_state!=0)
   {
      cout<<mysql_error(conn)<<endl<<endl;
      return 1;
   }
   res=mysql_store_result(conn);
   cout<<"MySQL Values in the Instruments Table."<<endl<<endl;
   while((row=mysql_fetch_row(res))!=NULL)
   {
      cout<<left;
      cout<<setw(18)<<row[0]
          <<setw(18)<<row[1]
          <<setw(18)<<row[2]
          <<setw(18)<<row[3]<<endl;
   }
   cout<<endl<<endl;
   ifstream infile;
   infile.open("Instrument.txt");
   if(infile.fail())
   {
      cout<<"ERROR. Could not open file!"<<endl;
      return 1;
   }
   infile.seekg(0, infile.end);
   int len=infile.tellg();
   infile.seekg(0, infile.beg);
   string rating;
   string player="INSERT INTO Player (PlayerID, Name, Salary, StartDate) values (plID, name, salary, startdate)";
   string plays="INSERT INTO Plays (InstrumentID, PlayerID, Rating) values (instnum, plID, rating)";
//   mysql_query(conn, "DELETE FROM Instrument WHERE MakerName='maker'");
   while(infile)
   {
      string instnum;
      infile>>instnum;
      cout<<instnum<<endl;
      string insttype;
      infile>>insttype;
      cout<<insttype<<endl;
      string maker;
      infile>>maker;
      cout<<maker<<endl;
      string year;
      infile>>year;
      cout<<year<<endl;
      string instrument="INSERT INTO Instrument (InstrumentID, InstrumentType, MakerName, YearMade) VALUES ( '"+instnum+"', '"+insttype+"', '"+maker+"', '"+year+"')";
      query_state=mysql_query(conn, instrument.c_str());
   }

   if(query_state!=0)
   {
      cout<<mysql_error(conn)<<endl<<endl;
      return 1;
   }
   cout<<endl<<endl;

   mysql_free_result(res);
   mysql_close(conn);

   return 0;
}


And here's the output:
MySQL Values in the Instruments Table.

0000
CO543 Cello Amati 0000
CO678 Cello Amati 0000
CT337 Clarinet Amati-Denak 2009
FE489 Flute BooseyandHawkes 1989
FE776 Flute Eloy 1975
p1234 Percussion Kicker 2007
s1234 String Casio 1998
s2345 String Yamaha 2003
VA089 Viola Scott 2008
VA333 Viola Guarneri 0000
VA678 Viola Guarneri 0000
VN456 Violin Viseltear 1990
VN458 Violin Stradivari 0000
VN555 Violin Stradivari 0000
VN888 Violin Amati 0000
w1234 Wind Chyme Central 1994
w2345 Wind Yamaha 2000


VN888
Violin
Amati
1650
VN555
Violin
Stradivari
1720
VN456
Violin
Viseltear
1990
VN458
Violin
Stradivari
1718
VA333
Viola
Guarneri
1664
VA678
Viola
Guarneri
1660
VA089
Viola
Scott
2008
CT337
Clarinet
Amati-Denak
2009
FE489
Flute
BooseyandHawkes
1989
FE776
Flute
Eloy
1975
CO543
Cello
Amati
1550
CO678
Cello
Amati
1550




Duplicate entry '' for key 'PRIMARY'


The correct years are in the txt file, and they are read in properly in infile, but they don't get added to the database (what the heck did I do this time...).

Last edited on
BHX (3450)
My general advice when it comes to doing file i/o is to get the program working with cout statements as it makes it a little easier to get instant results. Then once you have it working with cout statements you can change them to file i/o commands.
norm b (337)
Is the "year" field in your database a YEAR type?
http://dev.mysql.com/doc/refman/5.0/en/year.html


Edit: By the way, you might want to erase your username and password above, just in case . . .
Last edited on
Animegato88 (6)
Thanks for the heads up. I forgot to take that out when I posted it again.

Yes, the field in my database is a YEAR type, and I changed the name of the variables to something completely different, but I still got 0's for most of the inserted values. It's really weird, too, because 5 of the year values got put in, but the others got replaced with 0's. And there's a random set off 0's in there, too. But my infile does print the correct year values. It's really weird.
norm b (337)
Notice from the link in my post above:
As a 4-digit string in the range '1901' to '2155'.

So you're going to have to alter your table, or delete it and re-create it, making YearMade a varchar(4) field in order to handle those pre 1901 years.
mysql> alter table Instrument drop column YearMade;
mysql> alter table Instrument add column YearMade varchar(4);
Animegato88 (6)
OMG I LOVE YOU I LOVE YOU I LOVE YOU!!!! You guys are my heroes! I hate to say it, but this thing took me like a week to figure out, and it was starting to drive me crazy...! Thank you, Norm! Thank you, Specter! You guys are the best!
norm b (337)
You're welcome! Glad you got it working.
BHX (3450)
You're welcome. Though, norm b was more helpful than I was.
Topic archived. No new replies allowed.