Comments - Issue with INSERT data not being available for immediate SELECT

5 years, 7 months ago Matt Cole
This comment has the status of 'removed' and can only be seen by you.

For the record, here's my code:

int save_to_database(char *a, char *b, my_ulonglong &insert_id) {
  const char *insert_str = "INSERT INTO table_a (a, b) VALUES (?, ?)";
  MYSQL_STMT *stmt;
  MYSQL_BIND bind[2];
  int retval;
  unsigned long buffer_length[2];
  my_bool is_null[2];

  stmt = mysql_stmt_init(boinc_db.mysql);
  if(!stmt) {
    // Print an error, return -1
  }

  if(mysql_stmt_prepare(stmt, insert_str, strlen(insert_str))) {
    // Print an error, close stmt, return -1
  }

  memset(bind, 0, sizeof(bind));

  buffer_length[0] = strlen(c);
  buffer_length[1] = strlen(b);

  is_null[0] = 0;
  is_null[1] = 0;

  bind[0].buffer_type = MYSQL_TYPE_STRING;
  bind[0].buffer = c;
  bind[0].buffer_length = buffer_length[0];
  bind[0].length = &buffer_length[0];
  bind[0].is_null = &is_null[0];
  bind[0].is_unsigned = 0;

  bind[1].buffer_type = MYSQL_TYPE_STRING;
  bind[1].buffer = b;
  bind[1].buffer_length = buffer_length[1];
  bind[1].length = &buffer_length[1];
  bind[1].is_null = &is_null[1];
  bind[1].is_unsigned = 0;

  if(mysql_stmt_bind_param(stmt, bind)) {
    // Print an error, close stmt, return -1
  }

  if(mysql_stmt_execute(stmt)) {
    // Print an error, close stmt, return -1
  }

  insert_id = mysql_stmt_insert_id(stmt);

  mysql_stmt_close(stmt);
  return 0;
}

int generate_matches(my_ulonglong insert_id, char *a) {
  const char *insert_str = "INSERT INTO table_b (table_a_id_1, table_a_id_2) SELECT ?, id FROM table_a WHERE a = ? AND id != ?";

  MYSQL_STMT *stmt;
  MYSQL_BIND bind[3];
  int retval;
  unsigned long buffer_length[3];
  my_bool is_null[3];

  stmt = mysql_stmt_init(boinc_db.mysql);
  if(!stmt) {
    // Print an error, return -1
  }

  if(mysql_stmt_prepare(stmt, insert_str, strlen(insert_str))) {
    // Print an error, close stmt, return -1
  }

  memset(bind, 0, sizeof(bind));

  buffer_length[0] = sizeof(insert_id);
  buffer_length[1] = strlen(a) + 1;
  buffer_length[2] = sizeof(insert_id);

  is_null[0] = 0;
  is_null[1] = 0;
  is_null[2] = 0;

  bind[0].buffer_type = MYSQL_TYPE_LONGLONG;
  bind[0].buffer = &insert_id;
  bind[0].buffer_length = buffer_length[0];
  bind[0].length = &buffer_length[0];
  bind[0].is_null = &is_null[0];
  bind[0].is_unsigned = 1;

  bind[1].buffer_type = MYSQL_TYPE_STRING;
  bind[1].buffer = a;
  bind[1].buffer_length = buffer_length[1];
  bind[1].length = &buffer_length[1];
  bind[1].is_null = &is_null[1];
  bind[1].is_unsigned = 0;

  bind[2].buffer_type = MYSQL_TYPE_LONGLONG;
  bind[2].buffer = &insert_id;
  bind[2].buffer_length = buffer_length[2];
  bind[2].length = &buffer_length[2];
  bind[2].is_null = &is_null[2];
  bind[2].is_unsigned = 1;

  if(mysql_stmt_bind_param(stmt, bind)) {
    // print an error, close stmt, return -1
  }

  if(mysql_stmt_execute(stmt)) {
    // Print an error, close stmt, return -1
  }

  mysql_stmt_close(stmt);

  return 0;
}

And then later on in my program, I have:

      if(save_to_database(b, c, insert_id)) {
        // Print an error, rollback, turn autocommit on, return -1
      }

      if(generate_matches(insert_id, c)) {
        // Print an error, rollback, turn autocommit on, return -1
      }

Nothing here is generating errors. It's just that the code in generate_matches doesn't insert anything.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.