MySQL - Procedures and Functions with optional parameters

If you use database procedures and functions in MySql or Sql, that receive parameters and that are called from back code (for example, PHP), or from any other language, you will have noticed that, on more than one occasion, if you have decided to add a parameter, you have either created an alternative procedure or function (so that the code already written is not screwed up) or you have had to revise all the back code to add, even if it is empty or null, the parameter in question.

And you may have thought... "SQL could already have, in the style of javascript, php and other languages, the possibility of creating a parameter with a default value in case it is not supplied", that is... an optional parameter. 

For example, we are going to use a very simple procedure that receives two string parameters, returning their values.


  CREATE PROCEDURE localdb.parameters_test(p1 varchar(10),p2 varchar(30) )
  BEGIN
      select p1,p2;
  END


If in this case we do...


	call parameters_test("hello","bye");
    
    
    

    
p1
p2
    
hello
bye
    
   



If we now change the procedure to...



  
  CREATE PROCEDURE localdb.parameters_test(p1 varchar(10),p2 varchar(30),p3 varchar(10) )
  BEGIN
      select p1,p2,p3;
  END
  



But if we do not change the call, we will get an error. 





How can we then use database procedures and functions, where, within their code, we get optional parameters, even of undefined type values? Yes, I know that now the trend is to use data typing at all times, just as from 1998 to 2004 approximately, it was recommended to use Foreign Keys to preserve the integrity of the tables, while later it became fashionable to maintain the integrity of the code and not use the related keys, or Foreign Keys. And now many already use No-SQL to avoid defining a rigid database structure. 

All these types of practices are up to the analyst, who has to take into account whether robust integrity, more agile code development, and a thousand other variables are desired. As an anecdote, I will tell you that many people understood that "now it is not fashionable to create related keys/foreign keys" and ended up not using key indices. You can imagine how many databases I have had to index and reindex because, at a certain volume of data, the query was taking forever. In SQL, please use keys!

That said... to the point.


Optional Parameters in SQL/MySQL

To "simulate" and control parameters in database functions and procedures, we make use of the EXTRACTVALUE command, which is like a "reader" or "interpreter" of XML values. In the following basic example, you will understand it clearly.


  CREATE PROCEDURE localdb.parameters_test(pAll varchar(300))
  BEGIN
      set @p1 = (select ExtractValue(pAll,"p1"));
      set @p2 = (select ExtractValue(pAll,"p2"));
      select @p1,@p2;
  END
  



	Call parameters_test("<p1>Hello</p1><p2>Bye</p2>");
    
    
    
    

    
p1
p2
    
Hello
Bye
    
    


And if we now change the procedure to accept a new parameter, it won't be necessary to change anything in the back (or source or whatever you want to call it) code.


  CREATE PROCEDURE localdb.parameters_test(pAll varchar(300))
  BEGIN
      set @p1 = (select ExtractValue(pAll,"p1"));
      set @p2 = (select ExtractValue(pAll,"p2"));
      set @p3 = (select ExtractValue(pAll,"p3"));
      select @p1,@p2,@p3;
  END
  





	Call parameters_test("<p1>Hello</p1><p2>Bye</p2>");
    
    
    
    

    
p1
p2
p3
    
Hello
Bye
 
    
    


Of course, within the procedure or function, you should check both the type, the value and whether or not the passed parameter exists to make your code understandable and useful. But that is up to you and your team (if any).

Scrum, Sprints, Estimates and Braggarts

And what a topic it is!

Every year, in a strange inheritance of miracle diets, magic formulas that promise to be a revolution appear in the business world, especially in the technology sector. In addition to this, there are those who, by attending a conference, chance discovery or coffee conversation (with or without mystery) with a colleague in a similar position in a larger company, "soak it up" or look it over or create a concept in their head, arrive at the office and say: Eureka!

You have probably heard of, or suffered from, the Scrum method or similar. They are working methods, really applied to any sector, oriented to groups, in principle, with a common project whose tasks are dependent.

In many cases you will attend, or you will have attended, meetings where each professional is really in charge of a sprint (the estimated time for that group of tasks), although for some reason the "specialist" on duty has mixed non-dependent tasks in a sprint, hence it is said that each professional is in charge of a sprint. The meeting goes on forever, as it is really a meeting with those present who are not all involved in the same project or who do not have dependent tasks. 

