Configuring for SQLite database for Editor

Dr. Tohichi Hikita

Link to test case: Trying to get the sample database working in evaluation copy of editor. Followed steps as given in the installation documenation and went to the /examples/simple/simple.html page but get error below.

Debugger code (debug.datatables.net):

`Table information
Summary information about the DataTables on this page.
Information about 1 table available

example

Data source: Ajax
Processing mode: Client-side
Draws: 2
Columns: 6
Rows - total: 0
Rows - after search: 0
Display start: 0
Display length: 10
Version check
Check to see if your page is running the latest DataTables software.
LibraryInfoInstalledLatest
DataTablesUp to date1.11.41.11.4
AutoFill-2.3.7
ButtonsUp to date2.2.22.2.2
ColReorder-1.5.5
Editor2.0.62.0.6
FixedColumns-4.0.1
FixedHeader-3.2.1
KeyTable-2.6.4
Responsive-2.2.9
RowGroup-1.1.4
RowReorder-1.2.8
Scroller-2.0.5
SearchBuilder-1.3.1
SearchPanes-1.4.0
SelectNightly available1.3.41.3.4
Check for common issues
Run automated tests to check for common and previously reported issues.
15 tests complete. No failures or warnings found!

If you are having problems with your DataTables, please upload a data profile using the Upload option below, and post a support request in DataTables forums, with a link to a page showing the issue so we can help to debug and investigate the issue.
Upload configuration data
Upload your table's configuration and data to allow for further analysis.
It can often greatly assist in debugging a table if we can see the configuration and data in the table. This feature of the debugger will read your local DataTables configuration and upload it to debug.datatables.net for inspection.

Important: The data uploaded to the server can only be viewed by SpryMedia employees will be automatically deleted after two weeks. The data will never be sold or otherwise published. It is used for debugging any issues you are experiencing with DataTables only.Upload configuration`

Error messages shown: DataTables warning: table id=example - SQLSTATE[HY000]: General error: 1 no such table: datatables_demo

Description of problem:

• I am following the instructions given on the installation page (https://editor.datatables.net/manual/php/installing)
• I downloaded a copy of Editor (PHP version)
• Extracted files and copied to server
• Created a SQLite db using the supplied SQL code (https://editor.datatables.net/examples/sql/sqlite.sql) into a new db file
• I have edited the lib/config.php as shown below (changed type to "Sqlite" and added the filename as the db name
• Still get the same error message about no such table. I have verified that the "datatables_demo" table does exist as created by the supplied SQL code
• I don't know where the db is supposed to be copied to. I've tried:
• root folder
• /lib
• /lib/database
• /examples

I also tried

I assume that my issue is that the database is not being found but I could not find any more detailed documentation for how to set up a SQLite db in the installation or in the forum. I found (https://datatables.net/forums/discussion/37157/using-an-sqlite-database#Comment_98465) and (https://datatables.net/forums/discussion/61961/using-datatable-editor-with-sqlite) Could someone explain what the configuration needs to be in order to work with a SQLite db? And where the SQLite db should be located? Thanks.

<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Database user / pass
 */
$sql_details = array(
    "type" => "Sqlite",     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "",          // Database user name
    "pass" => "",          // Database password
    "host" => "", // Database host
    "port" => "",          // Database connection port (can be left empty for default)
    "db"   => "sample.db",          // Database name
    "dsn"  => "",          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    "pdoAttr" => array()   // PHP PDO attributes array. See the PHP documentation for all options
);


// This is included for the development and deploy environment used on the DataTables
// server. You can delete this block - it just includes my own user/pass without making 
// them public!
// if ( is_file($_SERVER['DOCUMENT_ROOT']."/datatables/pdo.php") ) {
//  include( $_SERVER['DOCUMENT_ROOT']."/datatables/pdo.php" );
// }
// /End development include

allan

Hi,

Thanks for all the details. The error message suggests that the demo SQL has not been successfully imported into sample.db, or possibly the path for sample.db is incorrect. It might be worth giving it an absolute path - e.g. /var/www/html/sample.db or whatever it is that you have it set up as.

I just double checked the SQLite SQL and datatables_demo is certainly there.

Thanks,
Allan

Dr. Tohichi Hikita

That did the trick. I figured it had to be something straighforward! I did try relative paths to find the database but it, for some reason, didn't occur to me to make an absolute path. I think I was thrown off by the other answer in the forum where the person asking used a local path to the db so I assumed that a local path should work.

I couldn't find from searching or in the setup guide how to specify the path (absolute or otherwise) so having that info in the SQLite section and/or in the code comment would be super helpful.

If relative paths work, it would also be really helpful to include a SQLite db as part of the examples so that people could get instant gratification of having an example that works right out of the box.

Thanks for the assist!

allan

Relative paths do work, they just need to pint to the correct place for where the file actually is. That is dependent upon where the PHP is executed from and the file location. The absolute path takes away any possibility of ambiguity which is where things go wrong.

Thanks for the suggestion about the SQLite db - I will look at that as an option. We find that very few people are using Editor with SQLite though, MySQL followed by SQL Server and then Postgres are the use order I think. But as you indicate it might be a fast way to try it out and then progress on to a different DB.

Allan

htuser

Hi all,
I also have the same issue about using datatable with SQLite db in Javascript Webview project using CapacitorJS. @allan , please can we have a JS datatable API for SQLite? The newly released SQlite WASM https://sqlite.org/wasm/doc/trunk/index.md will increase this demand in the near future?
As you know, SQLite.js https://sql.js.org/ exist years ago. However, the latest SQlite WASM API offer persistent storage on browser and WASI. Please let me know about it.
Best,

allan

JS datatable API for SQLite

I'm not actually sure what that would look like? Perhaps you can mock up so code showing what you are thinking? DataTables has add, update and remove methods, so any thing can interact with it.

Allan

htuser

I already post this about: https://datatables.net/forums/discussion/76009/datatables-editor-direct-connexion-to-sqlite-database-on-webview-and-browser#latest

I'm not actually sure what that would look like?
A sample of using DataTables as frontend for displaying data from a persistent SQLite WASM db, this in the client side.
DataTables has add, update and remove methods, so any thing can interact with it.
Does add is the equivalent of SQL insert into?
update is the equivalent of SQL update?
remove the equivalent of of SQL delete?
> mock up so code showing what you are thinking
I'll post about.
Best

allan

I've added a reply in your other thread. Let's discuss it further there rather than talking about it in two places.

Allan