Creating an ODBC DSN for connecting to SQLite database

We know now how  to download an ODBC driver for SQLite Databases.

The SQLite ODBC for Win64 installation from the above link installs the ODBC driver and is immediately available in the Windows ODBC Data Source Administrator(x64) as seen earlier and shown here in the Drivers' tab.

SQLiteDSN_0

You can invoke the above window by clicking it in the search charm as shown.

SQLiteDSN_1

Let us now create a DSN for the SQLite database we created earlier. We will be using the database created in an earlier post  albeit it has just one row of data.

In the ODBC Data Source Administrator (64-bit) click Add... with the User DSN highlighted.

The Create New Data Source window opens as shown.

SQLiteDSN_2

Highlight SQlite3 ODBC Driver and click Finish.

The SQLite3 ODBC Configuration window opens as shown.

SQLiteDSN_3.png

We will create a DSN for the database aloha.sqlite persisted at this location.
C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha.sqlite

For Data Source Name enter: flowerDSN  (you can use any other)

For Database Name we will use the Browse...button and browse to this file.

C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha.sqlite

After entering these as shown click OK.

SQLiteDSN_4.png

With no more windows to open the DSN (flowerDSN) gets created as shown.

SQLite_5

I also have another User DSN called ODBCSQLite.

If I highlight and click Configure..., the Configuration window  opens as shown.

SQLite_6

The ODBC Data Source Administrator does not have a testing link for this, but the above DSN's have been verified to work.

Querying a persisted SQLite using Python 3.7

Python uses the sqlite3 module written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

I launch the Python Shell in my All Programs.

PythonSQLite_00

Python 3.7.0b2 (v3.7.0b2:b0ef5c979b, Feb 28 2018, 01:32:48) [MSC v.1912 32 bit (Intel)] on win32

Type "copyright", "credits" or "license()" for more information.

Connecting to an existing SQLite database. UsersOwnerApr25.sqlite has a table called 'friends' with just one row of data.

 First you need to import sqlite3 and then establish a connection by providing the path to the database (note the direction of slashes).

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

>>> import sqlite3

>>> conn=sqlite3.connect("C:/Users/Owner/Desktop/Blog2017/MSSS2017/SQLite3_DBS/UsersOwnerApr25.sqlite")

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

After connecting create a cursor and the subsequently use the Execute() method of the cursor.

>>> c=conn.cursor()

>>> conn.commit()

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

For example, for inserting data into 'friends' table use the following statement. I already had a table with the name 'friends'

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

>>> c.execute("Insert Into friends values('Jay', 'Kris',45)")

--This is something I need to figure out---

I try to print the results but I get the same response after the print() statement.

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

>>> y= c.execute('Select * from friends')

>>> print (y)

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

Now I modify the print() statement and I succeed in printing the first row using the fetchone()method.

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

>>> print(c.fetchone())

('John', 'Chiavetta', 35)

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

I print all the rows using the fetchall().

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

>>> for row in c.execute('Select * from friends'):

print(row)

('John', 'Chiavetta', 35)

('Jay', 'Kris', 45)

>>>

-------

I have not figured out why I get this:

The IDLE interface is very useful as mentioned here. You get a lot of help.

ODBC Driver for the SQLite Databases x32bit and x64bit

The problem of creating an UWP with SQLite is still not settled.

Probably the SQLite ODBC driver will make way for more extensive use of this zero-configuration database. Now ODBC drivers are available to developers as posted earlier.

Please go to this site  to download your ODBC driver.

The current version are as described in my previous post. Download the one appropriate for you use:

LiteODBC_00

I downloaded the one shown here,

LiteODBC_0

You double click the executable show above to begin the installation.

Follow the wizard. Here are some screens shown for your guidance.

LiteODBC_1

LiteODBC_2

I chose the default folder.

LiteODBC_3

LiteODBC_4

As you may know Windows OS has two ODBC Data Source (x32) and (x64). You can verify that you find the driver in the (x64) version as shown here:

LiteODBC_5

Now you start using SQLite.

In the Install directory (in my case: C:\Program Files\SQLite ODBC Driver for Win64), you can find the ODBC Drivers for two versions,
SQLite 2.8.17 
 SQLite 3.22.0 .

April 3 Power BI Update available --> Download and Test Drive

You can get this new update from Windows Store as well. If you cannot access, you can get it from a link (the Desktop Download button) on this site.


April update has all of the following:

Reporting
Q&A Explorer (preview)
Buttons to trigger actions
Combo chart line styles
Improved default sort for visuals
Numeric Slicer now generally available

Analytics
Update your linguistic schema
New DAX function: COMBINEVALUES()

Custom visuals
Organizational visuals now generally available
Overview by CloudScope
Icon Map by Altius
Hexbin Scatterplot

Data connectivity
Adobe Analytics connector now generally available
SAP HANA connector improvement: Port selection
Dynamics 365 Business Central connector

Watch this video for the updated features:

Problem connecting to SQLite from a UWP app is still not solved

SQLite is the right relational database with zero configuration for mobile apps. There is a recipe in Microsoft documentation on using a SQLite database in a UWP app. This should be quite straight forward given a recipe but there are problems. Probably because of a plethora of versions of just about everything.

This post is not about a successful follow up of the recipe to its successful end, but steps in the way of achieving, trying to document as much as possible, firstly to find where things did not happen as expected, secondly to make sure it works, at least for me.

The computer is Windows 10 Version 1803, build 17133.73

SQLite for Universal Windows Platform added via Extensions and Update

The Solution for the app, UWPDataTest:

Universal Windows project-UWPDataTest

DataAccessLibrary -DataAccesLibrary

UWPDataTestPost_0

Microsoft.NETCore.UniversalWindowsPlatform used in the DataAccessLibrary(version 6.0.8)

UWPDataTestPost_2

Installed Microsoft.NETCore.UniversalWindowsPlatform

UWPDataTestPost_2a

DataAccessLibrary referenced in UWPDataTest

UWPDataTestPost_3

Using DataAccessLibrary in MainPage.xaml.cs

UWPDataTestPost_4

Using DataAccessLibrary in app.xaml.cs

UWPDataTestPost_5

DataAccess class made static.

UWPDataTestPost_6

Cannot add using Microsoft.Data.SQLite to class library. Data is not an option. If you add it and try to build you get build errors.

UWPDataTestPost_7

Build errors: DataAccessLibrary.dll could not be found. The DataAccessLibrary build has errors.

UWPDataTestPost_8.png

Error persists after upgrading the SQLite version from within VS Studio 2017.

UWPDataTestPost_9.png

Microsoft Visual Studio 2017 Community edition used.

UWPDataTestPost_10.png

I am not sure where the sqliteSample.db is in the computer. It does not seem to be present anywhere, although search is not guaranteed to work always.

UWPDataTestPost_11.png

The problem at present seems to be related to the absence of SQLite on the computer. Perhaps there is an independent source for the database.

Another possible source of error could be the Insiders Preview build may not support the SQLite added.

In VS2015 I did not face any problem, https://hodentekmsss.blogspot.com/2017/03/querying-sqlite-database-using-rsqlite.html