The company has just fallen victim to a braggart, and you have just suffered the consequences. For that word which the "specialist" heard and which led him to say "Eureka!", has become an absurd method of control and stressful examination of skills.

If you have understood anything at this point, you have suffered. Welcome to the age of technological fanfare.

If an agile methodology such as Scrum is applied correctly, we are in a quagmire if we are talking about services, especially if we are talking about IT development.

One of the bases of this type of methodology is time estimation. And that, in IT development, is a problem.

Let's see what time estimation would be like for a carpenter.

A carpenter is asked to make a chair. With a specific wood, varnish and shape. The carpenter has never made that specific chair. The carpenter is asked the question: when will it be ready? The carpenter has that wood. If he didn't have it, he would have to look for it and talk to suppliers, which would take an indeterminate amount of time. But fortunately for the person involved, he has the wood that has been requested. 

How long will it take? He has worked with that wood, and it takes him 3 days to make a chair. But he does not know the design, so he answers: "6 days". 

The carpenter finds that certain aspects of the design, with that particular wood, are not feasible. He has had to make some tests that have delayed him for a day. In the end he finds that for some elements he needs to change the wood. He writes an email (he is a modern craftsman) to the client who replies a day later, accepting the change of wood in those elements. 

The carpenter, when varnishing with the specified liquid, discovers that the liquid, which he also had, behaves differently on each type of wood. So he needs to change the type of varnish, writing back to the customer. The customer responds within two days, agreeing to the change. 

The carpenter delivers the job after 8 days. In this case he is lucky and the client (or project manager) understands the circumstance. 

The following month, another customer arrives asking for a design very similar to the previous one, with the same wood and the same varnish. The carpenter informs him of the changes that will be necessary, and when asked "when will it be ready?", the carpenter answers 5 days, delivering the work in 3 days. 

Is that understood? Yes, it is. Normally a computer developer does not repeat a task. Moreover, a good computer programmer/analyst will reuse threads of code (classes, traits, etc.), the initial preparation of which will take an indeterminate amount of time for the sake of saving time later on. And with this in mind, what do you think the answer to the question of when it will be there should be?

This is the big problem that a programmer, analyst or any position that needs to make a previous study of an unknown task before the time estimation. Any developer knows that, except for maintenance tasks with localised errors, something you estimate in five days can be done in two hours or a month.

Something that, no doubt, will never be understood by the agile methodologies braggart who uses it as a personnel control and who makes him feel useful with the conviction that, if it were not for him, the company would not be able to move forward. 

There are many types of workers in all companies and sectors. Some bring speed, others excellence, others creativity. A project manager who does not know how to make the most of his team is not doing his job properly. No matter how up to date he or she may be on miracle diets.

CSS and the chaos of Frameworks

Well, let's get straight to the point. Let's get straight to the point. Every time I see the use of css in my clients, it's little different from the old htmls full of "style=", only now we have endless strings of "class=" and sometimes combined with "style=". Of course, we'll probably have an initial Bootstrap load.

And it is a very good help to use CSS frameworks, which will simplify a lot the use of grids, button styles and positions, modal windows, menus... you know. But, if you are going to tackle a new project, think about, even if you use CSS frameworks for certain aspects, creating your own CSS template that addresses styles (colours, boxes, etc) that you are going to use in your project.

Additionally, it's funny how some people still don't make much use of custom tags instead of filling everything with divs and classes. The result will be very similar or similar, but it can help you to better understand the design of your web or webapp, as well as avoid the "overlaps" that make you say "why the hell is he doing this?

On the subject of custom tags, there are people who think that since they have less compatibility than classes, they decide not to use them. Although it is curious that they are integrating in their javascript requirements that only the latest browsers are going to support.

Let's see an example, using Bootstrap as a base.

	<!-- With Bootstrap -->
	<div class="row mt-3">
		<div class="col-4 offset-2 border shadow">
			Hi Bootstrap World
		</div>
	</div>

Now let's see what happens if we use custom tags.

	<!-- With Tags -->
	<style>
      mycard {
        width: 34%;
        border: 1px solid grey;
        padding: 1em;
        display: block;
        margin-left: 16%;
        background-color: white;
        border-radius: 3px;
        box-shadow: 5px 5px 7px rgba(0,0,0,.2);
        margin-top: 1em;
        text-align: center;
	}
   </style>
    
    
	<mycard>
		Hi Tags World
	</mycard>

