Saturday, April 21, 2018

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 .

Thursday, April 19, 2018

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:

Sunday, April 15, 2018

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.


Tuesday, April 10, 2018

Get the new version of SQLite

A new version of SQLite is available

SQLite 64-bit is an in-process library that provides you with a zero config, transactional database engine.

The latest version, SQLite 3.23.0(x64) is available for download from here:
https://www.filehorse.com/download-sqlite-64/SQLite

Features of SQLite from the above link listed here:

SQLite Features:
  • Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
  • Zero-configuration - no setup or administration needed.
  • Full SQL implementation with advanced features like partial indexes and common table expressions. (Omitted features)
  • A complete database is stored in a single cross-platform disk file. Great for use as an application file format.
  • Supports terabyte-sized databases and gigabyte-sized strings and blobs. (See limits.html.)
  • Small code footprint: less than 500KiB fully configured or much less with optional features omitted.
  • Simple, easy to use API.
  • Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately.
  • Well-commented source code with 100% branch test coverage.
  • Available as a single ANSI-C source-code file that is easy to compile and hence is easy to add into a larger project.
  • Self-contained: no external dependencies.
  • Cross-platform: Android, *BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems.
  • Sources are in the public domain. Use for any purpose.
  • Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.


However if you are a Visual Studio user, you can get the SQLite for Universal Windows Platform from the Visual Studio Marketplace that you can access from within Microsoft Visual Studio 2017 Community as shown here.


Get to review all the SQLite posts on this blog here.

Tuesday, April 3, 2018

For repeatability, TableSample with Repeatable option with a repeat-seed is better

TableSample returns approximately the rows specified with random values for each of physical 8KB page in a table. The returned rows are variable from zero to a value even higher than specified rows or % of rows. It all depends on how evenly data is distributed on the pages.


TableSampleRepeat_0

The REPEATBALE option results in selected sample returned gain. The repeat_seed must be an integer greater then 0.

With Repeatable option specified with the same repeat_seed value, SQL Server will typically returns the same subset of rows as long as no changes have been made to table.

With repeatable(10) option

10 rows out of 209 row is shown in the above.
With REPEATABLE option, the same rows are returned again and again for each run.




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 o...