Mysql odbc string ubuntu full example found

[redowl]

UBUNTU
install mysql, mysql odbc (…)

1. create a database
2. create a file “mysql2.kicad_dbl”
3. add file to kicad library in kidad lib manager

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Creato il: Feb 10, 2024 alle 11:34
-- Versione del server: 8.0.36
-- Versione PHP: 8.2.10-2ubuntu1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `kicad`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `Capacitors`
--

CREATE TABLE `Capacitors` (
  `CA` bigint UNSIGNED NOT NULL,
  `Part_ID` text COLLATE utf8mb4_unicode_ci,
  `Manufacturer` text COLLATE utf8mb4_unicode_ci,
  `MPN` text COLLATE utf8mb4_unicode_ci,
  `Symbols` text COLLATE utf8mb4_unicode_ci,
  `Footprints` text COLLATE utf8mb4_unicode_ci,
  `Description` text COLLATE utf8mb4_unicode_ci,
  `Value` text COLLATE utf8mb4_unicode_ci,
  `Capacitance` text COLLATE utf8mb4_unicode_ci,
  `Voltage_Rating` text COLLATE utf8mb4_unicode_ci,
  `Capacitance_Tolerance` text COLLATE utf8mb4_unicode_ci,
  `Dielectric_Type` text COLLATE utf8mb4_unicode_ci,
  `CasePackage_Size` text COLLATE utf8mb4_unicode_ci,
  `Verified` int DEFAULT '0',
  `Cost` decimal(10,0) DEFAULT NULL,
  `Mass` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dump dei dati per la tabella `Capacitors`
--

INSERT INTO `Capacitors` (`CA`, `Part_ID`, `Manufacturer`, `MPN`, `Symbols`, `Footprints`, `Description`, `Value`, `Capacitance`, `Voltage_Rating`, `Capacitance_Tolerance`, `Dielectric_Type`, `CasePackage_Size`, `Verified`, `Cost`, `Mass`) VALUES
(1, 'CAP-002', 'Murata', 'GRM0335C1E100JA01D', 'Device:C', 'Capacitor_SMD:C_0201_0603Metric', 'CAP CER 10PF 25V C0G/NP0 0201', '${Capacitance}', '10 pF', '25 V', '+/-10%', 'C0G', '0201', 0, 2, 3.6),
(2, 'CAP-003', 'Murata', 'GRM0335C1E100JA01D', 'Device:C', 'Capacitor_SMD:C_0201_0603Metric', 'CAP CER 20PF 25V C0G/NP0 0201', '${Capacitance}', '20 pF', '25 V', '+/-10%', 'C0G', '0201', 0, 2, 3.6);

--
-- Indici per le tabelle scaricate
--

--
-- Indici per le tabelle `Capacitors`
--
ALTER TABLE `Capacitors`
  ADD PRIMARY KEY (`CA`);

--
-- AUTO_INCREMENT per le tabelle scaricate
--

--
-- AUTO_INCREMENT per la tabella `Capacitors`
--
ALTER TABLE `Capacitors`
  MODIFY `CA` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

odbc drive call direct a file (no odbc.ini)
DRIVER={/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
username e password in connection string

mysql2.kicad_dbl
{
    "meta": {
        "version": 0,
        "filename": "mysql2.kicad_dbl"
    },
    "name": "MYDatabase",
    "description": "A database for testing purposes",
    "source": {
        "type": "odbc",
        "dsn": "",
        "username": "",
        "password": "",
        "timeout_seconds": 2,
        "connection_string": "DRIVER={/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so};Server=127.0.0.1;Database=kicad;User=user1;Password=password;Option=3;"
    },
    "libraries": [
        {
            "name": "Capacitors",
            "table": "Capacitors",
            "key": "Part_ID",
            "symbols": "Symbols",
            "footprints": "Footprints",
            "fields": [
                {
                    "column": "CA",
                    "name": "CA",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "Manufacturer",
                    "name": "Manufacturer",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "MPN",
                    "name": "MPN",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                },
                {
                    "column": "Value",
                    "name": "Value",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                },
                {
                    "column": "Dielectric_Type",
                    "name": "Dielectric",
                    "visible_on_add": true,
                    "visible_in_chooser": false
                },
                {
                    "column": "Voltage_Rating",
                    "name": "Voltage Rating",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                },
                {
                    "column": "Cost",
                    "name": "Cost",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                },
                {
                    "column": "Mass",
                    "name": "Mass",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                }
            ],
            "properties": {
                "description": "Description"
            }
        }
    ]
}