Wow. Looks like you've got a bit more of a mess. Except... if we have that "mycard" specification in our CSS file and, in ALL our code, we just use our new "mycard" tag.

Play with this code on Codilink

Of course, you can also do this using classes. And a very good practice is that, for each element, box, card, layout you want to use, you use a parent class (or tag) and the internal elements you use "standard" tags.

For example...

	<style>
      .CardClass {
        display: block;
        border: 1px solid grey;
        width: 10em;
        padding: 1em;
      }

      .CardClass p {
        display: block;
        color: black;
        padding: 0;
        font-size: .8em;
        line-height: .8em;
      }

      .CardClass p:nth-child(1) {
        border-bottom: 1px solid black;
        color: red;
        font-size: 1em;
        line-height: 2em;
      }

      .CardClass p:nth-child(3) {
        color: green;
      }
   </style>
    
    
	<div class="CardClass">
		<p>Hi! I'm Red</p>
		<p>And I'm not</p>
		<p>I'm Green</p>
	</div>

Play with this code on Codilink

Apparently more code to write, but... on the one hand you can skip (a bit) the definitions of the framework you are using (if you use it). On the other hand, every time you change your stylesheet, you'll have your whole site updated. And finally, you will have a code with unified styles and much, much, much cleaner.

In short. Use whatever you prefer. But... the less you fill the code that contains the information with styles and THOUSANDS OF CLASSES, you'll be glad when you need to you have to make a modification.

By the way, remember that to force the browser to use your latest version of your stylesheet, simply add a ?v999xx when you link to it. This will force the latest version to load.

Additionally, I recommend that you start using CSS variables, especially for colours, and thus contemplate the increasingly used dark mode or light mode depending on your system configuration.

As a last tip, remember that if something is not doing what it "should", maybe the "css" that your browser adds by default, or that class or tag is already defined by the framework, you can use "all: unset;" to remove everything that has been defined on it.

And although I understand the "laziness" of making custom styles, believe me, you'll be happy. You will be able to have templates to help you in other projects. You will be able to use relative sizes (many frameworks use pixel sizes) and customize as much as you want. And if you also use in development metalanguages like Pass (remember, PLEASE, remove the non-.css extensions from the deployer/publisher of production), then everything will be easier.

HTML, CSS and Javascript Online with Codi.link and others

Although you can do all this locally and with your code editor, there are times when you want to test "live" some code, css, html or javascript alternatives. And it is possible that your code editor supports addons to do all this on the same screen. But there are also many of us developers who don't want to "crash" our editors and resort to online tools, sometimes developed by ourselves, to do this "test area".

To make this easier for you, many publish their own online tools for the benefit of the community (and some for advertising purposes, which is not reprehensible).

CODI.LINK

The first one I recommend is Codi.Link, by spanish youtuber/streamer Miguel Ángel Durán.

Recently created and with the help of its followers, it promises to become one of the best online CSS, Html and Javascript testing grounds.

CODEPEN

One of the best known and most widely used is CodePen. Similar to the previous one and with a multitude of functions.

JS.DO

A test area that I have used (and use) for a long time, as it is simple and with no code separation is simple and without code separation is JS DO by Rodrigo Siqueira.

Online Json Validation and Error Checking

The Json data format allows us to do so many things, every developer, both Backend and Frontend has dealt with a huge Json containing... a bug.

To find that pesky "character", or that occasional composition error, Curious Concept brings us its online utility Json Formarter and Validator.

And although you probably know of other utilities for formatting, querying and even validating Jsons, I have found this simple tool to be the most useful and usable of all the ones I know.

Take a look and let me know.

Json Formarter and Validator Online

MySQL. Error 1031

If you have a MYSQL database with version 5.3 or earlier, and you try to do a transfer to a higher engine, you may find that some table gives you error 1031, and a description similar to "Table storage engine for 'table' doesn't have this option", it is very possible that, when that table was created, it was created with a ROW_FORMAT = FIXED.

And if you are using InnoDB, you should know that it only supports REDUNDANT, COMPACT, DYNAMIC and COMPRESSED formats.

Change the row_format of your table and.... go!