• Welcome, Guest. Please login.
April 18, 2019, 10:18:22 am


Welcome to the SQLitening support forums!

Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - D. Wilson

You've got Questions? We've got Answers! / sql query
April 09, 2019, 04:10:12 pm
I 'know it can be done - I am just having trouble creating it'

I have two tables

1) Inventory
2) Holds

I want to run a query that will give me result the shows the total holds vs inventory qty on hand. I want to show all the items that have more holds than the current inventory level.

In theory the system will never allow a user to put on hold if there is not an enough inventory.

But I want to run the query on program startup to create a dashboard for the user to know those inventory items have 'issues'

There could be multiple holds per inventory item

I know it would involve sum and group by. But I am stuck trying to create the needed sql query.

Any help would be appreciated.
I have a database with multiple tables (2). I need to load information from both tables my screen. This is my overview on how I am doing it.

1) I open the connection to the Server
2) I open the database
3) I get my record from the first database. There is only 1 record from the first database
4) I close that database
5) I open the second database
6) I retreive the records from from that database. Usually there are multiple records from the second database.
7) I close the connection to the server

I use a page up/page down to retreive the next record.
I am able to do this several times then on the forth (or so ) I get an error -14 Invalid set.

I use different numbers for the returned sets. The server is on the local computer.

Any ideas or insights would be appreciated. Code Examples would be awesome.
You've got Questions? We've got Answers! / Encryption
December 18, 2018, 08:04:52 pm
What are the steps to provide encryption to an sqlite database ??

If I encrypt a database using SQLitening can I open it and decrypt it using third party tools (ie a SQLite Database Viewers)

Any help would be appreciated.
I would like to replication a sqlite database at multiple locations.
I have looked online and there are several solutions. However they want all traffic routed through their server.
I like Litesync.io it is bidirectional and offers an 'Airplane' mode.

I want to keep the functionally of the sqlitening server.

Any insights would be greatly appreciated.
I am stuck on another SQL Query. It is something that I know should be able to be done. I just can't figure out how to do it. I want to a group by and sum of a field. The 'grouping' field is numeric and 5 digits.

FYI -- The grouping field is a Sales Category. The first 2 digits represent the major catagory; and the remaining digits allow us to refine our inventory by sub categories.

Raw data:

                 51000              $10.00
                 51100              $5.00
                 62500              $5.00
                 70000              $10.00
                 70500              $10.00

'Desired Results'

                 51000       51             $15.00
                 62000       62             $5.00
                 70000       70             $20.00

Basically I am subtotaling by the first two digits of the numeric value.

I am trying to create an SQL Query to get the report data. Any ideas or suggestions would be greatly appreciated.

Thank you for your assistance.
I know sqlite is typeless in column types. I am stumped. I would like to update the value of a field however. If the field is null it will not update the field. For Example:
                               Update INVENTORY set qty_on_hand = qty_on_hand + 5 Where Itemcode ='Item Code'

                         If the original value is 10. The server would update it to 15.
                         If the original value is null (no value in the field). The server does not apply the change.

Any help and insights would be greatly appreciated.

I have been using SQLitening for several years now with great success. I would like to 'hear' what other peoples stories have been. What has been the largest install in the way of users/database sizes. Has you encountered any issues that would worth sharing ??
You've got Questions? We've got Answers! / pictures
August 13, 2016, 03:27:37 pm
I am working on a project that deals with lots of pictures. I have no problem adding/saving them to a sqlite database through sqlitening. The question I have is this....

Is it possible to save the picture files in a directory where the sqlite database is saved  (under a different directory) and retreive the file when needed through the sqlitening server.

I am already keeping the pictures in a seperate database. But I am thinking about back ups that having the images seperate would allow easier backups. Since I only have to backup the changes.

Any thoughts or help would be greatly appreciated.
You've got Questions? We've got Answers! / Security
February 06, 2016, 07:08:09 pm
One question I have and maybe those who have studied the code. Is security. How secure is SQLitening we are not utilizing a User Name/ Password.

Does the server just accept calls from the dll or can other query the port.

