PlaceOweb Dotclear - Mot-clé - sql

mysql fonction pour supprimer les balises html

PlaceOweb — Sat, 08 May 2021 18:00:00 +0200

J'ai eu le même besoins que How to remove html tags except <br> from mysql data?, Remove HTML tags from record et SQL - Remove all HTML tags in a string

DELIMITER mydelimiter CREATE FUNCTION `strip_tags`($str text) RETURNS text DETERMINISTIC BEGIN DECLARE $start, $end INT DEFAULT 1; LOOP SET $start = LOCATE("<", $str, $start); IF (!$start) THEN RETURN $str; END IF; SET $end = LOCATE(">", $str, $start); IF (!$end) THEN SET $end = $start; END IF; SET... Lire mysql fonction pour supprimer les balises html

Data Types SQL langages de programmation INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

PlaceOweb — Sat, 05 Sep 2020 11:35:00 +0200

Quels sont les types SQL et des langages de programmation JAVA, PHP, Javascript, ...

Types MySQL Types MySQL (par défaut) Taille (en octet) Valeur min Valeur max JAVA TINYINT tinyint(4) 1 -128 127 byte : (1 octet ) entiers compris entre -128 et +127 (-2^7 et 2^7-1) TINYINT (unsigned) tinyint(3) UNSIGNED 1 0 255 SMALLINT smallint(6) 2 -32.768 32.767 short : (2 octets ) entiers compris entre -32768 et +32767 (-2^15 et... Lire Data Types SQL langages de programmation INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Node.js

PlaceOweb — Sat, 21 Dec 2019 19:23:00 +0100

Node.js JavaScript runtime

Installation de Node.js Lors de l'installation de Node.js, vous aurez les binaires : npm : Node Package Manager, le gestionnaire de packets pour Node. Le site npm pour chercher des packages node : le binaire de Node $ node -v v12.18.3 v14.9.0 $ npm -v 6.14.6 6.14.8 Download for Windows (x64) 14.15.0 LTS : Recommended For Most... Lire Node.js

Android SQlite

PlaceOweb — Sat, 05 Nov 2016 23:43:00 +0100

Android SQlite

Android SQLite database and content provider - Tutorial It is good practice to create a separate class per table. Thread Safety. If you work directly with databases and have multiple writers from different threads you may run into concurrency issues. The easiest way is to use the keyword synchronized in front of all methods of the provider, so... Lire Android SQlite

SQL "matching found rows" vs "affected rows"

PlaceOweb — Sat, 05 Nov 2016 20:25:00 +0100

Lors de modifications (UPDATE, DELETE) comment différencier le nombre d'enregistrement qui matche au critère WHERE et le nombre d'enregistrement réellement modifiés par la requête ?

"found (matched) rows" le nombre d'enregistrements trouvés (potentiellement modifiable)
"affected (changed) rows" le nombre d'enregistrements véritablement changés parmi les trouvés

En introduction, la conclusion client MariaDB/MySQL : "affected (changed) rows" par défaut. client SQLite : "found (matched) rows" uniquement ! pas moyen de le changer ?... PHP + MariaDB/MySQL : "affected (changed) rows" par défaut, possibilité de passer en "found (matched) rows" avec l'option PDO::MYSQL_ATTR_FOUND_ROWS => true dans le connecteur... Lire SQL "matching found rows" vs "affected rows"

MariaDB variables

PlaceOweb — Sun, 12 Jun 2016 12:00:00 +0200

Accès aux variables de status, système, session, globales, utilisateur.

Server Status Variables utiliser SHOW STATUS pour voir le status des variables. Server System Variables utiliser SHOW VARIABLES pour lister leur valeur ou le format avec 2 @ : @@event_scheduler User-defined Variables avec un seul @ : @ma_variable -- SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr] SHOW STATUS; -- par défaut SESSION,... Lire MariaDB variables

Réplication MariaDB

PlaceOweb — Sun, 05 Jun 2016 19:48:00 +0200

Petites notes sur la réplication avec MariaDB

Quel serait la meilleure méthode de réplication ? SBR : Statement Based Replication RBR : Row Based Replication MBR ? Mixed Based Replication MariaDB Binary Log Formats Replication and Binary Log Server System Variables replicate_ignore_db mysqld Options --binlog-ignore-db Dynamic Replication Variables SET GLOBAL replicate_ignore_db =... Lire Réplication MariaDB

MariaDB DEFAULT column VALUE

PlaceOweb — Tue, 10 May 2016 21:15:00 +0200

