Frequently Asked Questions

General

SQLwallet supports SQL Server, MySQL, SQLite, Oracle, and PostgreSQL natively.  In addition, you can connect to virtually any database through ODBC (as long as there is an ODBC driver for your database).

The Starter edition (up to 5 users) is free, forever. Pro edition (up to 15 users) costs £800 and contains advanced features such as AD authentication and your company branding. Enterprise edition (up to 30 users) costs £1600. All paid options include one year of free upgrades and UI customization including your logo and brand colors. If you need more licenses or bespoke integrations, contact us for a quote.

SQLwallet is as safe as any other data access tool. But it  is just a tool, and as any other powerful tool it can be harmful if used improperly or carelessly. SQLwallet does not limit you. Ultimately it is your responsibility to have a QA in place and ensure that your queries are correct and do not create excessive load on the database. It is also your responsibility to check that your report users and developers have proper permissions.

As of data editing, the risks are not any higher than to grant direct access to database (you would never do this, would you?) or to use your own developed application to modify your data. Moreover, SQLwallet keeps logs for every single data data modification, so you always can easily find who and when changed the data and undo the changes if needed.

If you want to be extra cautious, always make sure that the rights are limited by the SQL connection string and never use user accounts  with sysadmin or dbo permissions in SQLwallet connection strings. It is always good idea to have two separate connection strings for data-read reports and data editing apps.

All sensitive information stored in SQLwallet database such as connection strings passwords is encrypted using file-based private keys.

Finally, SQLwallet supports HTTPS connection and we strongly recommend to use it when using SQLwallet.

The back-end performance (i.e.  query execution speed) is dictated by the performance of your database and by how well your query is optimized. We recommend to prepare and run your query in your usual DB tool, inspect the execution plan and optimize it, and only then to use query in SQLwallet. 

The front-end performance depends on the amount of rows returned by your query. By default SQLwallet limits the output by 2000 rows (this can be changed in settings), which affects both Table and Chart views. If the  amount of rows exceeds the limit, the user will receive a warning message. However the limit has no impact on the import. In our tests the import to Excel worked well up to millions of records.

Pivot/Chart performance also depends on the amount of rows returned by the query, as the aggregation are performed on the client side. We use open source Pivot Table component which handles well up to 100,000 records.

To achieve the best Pivot/Chart performance we recommend to make the heaviest aggregations in your SQL query, effectively reducing the amount of rows up to several thousands (but not exceeding the general rows limit setting).

Installation and configuration

No. SQLwallet is a self-hosted service and does not require any additional web server software to run.

If you are running SQLwallet on a dedicated machine without IIS installed, you can specify default HTTP (80) or HTTPS (443) port. However, if you have IIS, Skype, Teamviewer or another software that is likely to use port 80 running on the same machine, than you need to specify any other free port, for example 88.

First of all, check which port you are using. You can find and change the port settings in the configuration file appsettings.json, located in SQLwallet installation directory. If you use default port 80, try to change it to any other unused port and restrat the service.

If this does not help, check for relevant error messages in Windows event viewer and submit them to our support or post on the forum and we will do our best to help resolving the problem.

SQLwallet stores report definitions, settings, permissions and logs in SQLite database, which is located in App_Data folder under SQLwallet installation directory (usually "C:\Program Files (x86)\SQLwallet\SQLwallet").

This is a single file which you can archive and copy to your own backup location, copy back to restore.

If you want to change the location of the database, simply move the sqlwallet.s3db file into a different folder and change the "DatabaseConnectionString" setting in appsettings.json configuration file.

In order to force SQLwallet to work over HTTPs, add the CertificatePfxFile and CertificatePassword settings in appSettings.json configuration file, also make sure you change the port to the HTTPS (usually 443)

"SystemSettings": {
  ...
  "ServerPort": "443",
  "CertificatePfxFile": "<path to PFX file>",
  "CertificatePassword": "<PFX private key>"
},
...

Creating reports

You can add parameters into your SQL query in a number of ways.

First, simply prefix parameter name with $ sign. For example, if your parameter is called Country, refer it as $Country. SQL editor supports autocompletion, so you will see all available parameters once you type $ sign.

SELECT * FROM Countries WHERE Code = $Country;

Secondly, all parameters are implicitly converted into SQL variables, so you can use @Country.

SELECT * FROM Countries WHERE Code = @Country;

Last, you can use macro syntax, with two dollar signs ($$Country). Macro parameters are substituted before the query gets parsed, so you can do things you cannot normally do with native SQL variables, for example:

USE $$Database;

SELECT * FROM Countries WHERE $$WhereClause

By marking parameter as optional you just remove client-side validation. In addition you need to implement the logic in your SQL query. For example, assuming you have two optional parameters $COUNTRYCODE and $COUNTRYNAME, your SQL code may look as follows:

SELECT * FROM Countries WHERE 
($COUNTRYCODE IS NULL OR Code = $COUNTRYCODE)
AND 
($COUNTRYNAME IS NULL OR Name LIKE '%'+$COUNTRYNAME+'%')

 

 

The typical use is 

SELECT * FROM Countries WHERE $COUNTRYCODE IS NULL OR Code IN ($COUNTRYCODE);

SQLwallet automatically constructs proper SQL for IN clause.

If you need to treat the parameter as string (list of comma-separated values) - that might be useful for passing the parameter into a stored procedure - you should specify query type "Stored procedure".

EXEC spGetCountries($COUNTRYCODE);

 

Let's say your sales users are only allowed to see data, related to their assigned leads only.

In such scenario you should use system parameter $CURRENT_USER in your query.

Let's say you want to select only the leads that are assigned to the current report viewer. The query will look like:

SELECT * FROM Leads
JOIN Users ON Leads.AssignedUserId = Users.Id
WHERE Users.Username = $CURRENT_USER;

You cannot re-order output columns because normally you don't add each and every output column - normally you only need to define only output columns where you want to change default behavior or appearance. If there were a sorting option, it would cause an ambiguity for the columns that are not specified in the Output Columns section.  Therefore the column order should be defined by your SQL query.

The easiest way is to make the validation part of your SQL.

For example (MS SQL)

IF (DATEDIFF(DAY,$dateFrom,$dateTo) > 30 
   SELECT 'The dates interval cannot bee more than one month'.
ELSE ...do your query here

 

Or alternatively

SELECT 'The dates interval cannot bee more than one month' 
WHERE (DATEDIFF(DAY,$dateFrom,$dateTo) > 30 

Running reports

To run a report within iframe, use:
/run/<report-id>?<param1>=<value1>&<param2>=<value2>&....
For example:

Both GET and POST are supported.

Please note that external authentication is not supported at the moment, so a user will need to authenticate in SQLwallet once per browser's session.

Pivot/Chart view in this mode is not supported at the moment.

Charts and pivots

Please adjust page orientation and set custom scale in the standard Print Preview window to make the chart fit the page borders.