1

As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have them go to the correct database as part of that single call?

BEGIN;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
           VALUES ('Lola', 'High', DEFAULT, DEFAULT, DEFAULT);
  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `charge`, `plea`, `sentence`, `create_time`, `update_time`, `hashID`)
           VALUES (000, 412056888, 0, NULL, NULL, NULL, DEFAULT, DEFAULT, LAST_INSERT_ID());
  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
           VALUES (1, 1, 1985, 34506028, 0, DEFAULT, DEFAULT, LAST_INSERT_ID());

COMMIT;

This might entail, for example, a function that inserts a value to a field only IF that field exists. For example, if the field "create_time" exists in all database.tables, then the value would be inserted in each of them. On the other hand, if "last_name" exists in only one of them, then it would be inserted only in that database.table.

I want a function to store which databases have which tables and use a set of fields that entails a union of all databases. Then when I insert values to fields, without me dictating which database gets what, the function should do that automatically.

Could I create such a function? How would I store it?

Improve this question
2
  • You seem to want to produce dynamic SQL on multiple tables that may share some field names? (note that the fact that these tables could be in different databases, or in a transaction is irrelevant if this is what you're asking for). Would this function update these three tables only? Or the tables mentioned were only to exemplify what you want? Would you use as many parameters for the function as you need for inserting fields in your tables?
    – Ezequiel Tolnay
    Apr 18, 2016 at 3:10
  • Ah yes! I'd forgotten about stored procedures. 1, The tables mentioned are only to exemplify what I want. 2. The use of various databases is simply part of an experiment with an additional security measure (also using encryption, etc.). I'm not sure what values to use in my call for update time (datetime) and hashID. DEFAULT throws an error, even though it works fine in regular inserts.
    – Lori
    Apr 18, 2016 at 15:14

1 Answer 1

Reset to default
2

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. Is this what you're looking for?

delimiter //
CREATE PROCEDURE yourdb.yourprocname (_first_name varchar(100), _last_name varchar(100), _create_time datetime, _update_time datetime,  _hash_id varchar(32),
  _id_status int, _id int, _criminal_status int, _day int, _month int, _year int, _state_id int, _state_id_status int)
begin
  START TRANSACTION;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
    VALUES (_first_name, _last_name, _create_time, _update_time, _hash_id);

  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, _create_time, _update_time, LAST_INSERT_ID());

  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_day, _month, _year, _state_id, _state_id_status, _create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Example call:

call yourdb.yourprocname ('Lola', 'High', CURRENT_TIMESTAMP, ... etc)
Improve this answer
2
  • It is a bit unusual to have a proc in one db (yourdb) that plays with 3 other dbs (ENTITY, ...).
    – Rick James
    Apr 18, 2016 at 5:24
  • Yes... however, we are experimenting with additional security measures that require multiple databases. :)
    – Lori
    Apr 18, 2016 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.