How to retrieve default column value ? Simply using the miscellaneous DEFAULT(col_name) function.

UPDATE my_table SET my_col_1 = DEFAULT(my_col_1), my_field = DEFAULT(my_field) WHERE id = 123... Lire MariaDB DEFAULT column VALUE

Android SQlite affectedRows

PlaceOweb — Sun, 01 May 2016 10:52:00 +0200

Les INSERT/DELETE/UPDATE retourne the number of rows affected, mais pas les bons ! Pour résumer il retourne le nombre de row matching au critère WHERE.

ContentValues values = new ContentValues(); values.put("changed", changed ? "1" : "0"); long affectedRows = database.update("my_table", values, "id = ?", new String[]{Integer.toString(id)}); Sur une clé primaire avec un seul row attendu et existant, cette méthode, retourne toujours 1, que la valeur d’origine soit différente que la valeur... Lire Android SQlite affectedRows

REPLACE INTO vs ON DUPLICATE KEY UPDATE

PlaceOweb — Mon, 04 Apr 2016 00:00:00 +0200

Which (non-standard) statement is better faster?

REPLACE INTO ...
INSERT INTO ... ON DUPLICATE KEY UPDATE ...

REPLACE INTO my_table (col_1, col_2, col_3, col_4) VALUES ('row1_valA', 'row1_valB', 'row1_valC', 'row1_valD'), ('row2_valA', 'row2_valB', 'row2_valC', 'row2_valD'); INSERT INTO my_table (col_1, col_2, col_3, col_4) VALUES ('row1_valA', 'row1_valB', 'row1_valC', 'row1_valD'), ('row2_valA', 'row2_valB', 'row2_valC', 'row2_valD') ON DUPLICATE KEY... Lire REPLACE INTO vs ON DUPLICATE KEY UPDATE

MariaDB Connector/Java You need to set exactly 1 parameters on the prepared statement

PlaceOweb — Sat, 02 Apr 2016 14:42:00 +0200

Exception in thread "main" java.sql.SQLException: You need to set exactly 1 parameters on the prepared statement
Query is : INSERT INTO my_table (my_col) VALUES ('m_val')null;
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: You need to set exactly 1 parameters on the prepared statement
Query is : INSERT INTO my_table (my_col) VALUES ('m_val')null;

After MariaDB Connector/Java (CONJ) version 1.3.6 (since 1.3.7 and 1.4.0), the INSERT INTO my_table (my_col) VALUES ('my_val') failed : Exception in thread "main" java.sql.SQLException: You need to set exactly 1 parameters on the prepared statement Query is : INSERT INTO my_table (my_col) VALUES ('my_val')null at... Lire MariaDB Connector/Java You need to set exactly 1 parameters on the prepared statement

sql occurence char count

PlaceOweb — Tue, 08 Mar 2016 20:33:00 +0100

Comment compter le nombre d’occurrence d'un caractère dans une colonne ?

