wiki:MySQLStoredProcedureAPI

US3 MySQL Stored Procedures API

Stored Functions and Procedures

General

check_GUID check_user
check_user_email last_errno
last_error last_insert_id
validate_user verify_user
verify_userlevel verify_user_email
timestamp2UTC

Information about People

count_people delete_person
get_people get_personID_from_GUID
get_person_info get_user_info
new_person update_person

Information about Buffers

add_buffer_component count_buffers
delete_buffer delete_buffer_components
get_bufferID get_buffer_desc
get_buffer_info new_buffer
update_buffer verify_buffer_permission


get_buffer_component_desc get_buffer_component_info
get_buffer_components


count_spectrum delete_spectrum
get_spectrum new_spectrum
update_spectrum verify_componentID

Information about Analytes

count_analytes delete_analyte
get_analyteID get_analyte_desc
get_analyte_info new_analyte
update_analyte verify_analyte_permission


count_spectrum delete_spectrum
get_spectrum new_spectrum
update_spectrum verify_componentID


get_nucleotide_info set_nucleotide_info

Information about Experiments

count_experiments delete_experiment
get_experiment_desc get_experiment_info
get_experiment_info_by_runID new_experiment
update_experiment verify_experiment_permission
verify_operator_permission delete_HPCRequest


count_projects delete_project
get_project_desc get_project_info
get_projectID_from_GUID new_project, new_project2
update_project, update_project2 verify_project_permission


count_solutions count_solutions_by_experiment
delete_solution delete_experiment_solutions
delete_solutionAnalytes get_solutionAnalyte
get_solutionBuffer get_solution
get_solutionIDs get_solutionID_from_GUID
new_experiment_solution new_solution
new_solutionAnalyte new_solutionBuffer
update_solution verify_solution_permission
all_solutionIDs


new_cell_experiment all_cell_experiments
delete_cell_experiments

Information about Experiment Data

get_rawDataIDs get_rawDataID_from_GUID
count_rawData count_rawData_by_experiment
delete_rawData download_aucData
get_rawData new_rawData
upload_aucData all_rawDataIDs


get_editedDataIDs count_editedData
count_editedData_by_rawData delete_editedData
download_editData get_editedData
new_editedData update_editedData
upload_editData verify_editData_permission
all_editedDataIDs get_editID

Information about Models

count_models count_models_by_editID
delete_model get_modelID
get_model_desc get_model_desc_by_editID
get_model_info new_model
update_model verify_model_permission


count_noise count_noise_by_editID
delete_noise get_noiseID
get_noise_desc get_noise_desc_by_editID
get_noise_info new_noise
update_noise verify_noise_permission

Information about Reports

Information associated with a global report structure

count_reports delete_report
get_reportID get_reportID_by_runID
get_report_desc get_report_desc_by_runID
get_report_info get_report_info_by_runID
new_report update_report
verify_report_permission


Report information associated with a particular runID / triple

count_reportTriple delete_reportTriple
get_reportTripleID get_reportTriple_desc
get_reportTriple_info new_reportTriple
update_reportTriple


Information associated with an individual report document

count_reportDocument delete_reportDocument
get_reportDocumentID get_reportDocument_desc
get_reportDocument_info new_reportDocument
update_reportDocument upload_reportContents
download_reportContents

Information about Hardware

add_rotor_calibration count_rotor_calibrations
delete_rotor_calibration get_rotor_calibration_profiles
get_rotor_calibration_info get_rotorcalibrationID_from_GUID
replace_rotor_calibration count_calibration_experiments


add_rotor count_rotors
get_abstractRotorID_from_GUID get_abstractRotor_info
get_abstractRotor_names get_rotor_names
get_rotor_info get_rotorID_from_GUID
delete_rotor


add_lab count_labs
get_lab_names get_lab_info
get_labID_from_GUID


add_instrument count_instruments
get_instrument_names get_instrument_info
get_operator_names


get_abstractCenterpiece_names
get_abstractCenterpiece_info


US3 database users are required to use the API---MySQL SELECTs, UPDATEs and INSERTs are not supported for general use. A good first place to begin might be the validate_user() function. This function will authenticate the user against the database using either an email address or a GUID, and set some user-defined variables to streamline database access. Additionally, validate_user() returns both the current email and GUID if the login was successful. Other procedures and functions call appropriate verification routines as necessary.

Many US3 procedures use MySQL's support for multiple queries to return multiple result sets. For procedures that do this, the first result is a status code and the second result is the data itself, if any. If everything went ok, the status code is zero. Non-zero status codes are the same ones as defined in last_errno(), or the user may choose to call the last_error() function for a text explanation on any non-zero status. Documentation on the individual procedures describes status codes they return. Programming environments that support multiple MySQL result sets include PHP using the MySQLi extension, MySQL++, and the MySQL C API. Check out a couple of examples below!

The test database info is as follows:

For mysql, the user only has execute permission and must log in with a secure (ssl) connection.

  • userid: us3secure
  • pw: us3

The internal userid and pw for the stored procedures are specific for each user.

Sample grant file

Sample data file

A PHP example (procedural method)

A PHP example (class method)

A C++ example

Return to main page

Last modified 6 years ago Last modified on Jun 21, 2012 5:06:36 PM