c insert variable to mysql database beaglebone

Question

Working on a C project where I'm trying to insert values into a mysql database (linux debian on beaglebone black). The code works fine when I insert constants into the database but I cannot figure out how to get variables for date/time and a double number (temperature). Been at it for a week but cannot seem to figure it out so any insight would be appreciated.

All the assorted things I've tried either end up with a compile error, null in database or zeros. I think I'm close... but obviously missing something, probably in the INSERT INTO line?

char buf[LEN];
time_t curtime;
struct tm *loc_time;

curtime = time (NULL);

loc_time = localtime (&curtime);


strftime (buf, LEN, "%Y-%m-%d" " " "%X", loc_time);
fputs (buf, stdout);


MYSQL *con = mysql_init(NULL);

if (con == NULL)
{
   fprintf(stderr, "mysql_init() failed\n");
   exit(1);
}

if (mysql_real_connect(con, "localhost", "user", "pass", "TempDB", 0, NULL, 0) == NULL) 
{
   finish_with_error(con);
}   

if (mysql_query(con, "CREATE TABLE IF NOT EXISTS TempMeas(MeasTime DATETIME, Temp DOUBLE)"))
{
    finish_with_error(con);
}

if (mysql_query(con, "INSERT INTO TempMeas(MeasTime, Temp) VALUES('%d', '%d')", buf, j))
{    
    finish_with_error(con);
}

mysql_close(con);

exit(0);

Gerald Zehetner · Accepted Answer · 2015-04-04 13:24:07Z

1

If you want to insert the actual date and time into the SQL you can do that with sql:

INSERT INTO TempMeas(MeasTime, Temp) VALUES(now(), 'other value');

you get the other values into it with:

#include<string.h>      # needed for sizeof()

/* all the other stuff */

char query1[999];
int num = sprintf(query1, "INSERT INTO TempMeas(MeasTime, Temp) VALUES(now(), '%d');", j);
if (num > sizeof(query1))
{
  printf("Error: Query too long.\n");
  exit (1);
}
if (mysql_query(con, query1))
{
  printf("Error: mysql_query failed.");
  exit (1);
}

Changed so that query has defined size, and checking if the actual query fits in our char query[999]

edited Apr 4, 2015 at 13:24

answered Mar 31, 2015 at 15:03

Gerald Zehetner

6265 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

graceyj20 Over a year ago

Tried using your 3 line code and it compiled but when I ran it it gave an error: "Segmentation fault". I've had this error with some other things I've tried but when I use constants it inserts without problem. Any thoughts?

graceyj20 Over a year ago

Ran gdb and received the following: Program received signal SIGSEGV, Segmentation fault. 0xb6b9d010 in _IO_str_overflow () from /lib/arm-linux-gnueabihf/libc.so.6 Then did backtrace: #0 0xb6b9d010 in _IO_str_overflow () from /lib/arm-linux-gnueabihf/libc.so.6 #1 0xb6b9c252 in _IO_default_xsputn () from /lib/arm-linux-gnueabihf/libc.so.6 #2 0xb6b7b712 in vfprintf () from /lib/arm-linux-gnueabihf/libc.so.6 #3 0xb6b952e2 in vsprintf () from /lib/arm-linux-gnueabihf/libc.so.6 #4 0xb6b82838 in sprintf () from /lib/arm-linux-gnueabihf/libc.so.6 #5 0x000088e0 in main (argc=2, argv=0xbef ...

Gerald Zehetner Over a year ago

Sorry, my fault. Edited post.

graceyj20 Over a year ago

Awesome! Missing a colon & a ) on the new code but once those are in it's perfect! Many many thanks!

Gerald Zehetner Over a year ago

No problem. I found the missing ) but i can't find where a colon is missing.

|

Collectives™ on Stack Overflow

c insert variable to mysql database beaglebone

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related