Recent Posts

Pages: [1] 2 3 ... 10
1
You've got Questions? We've got Answers! / Recalulating work free days
« Last post by Fim on Today at 04:12:52 AM »
I have a calendar of every day from 1 Jan 2015 until 31 Dec 2099.
The days of work-free days are marked with an X in the ARBFRI column. When adding or deleting a work-free day, DAG_LOPNR (work day number) must be recalculated.
I do it in a program, but it takes a very, very long time.
Is there any way to do it in SQLite?
Like this:
UPDATE CAL SET DAY_LOPNR = PREV + 1 WHERE ARBFRI = ''
Here is the beginning of th table:
Code: [Select]
DATE   ARBFRI DAG_LOPNR
150101                1
150102                2
150103      X         0
150104      X         0
150105                3
150106      X         0
150107                4
150109                5
150110                6
150111      X         0
150112      X         0
150113                7

/Fim W.
2
You've got Questions? We've got Answers! / Re: Features missing in "local mode"
« Last post by cj on May 21, 2017, 06:21:20 AM »
Quote
--- Local Mode ---
If the FileName is not fully pathed then it is assumed to be relative to the current folder which is the same folder, unless changed by chdir, that the first .Exe was run from. If fully pathed then no assumptions are made. Since this running on a local computer SQLitening allows the files to be located anywhere on the local hard drives or local network drives.

Examples:
If your .Exe started in C:\Apps\MyApp then:
If FileName is X\Y\Able.Sld then will assume file is in C:\Apps\MyApp\X\Y.
If FileName is ..\Y\Able.Sld then will assume file is in C:\Apps\Y.
If FileName is C:\Able.Sld then no assumption.

--- Remote Mode ---
The FileName is assumed to be relative to the folder which the service is running from. Since this is running on a remote server SQLitening can not allow the user to access files anyplace on the server. Access is denied to any FileName that has a colon, a double dot, or begins with a backslash. This will insure that the file is in same folder as the service or below it.


Examples:
If your service is running from C:\SQLitening then:
If FileName is Data\Able.Sld then will assume file is in C:\SQLitening\Data.
If FileName is ..\Y\Able.Sld then will get error -8 Access Denied.
If FileName is C:\Able.Sld then will get error -8 Access Denied.

Code Example:

' Create a new database
slOpen "DBFirst.db3", "C"
slExe "Create Table If Not Exists Table1 (Field1, Field2, Field3)"
slClose

' Create a second database and attach the first
slOpen "DBSecond.db3", "C"
slExe "Create Table If Not Exists Table1 (Field1, Field2, Field3)"
slAttach "ExampleB_New1.Sld", "One"
3
You've got Questions? We've got Answers! / Re: Features missing in "local mode"
« Last post by Fim on May 21, 2017, 02:56:08 AM »
slSetRelNamedLocks:
"Named locks are only used in remote mode, ignored when running in local mode."
/Fim W.
4
You've got Questions? We've got Answers! / Re: Features missing in "local mode"
« Last post by cj on May 20, 2017, 08:15:20 PM »
I haven't seen any list of missing features in local mode so started a list

Single-writer service handling I/O for client/server mode
Connect over the internet

SQLiteningServer.Log:
Record login of users
Record logout of users

Some features in the SQLiteningServer.Cfg:
Require password
Only allow read-only
Maximum connections allowed
Disallow creating databases
Time-out users

Might be able to encrypt a folder making data only available to service
Server-side includes like security logon (such a big thing, but so little dicussion on the subject)
Reading and writing restricted to path under the server

slGet/slPut restricted to path under server and file names must be listed in SQLiteningServer.cfg
and reading/writing to files related to the server are not allowed
5
You've got Questions? We've got Answers! / Features missing in "local mode"
« Last post by Fim on May 20, 2017, 04:46:09 AM »
Is there any compilation of features available in "remote mode" but missing in "local mode"?
/Fim W.
6
Someone asked about this OP question on the SQLite mailing list/forum:

http://sqlite.1065341.n5.nabble.com/foreign-key-constraint-failure-td95683.html

Looks like there is a way, but it's not straightforward.
7
You've got Questions? We've got Answers! / Re: Query Multiple Fields Against List
« Last post by cj on May 09, 2017, 12:41:15 PM »
This is rough, but seems to work.  I know you wanted to do in a SQL statement, but this generates SQL.

Code: [Select]
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL sColumns,sql,sOrder AS STRING
 slopen "junk.db3","C"
 slexe "drop table if exists accounts"
 slexe "create table if not exists accounts(f1,f2,f3)"
 slexe "insert into accounts values(1,2,3)"
 slexe "insert into accounts values(4,5,6)"
 slexe "insert into accounts values(7,8,9)"

 REDIM sList(1 TO 3) AS STRING
 sList(1) = "1,40,70"
 sList(2) = "20,50,80" 'won't find in here
 sList(3) = "30,60,9"

 sOrder   = "1,2,3"  'search order
 sColumns = "f1,f2,f3"
 test("select rowid,* from accounts",sColumns,sList(),sOrder)
END FUNCTION

FUNCTION test (sql AS STRING, sColumn AS STRING,sList() AS STRING,sOrder AS STRING) AS STRING
 LOCAL x AS LONG
 LOCAL order AS LONG
 LOCAL NumberOfColumns AS LONG
 sql+= " where " + $CR
 NumberOfColumns = PARSECOUNT(sColumn)
 FOR x = 1 TO NumberOfColumns
  order = VAL(PARSE$(sOrder,x))
  sql+= PARSE$(sColumn,x) + " not in (" + sList(order)+ ")" + $CR + " and "
 NEXT
 sql = LEFT$(sql,-4)
 FUNCTION = sel(sql)
END FUNCTION

FUNCTION sel(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 LOCAL s AS STRING
 slselary sql,sArray(),"Q9"
 IF UBOUND(sArray) > 0 THEN
   s = JOIN$(sArray(),$CR)
   FUNCTION = s
   ? s
 END IF
END FUNCTION
8
Thanks, Bern. That certainly will shorten the query and make future changes easier.

In searching the web, I noticed that some versions of SQL support the "WHERE (F1,F2,F3) NOT IN (csl)" construct, but SQLite apparently isn't one of them.
9
It's not exactly what you want, but should be able to do something like this:

WITH csl AS VALUES (comma-separated-list) SELECT Name FROM Accounts WHERE F1 NOT IN csl AND F2 NOT IN csl AND ...

It should help avoid having to retype the list every time and make it easier to change the list should you need to down the line (assuming the list is the same for all your fields).
10
Is there a "simple" way to query multiple fields against a single comma separated list? I'm trying to avoid multiple "NOT IN" sections of a query. The ideal solution would be something like:

Code: [Select]
"SELECT Name FROM Accounts WHERE (F1,F2,F3) NOT IN (comma-separated-list)"
But SQLite doesn't appear to like this. I'm trying to avoid:

Code: [Select]
"SELECT Name FROM Accounts WHERE F1 NOT IN (comma-separated-list)
            AND F2 NOT IN (comma-separated-list)
            AND F3 NOT IN (comma-separated-list)"

Mostly because there will be up to seven "F" fields in the query.

Any suggestions are welcome.
Pages: [1] 2 3 ... 10