Im not sure, but isn’t “SQLite3DSN” just the driver? You’d need to set up an actual database first. And then set parameters to connect to that specific database (as you could have multiple SQLite databases on your system).
Jonathan ,
Thanks you for your comment. SQLite3DSN is the data source name. I do have an sqlite database setup. Its name is at the end of the line: Database=C:Users\nasad\Documents\KiCad\DB_Files\EPC_Db.sqlite
I can view the database using the windows program, DB Browser. I made it from my Altium database.
Regards,
Nasa
cioma, thank you. I have been using that link as a template for my .kicad_dbl.
I have confidence in my .kicad_dbl file. Claudio.Lorini also has some example files on github.
I’m new to posting so I don’t know how to get the vertical & horizontal sliders so the lines can continue.
I’ve managed to get it to work on Windows, but I can’t provide you with much assistance I’m afraid (insert “I have no idea what I’m doing” meme here).
Let’s compare against my config file:
"name": "My Database Library",
"description": "My Database Library Description",
"source": {
"type": "odbc",
"dsn": "",
"username": "root",
"password": "",
"timeout_seconds": 2,
"connection_string": "dsn=KicadDb"
So, I assume you have no username/password set up for your db? I only have “root” as username and blank pw (safety ftw :)).
But I guess the problem lies with the “dsn” and “connection_string” lines. Note I didn’t specify the db path in the connection string, only the dsn name I’ve set up just like you did.
I’m coming late to this party. 
I have my own SQLite database system (originally written for gEDA), and it was working well with v5, but then for v6, I was missing some python interface for eeschema.
I am now very curious about the v7 implementation.
Thanks for the effort.
Shouldn’t that be C:\Users…?
Without the leading \ it becomes a relative path from wherever the process is executing.
1 Like
Well, I found a way to test ODBC connections from PowerShell in Windows: link
So here is what I did to test SQLite ODBC connection under Windows 10 x64:
-
Install 64-bit SQLite ODBC driver (sqliteodbc_w64.exe): link
-
Create a PowerShell script named “test_odbc_sqlite.ps1”:
$conn = New-Object System.Data.Odbc.OdbcConnection("Driver=SQLite3 ODBC Driver;Database=D:\project\kicad\library\kicad.sqlite;")
$conn.open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT * FROM component"
$reader = $cmd.ExecuteReader()
$reader.Read()
$reader[0]
$reader[1]
$reader[2]
$reader[3]
$reader[4]
$reader[5]
$reader[6]
$reader[7]
$reader[8]
$reader[9]
$reader[10]
$reader.Close()
$conn.Close()
- Run this script from PowerShell:
PS C:\Users\cioma> D:\program\database\test_odbc_sqlite.ps1
- Here is the script output:
True
100001
kicad_sym:test
kicad_fp:test
test_test
test
Test
test, test component
Here is my experimental SQLite database:
kicad.sqlite (8 KB)
thank you cioma, I’m going to try it. Thanks for all your help
nasa
cioma, thanks for the script. I modified it for my database. Here is the output for capacitors. Its a beautiful thing & gives me hope.
Thanks so much. This is my 1st time using powershell.
1 Like
Was the first time for me as well 
I noticed that in your script output it shows table column header names, not the content of the first data row (first component). Feel free to share your SQLite database, I can take a look into it under Linux.
1 Like
That’s the problem. I’m looking at it now. I haven’t imported my .csv data into the database correctly.
That’s why KiCad isn’t reading it correctly.
I’d like to figure it out myself so I can get it right. I think we’re on the verge of success.
Thanks so much for your help.
1 Like
Two things: first the path should start with C:\Users for an absolute path, second perhaps the \n in the string after Users is being interpreted as a newline character and is needs to be escaped.
Thank you all for your comments. I still can’t get KiCad to connect to the database.
Thanks to cioma, I was able to test the connection to the database thru Microsoft PowerShell.
I connected to it by specifing the driver and database name:
(sorry I haven’t figured out the horizontal scroll bars)
(“Driver=SQLite3 ODBC Driver;Database=C:\Users\nasad\Documents\KiCad\DB_Files\Test\EPC_Db.sqlite;”)
Then by the DSN name: (“DSN=SQLite3DSN;”)
Since this works in PowerShell, my .kicad_dbl file must not be correct.
Using the driver & database name method:
“name”: “EPC Database”,
“description”: “EPC parts library”,
“source”: {
“type”: “odbc”,
“dsn”: “”,
“username”: “”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “Driver=SQLite3 ODBC Driver;Database=C:\Users\nasad\Documents\KiCad\DB_Files\Test\EPC_Db.sqlite;”
I tried escaping the \n in nasad.
Using DSN method:
“name”: “EPC Database”,
“description”: “EPC parts library”,
“source”: {
“type”: “odbc”,
“dsn”: “”,
“username”: “”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “DSN=SQLite3DSN;”
I tried DSN= in lower case
Putting a semicolon before DSN
made connection string="" and putting the DSN where dsn:“SQLite3DSN”
In all cases I get
Its possible that the rest of the .kicad_dbl file has a mistake in it, but I can’t see it.
Thank you all for your help.
nasa
EPC_Db.sqlite (156 KB)
EPC_DbDriver.kicad_dbl (4.9 KB)
EPC_DbDSN.kicad_dbl (4.8 KB)
maybe your DB tables are missing a primary key?
I’ve been struggling this on macOS too - I’m on an M1 MacBook. I have the QA SQLite database installed and have validated the schema as correct with DBBrowser. I installed the database & ODBC drivers with brew install sqlite and brew install sqliteodbc. Testing the sqliteodbc installation with brew test sqliteodbc reveals the location of the .so to be /opt/homebrew/Cellar/sqliteodbc/0.9998/lib/libsqlite3odbc.so and I have set this in the /opt/homebrew/etc/odbcinst.ini files (as found by odbcinst -j).
Unfortunately, this fails with a database connection failure (and I believe I have tried every combination in the connection string!)
Symbol library 'qa_dblib' failed to load.
Could not load database library: could not connect to database KiCad (/Users/vagrant/kicad/thirdparty/nanodbc/nanodbc/nanodbc.cpp:996: 0100: [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/Cellar/sqliteodbc/0.9998/lib/libsqlite3odbc.so' : file not found )
The missing file is definitely there and the path is correct.
After nearly giving up, I installed the DevArt ODBC drivers as a demo. Just setting the ‘dsn’ details in the .kicad_dbl file resulted in a connection and I could place symbols from the test database.
So there is definitely something slightly flaky here with the normal ODBC drivers & it might be worth trying the DevArt ones.
I will plug away at this a bit more to see if I can produce a more useful bug report.
[Disclaimer] I have absolutely no commercial connection with DevArt.
On M1, you need to make sure to use x86_64 ODBC drivers rather than arm64 ones, as KiCad is not compiled for arm64. I’m not familiar with DevArt but I wonder if that’s the difference.
Yay - that works!
So the SQLite can be ARM native but the SQLiteODBC needs to be x86_64. Didn’t appreciate that as I assumed the SQLite interface was simply an external program - I should have twigged it was a shared object/dylib. Thanks for the hint.
in your dbl files there are some items with extra ‘,’ terminations, for example:
...
{
"column": "Power_Rating",
"name": "Power_Rating",
"visible_on_add": false,
"visible_in_chooser": true,
},
{
"column": "Material",
"name": "Material",
"visible_on_add": false,
"visible_in_chooser": true,
},
{
"column": "MFG",
"name": "MFG",
"visible_on_add": false,
"visible_in_chooser": true,
},
...
Claudio,
Thanks you for your comments. I thought I took out all the extra “,” out as I thought this is not correct.
I guess I missed some. Anyway:
The database now works!!!
Thank you and everyone for your comments & suggestion.
I feel humbled
Regards,
nasa
2 Likes