• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

Tag Archives: Genarating Code

Self generating Simple SQL procedures – MySQL

May 22, 2020 12:22 pm / Leave a Comment / dangerousDBA

As you may know if you have been following my recent posts then I am currently embarking on becoming my own Ishmael and trying to manage Google Cloud Projects!

One of the things that occurred to me is that I do not want to be hand writing a lot of statements (I am up to 33 tables already for the meta data alone) for the administration of the system so I decided to take a break and write some code that will generate that for me.

I am currently looking to use a MySQL database for the backend of the permissions service, and so …..

What does the code do:

In the repo at the moment you will find two files, one for creating simple add (INSERT) stored procedures, and the second for simple get (SELECT) procedures.

The add SELECT statement that generates the add (INSERT) procedure takes in parameters for all the columns in the table and will INSERT them if the unique key(s) on the table are not violated.

The get SELECT statement generates procedures that will get (SELECT) you the primary key for the table based on the unique key of the table.

When you run the SELECT statement for the creation of the simple add or the get stored procedures you will get output of four things:

  1. A DROP PROCEDURE statement
  2. A CREATE PROCEDURE statement
  3. A CALL statement
  4. A SELECT of the out variable as an example / test

How does it do it:

The SELECT statements in the repo use the meta tables found in the information_schema of MySQL. Specifically the tables, key_column_useage and columns table.

There is then a lot of string manipulation and use of sub-selects to get the meta data into a format that could be turned into code for a stored procedure.

GOTCHA: There is one assumption that you can change to suit your needs in that it assumes ALL your unique keys are MySQL UNIQUE INDEX’s and are named like: uix%.

Why do it like this:

At the moment these statements provide the functionality that I need and at some point I can add to some form of user interface, with all the security of stored procedures. I also do not have to worry too much about changing the underlying database structure if the output of the stored procedures remain the same VS a hand coded SQL statement for the future.

Posted in: 2020, GCP, Google, Programming, SQL / Tagged: Genarating Code, MySQL, SQL, Stored Procedures

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Bootcamp colum convert data types DB2 db2 DB2 Administration DB2 Development db2advis db2licm Decompose XML EXPORT GCP Google IBM IBM DB2 LUW idug information centre infosphere IOT LOAD merry christmas and a happy new year Position Tracking python Recursive Query Recursive SQL Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2023 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress