MySQL:- Prepared Statements – Insert

Background

Continuing with our work through MySQL, let us touch on how to utilize prepared statements.

 

Database

Table

SQL

SQL – Insert

SQL – Insert – Sample

SQL – Insert – Sample – Single Inserts

    /*
     * Compose Query
    */
    set @queryCourseScheduleInsert 
         = 'INSERT INTO testdb.courseSchedule ( studentID, courseID, calendarYear, calendarQuarter) values (?, ?, ?, ?)';
                                        
    /* Prepare Statement */                                   
    prepare pstmtCourseScheduleInsert from @queryCourseScheduleInsert;
    
    /* Set variables for Insert 01 */
    set @studentID         = '4514';
    set @courseID          = 'CS101';
    set @calendarYear      = 2020;
    set @calendarQuarter   = 3;
    
    /* Execute Prepared Statement */
    execute pstmtCourseScheduleInsert USING @studentID, @courseID, @calendarYear, @calendarQuarter;

    /* Deallocate Prepared Statement */
    deallocate prepare pstmtCourseScheduleInsert;


SQL – Insert – Sample – Multiple Inserts


    /*
     * Compose Query
    */
    set @queryCourseScheduleInsert 
         = 'INSERT INTO testdb.courseSchedule';
     
    set @queryCourseScheduleInsert 
        = concat(@queryCourseScheduleInsert
                    ,' ( studentID, courseID, calendarYear, calendarQuarter) '
                 );
                                       
    
    set @queryCourseScheduleInsert
        = concat(@queryCourseScheduleInsert
                 , ' values (?, ?, ?, ?)'
                );
                                        
    /* Prepare Statement */                                   
    prepare pstmtCourseScheduleInsert from @queryCourseScheduleInsert;
    
    /* Set variables for Insert 01 */
    set @studentID         = '4514';
    set @courseID          = 'CS101';
    set @calendarYear      = 2020;
    set @calendarQuarter   = 3;
    
    /* Execute Prepared Statement */
    execute pstmtCourseScheduleInsert USING @studentID, @courseID, @calendarYear, @calendarQuarter;

    /* Set variables for Insert 02 */   
    set @courseID       = 'ENG101';

    execute pstmtCourseScheduleInsert USING @studentID, @courseID, @calendarYear, @calendarQuarter;
  
    /* Set variables for Insert 03 */
    SET @calendarQuarter   = 4;
    SET @courseID          = 'MAT204';

    execute pstmtCourseScheduleInsert USING @studentID, @courseID, @calendarYear, @calendarQuarter;

    /* Set variables for Insert 04 */
    set @courseID       = 'GEO191';

    execute pstmtCourseScheduleInsert USING @studentID, @courseID, @calendarYear, @calendarQuarter;


    /* Deallocate Prepared Statement */
    deallocate prepare pstmtCourseScheduleInsert;
    

Code Sharing

Github

Gist

DanielAdeniji/MySQL.preparedStatements.insert.sql
Link

 

Summary

MySQL’s implementation of prepared statements is easy to approach and use.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s