db files and sql files

Talk about anything
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

db files and sql files

11 Jun 2018, 06:29

- In recent months, I've had difficulty finding utilities for handling db and sql files.
- These are the results I've found so far:

to view SQL files as plaintext:
Notepad2 [or any text editor, the files may be too big for Notepad to handle]

to browse SQL files:
perhaps? DB Browser for SQLite
[I could see all of the information in Notepad2, however, I got this error:]
Please enter the key used to encrypt the database.
If any of the other settings were altered for this database file you need to provide this information as well.

to view DB files as plaintext:
(not applicable, the file appeared to be a binary format)

to browse DB files:
DB Browser for SQLite

to read/write DB files in AHK:
this class which requires sqlite3.dll:
[Class] SQLiteDB - Update on 2016-03-28 - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=6&t=1064
[further links:]
SQLite and AHK - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=5&t=39965
SQLite - AutoHotkey Community
https://autohotkey.com/boards/viewtopic ... 12&t=13432

- Also, SQL apps mentioned in the Stack Overflow 2018 survey:
Stack Overflow Developer Survey 2018
https://insights.stackoverflow.com/survey/2018/

Code: Select all

58.7%	MySQL
41.2%	SQL Server
32.9%	PostgreSQL
25.9%	MongoDB
19.7%	SQLite
18.0%	Redis
14.1%	Elasticsearch
13.4%	MariaDB
11.1%	Oracle
7.9%	Microsoft Azure (Tables, CosmosDB, SQL, etc)
5.5%	Google Cloud Storage
5.5%	Memcached
5.2%	Amazon DynamoDB
5.1%	Amazon RDS/Aurora
3.7%	Cassandra
2.5%	IBM Db2
2.4%	Neo4j
2.2%	Amazon Redshift
2.2%	Apache Hive
2.1%	Google BigQuery
1.7%	Apache HBase
66,264 responses
- Any further help or information would be much appreciated. Thanks.

- Btw please be very clear about any installation instructions.
- DB Browser for SQLite was a stand-alone app, I installed the portable version.
- For just me's SQLite library, I wrote some code in a script, used #Include to include Class_SQLiteDB.ahk, and I dowloanded sqlite3.dll separately and placed it in the same folder as Class_SQLiteDB.ahk.
Last edited by jeeswg on 11 Jun 2018, 10:38, edited 1 time in total.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
swagfag
Posts: 6222
Joined: 11 Jan 2017, 17:59

Re: db files and sql files

11 Jun 2018, 07:20

i pretty much just use DB Browser for SQLite
there's also SQLite Studio with a bit more sophisticated UI and additional features, but it autocommits on query execution and i havent found a way to turn it off
User avatar
nnnik
Posts: 4500
Joined: 30 Sep 2013, 01:01
Location: Germany

Re: db files and sql files

11 Jun 2018, 07:56

Ah you know sqlite is the only database that you can open as file.
Many of the remaining databases are larger than single files and can't be opened or edited unless they are running.
SQL itself is a language - sql files therefore are sql scripts used to setup, fill, modify or query databases.
See: https://www.w3schools.com/sql/default.asp
The so called "SQL Apps" are actual database servers.
Many of them are easy to install. Once you have the database server running you can connect to it from any kind of program as long as that program can communicate with that specific database server.
In order to do that you often need specific drivers which are then used by the program connecting to the database server.
Recommends AHK Studio
Guest

Re: db files and sql files

11 Jun 2018, 10:09

Database .NET @ https://www.portablefreeware.com/index.php?id=2417
Database .NET is a powerful and intuitive multiple database management tool. The program allows the administration of local or remote databases to browse objects, design tables, edit rows, export data, create new databases and run queries with a consistent interface. The program includes table/data editor, index editor, syntax highlighting, SQL profiler, code manager and multiple query tabs.

Supported databases include SQL Server, SQL Azure, Firebird, SQLite, MySQL, PostgreSQL, Oracle, IBM DB2 and Informix, Sybase ASE, MS Access and Excel, dBase IV, FoxPro, Data Sources, ODBC DSN and OData. Can export to CSV, XML, TXT, XLS, or SQL Print Query Results.
User avatar
hoppfrosch
Posts: 443
Joined: 07 Oct 2013, 04:05
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

Re: db files and sql files

11 Jun 2018, 23:29

dbeaver - Universal Database Tool

Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: db files and sql files

12 Jun 2018, 04:09

- @Guest and @hoppfrosch: Thanks for the recommendations.
- Database .NET crashed with an error message when trying to open the sql file, and stated that the db file was unrecognised (and displayed the binary as a string).
- dbeaver stated that error occurred when trying to open the sql file, and appeared to show that the db file was unrecognised (and displayed the binary as a string).
- I could try MySQL, however, I'm not sure which app would be the most likely to work to open sql (and db) files.
MySQL :: MySQL Community Downloads
https://dev.mysql.com/downloads/

- The db file is a small file (16 KB) created by some of just me's code, using sqlite3.dll, in one of the links.
- The sql file is 179 MB and the top of the file states this, although I had trouble trying to get phpMyAdmin to work.

Code: Select all

-- phpMyAdmin SQL Dump
-- version 4.0.10deb1
-- http://www.phpmyadmin.net
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
nnnik
Posts: 4500
Joined: 30 Sep 2013, 01:01
Location: Germany

Re: db files and sql files

12 Jun 2018, 05:02

if you want a simple setup for messing with SQL, PHP, HTML/JS/HTML, Perl or Java EE I reccomend XAMPP
https://www.apachefriends.org/download.html
Recommends AHK Studio
User avatar
joedf
Posts: 8940
Joined: 29 Sep 2013, 17:08
Location: Canada
Contact:

Re: db files and sql files

12 Jun 2018, 05:38

hoppfrosch wrote:dbeaver - Universal Database Tool

Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.
Cool! Thanks for sharing :+1:
nnnik wrote:if you want a simple setup for messing with SQL, PHP, HTML/JS/HTML, Perl or Java EE I reccomend XAMPP
https://www.apachefriends.org/download.html
+1
Image Image Image Image Image
Windows 10 x64 Professional, Intel i5-8500, NVIDIA GTX 1060 6GB, 2x16GB Kingston FURY Beast - DDR4 3200 MHz | [About Me] | [About the AHK Foundation] | [Courses on AutoHotkey]
[ASPDM - StdLib Distribution] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library]
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: db files and sql files

08 Aug 2018, 11:03

- I have solutions for my 2 problems.
- just me's SQLite library (Class_SQLiteDB.ahk) plus sqlite3.dll is good for creating and maintaining db files (binary files), and it's possible to retrieve the text via SQL queries e.g. generate a tsv file (tab-separated values).
- For the .sql file (phpMyAdmin SQL Dump), it just contains multiple SQL statements, I can run these using the library I just mentioned to generate a db file.
- Btw it's *much* faster to INSERT multiple rows in one query than it is to INSERT one row at a time.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

Return to “Off-topic Discussion”

Who is online

Users browsing this forum: Gio and 61 guests