Any Insight or suggestions in securing the server would be appreciated.
You've got Questions? We've got Answers! / Dual Servers
January 15, 2016, 05:10:14 pm
I have two servers in two locations. Currently I write to one server , Close the connection and connect to the other server. Is there a better way to update both servers ??
You've got Questions? We've got Answers! / Security
September 03, 2015, 09:36:05 pm
Using Sqlitening - What measures should be adopted to prevent SQL Injection attacks. Any thoughts would be appreciated.
I have never done this -- What is the procedure for updating the sqlite database engine using sqlitening ?? The version that is included in with the downloads is an older version. Just curious -- I don't like being on the 'bleeding edge' but it would be good to know for the future.
General Board / Future
February 03, 2014, 08:09:09 pm
I have noticed a lot of activity on the Power Basic Forums regarding companies (including Paul's) severing ties with Power Basic. The question I have is where does that leave us ?? Who is hosting the forum ?? I know SQLitening is open source but we never know what the future holds.
You've got Questions? We've got Answers! / Windows 7
October 01, 2013, 10:08:18 pm
Ok I have to break some habits. I have always created my own directory (not under Program Files) and placed my program/data files  in those directories. I have found this to be very reliable in the past and has worked excellent for my needs.

But with Windows 7 -- Windows will not allow you to save to those directories without  setting the program to Administor level. However when I set a program for Adminstrator access - I get the pop up asking if I would like to allow the program to make changes to the computer.

It also does not like the app.path statement to get the path of the software. I used to create a file there where I kept all my settings.

What is the best approach to saving data and allowing user permissions on Windows 7 ??

Also does this effect SQLitening running in the client/server mode ??

Any thoughts or  advice would be greatly appreciated.

I am considering utilizing some characters in the extended character set. Ascii value greater than 127 and less than 255. I would like to use them as field delimiters in a field (I want to combine several fields into one before sending it the database).

Do you think this would raise any issues in using them in a sqlite database. I am going to do some testing. But I thought I would put it out and see what other think or what they have experienced.

General Board / Power Basic Down ??
July 23, 2013, 01:24:17 am
For the past two days I have been trying to access the power basic website. I have not been able to. Is anyone else having the same issues
I created a database and have a set declared as numeric. I used an external program and the database structure says the field is numeric. I imported a bunch of data in the table. But I am having trouble retrieving data from the table.

I know there are 0 values (THE NUMBER ZERO)  in the table.
When I query                            SELECT * from Table WHERE fieldname =0        it returns nothing
When I query                            SELECT * from Table WHERE fieldname = '0'      it return the expected results ????
When I query                            SELECT * from Table WHERE fieldname > 0        it returns nothing
When I query                            SELECT * from Table WHERE fieldname =1.15    if returns the expected results.

All my other querys work great. It seem to be related to 0 (zero) queries.

I have used the is null syntax and sqlite does not return any value (as expected) I know the fields have a value.

I have spent over 4 hours trying to figure this out. I think I am having a 'senior' moment (But I am not that old). What am I missing. Any help/insights would be appreciated.
I have a 'stupid' question.. I just want to verify something before I jump in.

It is my understanding that I can combine both updates and inserts within one transaction. Ie insert 5 new records and update 20 records all within one transaction. Rather than creating two seperate transactions one for inserting and one for updating.
I am waving the white flag -- I am trying to figure out an sql query. I know SQLite should be able to do this I just don't know how to create the query.

I need to query a history file and pull the last record for a time period. I am familiar with the Group By function.
The Group by function use functions to get the sum of columns-- However I only need one record per customer.

I would like to use the group by function to get all of the customers in the file with one call to the server.

The file is sorted by date in descending order. I want to retrieve only the last record.

The four fields are:
Customer - Customer code
Idate    - Invoice date
Inumber  - Invoice number
Iamount  - Invoice total

Sample Data

Customer    Idate         Inumber    Iamount
C1              4/4/2013       10142      145.00
C1             10/5/2012      10030      50.00
C1             2/6/2012        10020      14.00
C2             4/1/2013        10141      99.00
C2             6/12/2012      10028      5.00
C3             1/1/2013         10140      893.75
C3             3/6/2012         10010      78.70
C4             1/1/12             10009      15.75

If I use a cut off of 4/4/2013 then results would be:

C1         4/4/2013   10142      145.00
C2         4/1/2013   10141      99.00
C3         1/1/2013   10140      893.75
C4         1/1/12        10009      15.75

If I use a cut off of 4/1/2013 the results would be:

C1         10/5/2012  10030      50.00
C2         6/12/2012  10028      5.00
C3         1/1/2013    10140      893.75
C4         1/1/12         10009      15.75

If I use a cut off of 6/12/2012 the result would be:
C1         2/6/2012    10020      14.00
C2         6/12/2012  10028      5.00
C3         3/6/2012    10010      78.70
C4         1/1/12        10009      15.75

If I use a cut off of 2/1/12 then the result would be:
C4         1/1/12     10009      15.75
General Board / Happy Holidays
December 24, 2012, 02:21:29 am
With the holidays here; it is time to remember our friends and family that enrich our lives. I know it seems funny -- but I want to send a big holiday thank you to all the people who are active with the SQLitening project. This project has provided me with a very powerful tool in my programming toolbox. I may never meet any of you in person. But I appreciate the help and advice that has been provided over the years. SQLitening has a bright future and I look forward to 2013.

Happy Holidays and Happy New Year.