SET @string = "Bonjour ""Mr Vinal""; il fait beau l'été, l'automne et l'hivers, n'est ce pas ?";
SELECT 
SUM( LENGTH(@string ) ) AS total_length,
SUM( LENGTH(@string) - LENGTH(replace(@string, ',', '')) ) AS coma_virgule, 
SUM( LENGTH(@string) - LENGTH(replace(@string, '''', '')) ) AS quote_cote, 
SUM( LENGTH(@string) - LENGTH(replace(@string, '"', '')) ) AS double_quote_cote, 
SUM( LENGTH(@string) - LENGTH(replace(@string, ';', '')) ) AS semicolon_point_virgule
;
/*
+--------------+--------------+------------+-------------------+-------------------------+
| total_length | coma_virgule | quote_cote | double_quote_cote | semicolon_point_virgule |
+--------------+--------------+------------+-------------------+-------------------------+
|           79 |            2 |          4 |                 2 |                       1 |
+--------------+--------------+------------+-------------------+-------------------------+
*/

D'autres liens : http://stackoverflow.com/questions/... http://stackoverflow.com/questions/... https://docs.oracle.com/cd/B28359_0... http://sqlblog.com/blogs/adam_macha... http://rebustechnologies.com/using-... http://stanbiron.com/2010/07/29/Sim...... Lire sql occurence char count

MariaDB fonctions de date et de temps

PlaceOweb — Mon, 28 Dec 2015 21:21:00 +0100

Comment gérer ses dates avec MariaDB, soustraire des dates, ajouter des intervalles de temps, ...

... extraire les secondes, minutes, jours, semaines, mois, ans...

Au même titre que MySQL fonctions de date et de temps, voici comment gérer les dates et times avec MariaDB, selon Date and Time Functions et Date and Time Data Types MariaDB> SELECT VERSION(), CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, UNIX_TIMESTAMP();... Lire MariaDB fonctions de date et de temps

The last packet successfully received from the server was X milliseconds ago. The last packet sent successfully to the server was Y milliseconds ago.

PlaceOweb — Tue, 01 Dec 2015 06:21:00 +0100

The last packet successfully received from the server was X milliseconds ago. The last packet sent successfully to the server was Y milliseconds ago.

MySQL server has gone away

2015-12-01 09:01:45,010 FATAL (EJB default - 7) class com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 13 973 milliseconds ago. The last packet sent successfully to the server was 3 milliseconds... Lire The last packet successfully received from the server was X milliseconds ago. The last packet sent successfully to the server was Y milliseconds ago.

android.database.Cursor moveToFirst or getCount

PlaceOweb — Mon, 28 Sep 2015 21:44:00 +0200

moveToFirst vs getCount ? Wich is faster on android.database.Cursor ?

It's almost the same time because Cursor.moveToFirst() call SQLiteCursor.getCount(). Look at GrepCode / com.google.android / android android.database.AbstractCursor GrepCode / com.google.android / android / 5.1.1_r1 / android.database.AbstractCursor GrepCode / com.google.android / android / 5.1.1_r1 / android.database.AbstractCursor GrepCode /... Lire android.database.Cursor moveToFirst or getCount

sql_log.sh

PlaceOweb — Sat, 19 Sep 2015 19:15:00 +0200

Petit script pour activer les logs des requêtes SQL (MariaDB, MySQL).

Usage :

./sql_log.sh OFF

./sql_log.sh ON

tail -f /var/lib/mysql/hostname.log

~# vim sql_log.sh #!/bin/bash #echo $1 #echo $* #echo $@ #echo $# # Le nombre d'arguments : $# #echo $# var_on_off=""; if [ $# = 1 ] then if [ $1 = "ON" ] then var_on_off=$1; elif [ $1 = "OFF" ] then var_on_off=$1; else echo "Un seul ARG : ON/OFF";... Lire sql_log.sh

MySQL create command SELECT/INSERT/UPDATE

PlaceOweb — Sun, 18 Jan 2015 23:30:00 +0100

Commandes SQL pour générer des statements SELECT, INSERT, REPLACE, UPDATE de vos tables, dans le but d'avoir les requêtes déjà prêtes avec la liste de vos champs.

/* `information_schema`.`COLUMNS` TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT */ -- SET [SESSION | GLOBAL]... Lire MySQL create command SELECT/INSERT/UPDATE

MariaDB

PlaceOweb — Mon, 31 Mar 2014 11:18:00 +0200

MariaDB remplaçant de MySQL ?

Au 2014-03-31, MariaDB lance sa nouvelle version stable : MariaDB 10.0.10 Les commandes client mysql/mariadb Par exemple pour le lancer sans la complétion et avec demande du mot de passe, mysql --no-auto-rehash -p Pour changer le prompt : MariaDB [(none)]> prompt MariaDB> PROMPT set to 'MariaDB> ' MariaDB> PROMPT \U > root@localhost > Et le... Lire MariaDB

Deadlock MySQL

PlaceOweb — Wed, 25 Dec 2013 19:49:00 +0100

Exception : Deadlock found when trying to get lock; try restarting transaction com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

Exception::getMessage: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

SHOW INNODB STATUS; -- MySQL 5.1 : 'SHOW INNODB STATUS' is deprecated and will be removed in a future release. Please use 'SHOW ENGINE INNODB STATUS' instead SHOW ENGINE INNODB STATUS; -- Consultez la section : ------------------------ LATEST DETECTED DEADLOCK ------------------------ La syntaxe de SHOW INNODB STATUS est un synonyme déprécié... Lire Deadlock MySQL

group http user agent sql

PlaceOweb — Thu, 15 Aug 2013 16:00:00 +0200

MySQL statement to know distinct browser version previoulsy stored in a table from User-Agent header ($_SERVER['HTTP_USER_AGENT'] with PHP for example).

There is no real way to extract browser name and version from browser agent header. Inspired from this links, i propose my query suggest : How should I deal with user agent parsing in logs? (statement with SQL Server) Understanding user-agent strings Internet Explorer user-agent tokens : Version Platform Feature and Trident token T-SQL Function:... Lire group http user agent sql