syntaxerror in mysql INSERT command[solved]

[janpihlgren]

I have written a C-program to Insert INTO a MySQL database.
The program reads data from a textfile into a struct.
The INSERT-statement have to read data from the struct and insert the data into a database.
The INSERT-statement:

Code: Select all

if(mysql_query(conn, "INSERT INTO temp(datum,tid,fukt,temp) VALUES(,term.datum,term.tid,term.fukt,term.temp)")){
{
     fprintf(stderr,"%s\n",mysql_error(conn));
     mysql_close(conn);
     exit(1);
}


The compilation goes OK.
This is the complete program:

Code: Select all

/*************************************************
 * insert__test.c
 * Jan Pihlgren  2016-07-25
 * Kompilering: gcc insert_test.c -L/lib64/mysql -L/usr/lib64 -lmysqlclient -o insert_test
 * Exekvering: ./insert_test
 *
 *************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include </usr/include/mysql/mysql.h>
//#include </usr/include/mysql/my_global.h>

struct tabell {
   char datum[11];
   char tid[6];
   char fukt[5];
   char temp[5];
};

struct tabell term;

int main(int argc, char **argv)
{
  struct tabell term={0};
  struct tabell *ptr;   
  ptr=&term;
 
  FILE *fp;

  MYSQL *conn;
  MYSQL_RES *result;
  MYSQL_ROW row;

  int num_fields;
  int i;

  char *server = "localhost";
  char *user = "root";
  char *password = "password"; /* sätt lösenordet för root först. Ange detta här. */
  char *database = "temperatur";
  char *query = NULL;

 
  int storlek=28;
  int postnr;
  fp=fopen("/home/jan/Development/Data/temp2.txt", "r");
  fseek(fp,SEEK_SET,0);
  fscanf(fp,"%s %s %s %s",term.datum,term.tid,term.fukt,term.temp);


  printf("Datum = %s Tid = %s Fukt = %s Temp = %s\n",term.datum,term.tid,term.fukt,term.temp);
  fclose(fp);
  printf("Datum = %s Tid = %s Fukt = %s Temp = %s\n",term.datum,term.tid,term.fukt,term.temp);

  conn = mysql_init(NULL);
  if(conn == NULL){
     fprintf(stderr, "%s\n",mysql_error(conn));
     exit(1);
  }

  if (mysql_real_connect(conn, server, user, password, database, 0, NULL, 0) == NULL) {
         fprintf(stderr, "%s\n", mysql_error(conn));
         mysql_close(conn);
         exit(1);
  }
 
  if(mysql_query(conn, "INSERT INTO temp(datum,tid,fukt,temp) VALUES(,term.datum,term.tid,term.fukt,term.temp)")){
     fprintf(stderr,"%s\n",mysql_error(conn));
     mysql_close(conn);
     exit(1);
  }
}


I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'term.datum,term.tid,term.fukt,term.temp)' at line 1

[pmithrandir]

I don't see the relation with mageia, but your values start by a comma. Remove it and it will work.
If you have 4 fields, you need 4 values.

Pierre

[janpihlgren]

The problem is solved with a "work a round".

Code: Select all

/*************************************************
 * insert_v4.c
 * Jan Pihlgren  2016-07-27
 * Kompilering: gcc insert_v4.c -L/lib/mysql -L/usr/lib -lmysqlclient -o insert
 * Exekvering: ./insert
 *
 *************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include </usr/include/mysql/mysql.h>

int main(int argc, char **argv)
{
 
  FILE *fp;

  MYSQL *conn;
  MYSQL_RES *result;
  MYSQL_ROW row;

  int num_fields;
  int i;

  char *server = "localhost";
  char *user = "root";
  char *password = "1edinge1"; /* sätt lösenordet för root först. Ange detta här. */
  char *database = "temperatur";
  char *query = NULL;

  char datum[11];
  char tid[6];
  char fukt[5];
  char temp[5];
 
  int storlek=28;
  int postnr;
  char sql[256]="INSERT INTO temp(datum,tid,fukt,temp) VALUES(";
  char sqlquery[256];
 
  conn = mysql_init(NULL);
   
  if (mysql_real_connect(conn, server, user, password, database, 0, NULL, 0) == NULL) {
         fprintf(stderr, "%s\n", mysql_error(conn));
         mysql_close(conn);
         exit(1);
  }
   
  fp=fopen("/home/jan/Development/Data/temp2.txt", "r");
  //fseek(fp,SEEK_SET,0);
  //fscanf(fp,"%s %s %s %s",datum,tid,fukt,temp);


  postnr=0;
  while (!feof(fp)){
   fseek(fp,0,SEEK_CUR); //offset = 0 för textfiler.
   fscanf(fp,"%s %s %s %s",datum,tid,fukt,temp);
   //printf("postnr=%d Datum = %s Tid = %s Fukt = %s Temp = %s\n",postnr,datum,tid,fukt,temp);
   postnr++;
   strcpy(sqlquery,sql);
   strcat(sqlquery,"'");
   strcat(sqlquery,datum);
   strcat(sqlquery,"'");
   strcat(sqlquery,",'");
   strcat(sqlquery,tid);
   strcat(sqlquery,"',");
   strcat(sqlquery,fukt);
   strcat(sqlquery,",");
   strcat(sqlquery,temp);
   strcat(sqlquery,")");
 
   //printf("sqlquery = %s\n",sqlquery);
   
   if(mysql_query(conn,sqlquery)){
     fprintf(stderr,"%s\n",mysql_error(conn));
     mysql_close(conn);
     exit(1);
   }
  }

  fclose(fp);
  mysql_close(conn);

}