As we know, in SQL statements terminate, or should they terminate? by a semicolon ;
.
I mean:
SELECT persona_id, persona_nom FROM persona;
But it turns out that in programming languages, it is very often written like this:
Form A:
$SQL="SELECT persona_id, persona_nom FROM persona";
Here the semicolon at the end is to indicate the end of that line in the programming language, not the SQL statement, which will be sent to the database driver without the semicolon .
Generally the SQL statement works without the semicolon in it .
In my case I try to write like this:
Form B:
$SQL="SELECT persona_id, persona_nom FROM persona;";
Here only the semicolon within the quotes will be sent to the database handler, while the final semicolon will indicate an end of line in the programming language.
My question is if I might get some unexpected result or just the program might crash in some cases when we use way A .
A scenario in which (I think), it could be problematic, would be in the hypothetical case of executing, in successive calls, normal SQL statements and Stored Procedures or functions created by the same user.
I don't know what database engine you currently use, but in SQL Server
Until version 2012, you can create transactions without the final semicolon, it does not affect its operation. However, in new versions (2016) this is obsolete and now it is MANDATORY to end all types of sql transactions with
;
.On this page you can see the functions and statements obsolete with sql server 2016.
In the case of PostgreSQL if you execute a query from the command console, the query is not executed until you put the semicolon to indicate the end of the query.
On the other hand if you write queries inside PgAdmin you may or may not use the semicolon and both forms are equally valid.
your answer to this : To put or not to put ; at the end of an SQL string has or could have any effect? it is YES the programming languages automatically add the semicolon, as the data managers an example of them
PhPMyAdmin, MySqlWorkBench, PhPgAdmin ,PgAmin3
(that I have handled them) also do it; it is enough to execute these instructions directly with the motor and until you read it, it;
will continue waiting for the instruction in the image, I will place the example for youThe key is in succession . The sentences will be sent to the database one by one, so
;
it is not necessary in any case.On the other hand, any modern ORM (or database plug-in) is more than capable of handling all these issues.
The use or non-use of the ";" does not represent a risk. Both forms are valid, as far as I know the use of it was recommended from the first versions of SQL, seeking to prepare programmers for a possible implementation and obligation, which apparently in the SQL 2016 version is declared obsolete. It does not remove the possibility that its use in the future will be mandatory as in programming languages such as Java. Greetings.
In summary: There is no difference between the two forms that you presented because in general that semicolon is being implicitly put. Making form B two results in:
Now depending on what you are doing (and for general knowledge) I recommend that you read about SQL Injection.
https://www.w3schools.com/sql/sql_injection.asp https://es.wikipedia.org/wiki/Inyecci%C3%B3n_SQL
The semicolon is also useful in SQL when you have many instructions in a single batch (triggers, stored procedures, etc) but you can replace it with BEGIN - END which is the same as {} in any other programming language