SQLitening Support Forum

In a program to find words from a database of 4 million words, I have a timing problem.
If I execute a select statement, for example
SELECT * FROM WORDLIST WHERE LENGTH = 10
and then downloads all the selected data, it takes 6 seconds on a completely just started PC. The second time when I execute another select statement, SELECT * FROM WORDLIST WHERE LANGD = 12, and then retrieve all the selected data, it takes 0.6 seconds. If I exit the program without restarting the PC, restart the program, all the select statements take less than a second.
I've come so far in my research that it's not the Select statement itself that takes time, it's the subsequent fetching of data that takes time.
Anyone have a good explanation?

Can a PDF file be stored in a database, if so how?
/Fim W.

Is there any way to find out what locks I have made with slSetRelNamedLocks?
/Fim W.

In my system, I handle all processing of a Sql database via a special IO module, a module for each table. Everything works as I had imagined. But in a special case I get error code 7. I have done some tests and it seems I do NOT get error if I make all SQL calls directly in a program without going through an io module.
I get the error after 3732 calls with function GU (get unique).

FUNKTION_GU:
'===========
'
GU_SELECT:
    slOpen TRIM$(KOM.DATABAS)
    SET_NUMMER = slGetUnusedSetNumber
    SEL_STRANG = "SELECT * FROM ART WHERE ARTNR= " + $SQ + TRIM$(ART.ARTNR) + $SQ
    SVAR = slSel(SEL_STRANG, SET_NUMMER) ' Ska ge noll
    IF SVAR <>  0 THEN
        KOM.FELTEXT = "SYSTEMFEL i ARTIO GU slSel=" + STR$(SVAR)  <<============
        GOTO SYSTEMFELHANTERING
        EXIT SUB
    END IF
Tips where to look for errors are gratefully received.
/Fim W.

What I'm getting is slSelBind is a method of using "Prepared Statement Object" in SQLite
In an ERP system there is something called "requirements explosion" and I have programmed it, works ok, but it takes time.
This slSel-sts is executed 220 times for a particular item:
"SELECT MTRLNR, POS, QUANT, FROM_VECKA, TOM_VECKA, SORT_1 FROM STR WHERE ARTNR =" + $ SQ + TRIM $ (ARTNR) + $ SQ + "ORDER BY MTRLNR"
Instead, I've tried this:
slFIX = slBuildBindDat ("$ SQ + TRIM $ (ARTNR) + $ SQ")
..
..
slSelBind ("SELECT MTRLNR, POST, QUANTITY, FROM_VECKA, TOM_VECKA, SORT_1 FROM STR WHERE ARTNR =? ORDER BY MTRLNR", slFIX, SET_NUMMER_TABELL (NIVA))

But it does not work, I think wrong, but I do not know what.

I am building a smaller ERP system with Powerbasic and SQLitening. It works as it should when I run in Remote mode. But when I run in Local mode I get "Database locked" every now and then. It does not appear to be a systematic error. Does anyone have a tip where to look for the error?
/Fim W.

SELECT strftime('%H:%M:%f','now','localtime')
Is it the timestamp from the server or the client.
I can't find it in the manual, or I'm bad at finding it.

And, if it is from the client, how to get it from the server?
/Fim W

According to https://sqlite.org/cli.html item 18, two parameters can be used.
This works ok:
sqlite3 winova.db3 .help
But not this:
sqlite3 winova.db3 .read export.sql
Then I got the error message: Usage: .read FILE
Am I doing something wrong or ....
/Fim W

All I've done is to change sqlite3.dll from versions 3.14 to 3.26. Then I get wrong. There is nothing in the log file.

I have a database of just over 60 tables. The names are always 3 letters, such as ARB, ART, FAB. Now I intend to insert a table description in each table on rowid = 0.
Is it good or bad?
/Fim W.


MIN_MAX_START:
    TEMP_MIN = 100
    TEMP_MAX = -100

MIN_MAX_SELECT:
    SQL_SATS = "SELECT TEMP FROM TEMPERATUR WHERE DATE LIKE " + $SQ + 2017% +$SQ
    slSel(SQL_SATS)

MIN_MAX_READING:
    IF slGetRow = 0 THEN EXIT FUNCTION
    TEMP_=  VAL(slFN("TEMP" ))

MIN_OCH_MAX_LAGRING:
    TEMP_MAX = MAX(TEMP_MAX, TEMP)
    TEMP_MIN = MIN(TEMP_MIN, TEMP)
    GOTO MIN_MAX_READING


Is it possible to solve this in an SQL statement only?
/Fim

How to put strings coded with UTF-16 in a SQLite databas using SQLItening?

This does not work:

DIM RAD_IN AS WSTRING
slExe(RAD_IN, "E2")

Parameter mismatches definition says the compiler.
/Fim W.

SELECT BES_KVANT * PRIS / PRISKVANT FROM IRA

Works as i should, but...
For some records PRISKVANT  is zero.
In that case I will use 1 instead.
But how to put an if-statment in that SELECT-command?
Like  SELECT BES_KVANT * PRIS / (if PRISKVANT = 0 then 1 else PRISKVANT) FROM IRA
Pls, don't say RTM, i have tried.
/Fim

I have a conversion program, it reads a sequential file of about 4,000,000 records with 19 fields. For each record I make an INSERT in a table. It takes about 13 hours because compilation of each INSERT takes about 12 ms. Is there any way to use that function in slExeBind that tells SQLite3 to only compile the
SQL statement once and then insert each row re-using the compiled statement?

/Fim W.

Are there any differences between
CREATE TABLE HIS (AA PRIMARY KEY, BB, CC);

and

CREATE TABLE HIS (AA, BB, CC);
CREATE UNIQUE INDEX HIS_INDEX0 ON HIS (AA);

/Fim W.

I have a Item Master Table ITE with 100,000 items. I want all the items whose item name contains all the keywords. The number of keywords can be 1 to 5.     
Current solution is shown below (maybe something wrong in the translation from Swedish to English)
Is it possible to solve the selection in the server using LIKE or like() ?
STARTING:
    slSel "SELECT ITEM_NO, ITEM_NAMN FROM ITE ORDER BY ITEM_NO"

FETCH_NEXT_ITEM:
    IF slGetRow = %FALSE THEN GOTO ENDING
    ITE.ITEM_NO   = slF(01)
    ITE.ITEM_NAME = slF(02)
    ITEM_NAME = SHRINK$(ITEM_NAME)

SELECT_ITEM:
    IF INSTR(ITEM_NAME, KEYWORD1) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD3) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD3) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD4) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD4) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD5) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD5) = 0 THEN GOTO FETCH_NEXT_ITEM
SELECT_ITEM_END:

    PRINT ITEM_NO, ITEM_NAMN
    GOTO FETCH_NEXT_ITEM   
   
ENDING:   
     
     
Example:
KEWORD1 = "FOR"
KEWORD2 = "SS"       

Item Names
1 CHASSI FOR SWITCH    <-- Hit
2 CHASSI-STOMME
3 FORMAT-BAND     
4 KASSERAD OMFORMARE   <-- Hit

/Fim W.

If a client does not call the server in x minutes, you will get an error -18 at the next call.
Is there any good way to stay connected?
/Fim W.

This triggers does not work, what am I doing wrong?
CREATE TRIGGER ARB_TRIGG_05 UPDATE OF FK_KOST ON ARB FOR EACH ROW BEGIN UPDATE VOR SET FK_KOSTN_ARB_SUM = FK_KOSTN_ARB_SUM - OLD.FK_KOSTN + NEW.FK_KOSTN WHERE ORDERNR = NEW.ORDERNR; END
I am making a change of FK_KOST in ARB and want change to update an overruled table VOR.
But FK_KOSTN_ARB_SUM in VOR does not update.

/Fim W.

This works well:
CREATE TRIGGER ARB_TRIGG_01 INSERT ON ARB FOR EACH ROW BEGIN UPDATE VOR SET FK_KOSTN_ARB_SUM = FK_KOSTN_ARB_SUM + NEW.FK_KOSTN WHERE ORDERNR = NEW.ORDERNR; END

But not this:
CREATE TRIGGER ARB_TRIGG_01 INSERT ON ARB FOR EACH ROW WHEN OPKOD = 'LEGO' BEGIN UPDATE VOR SET FK_KOSTN_ARB_SUM = FK_KOSTN_ARB_SUM + NEW.FK_KOSTN WHERE ORDERNR = NEW.ORDERNR; END
I do not understand what it's wrong.
Pls help
/Fim W.

I have two tables. One ART with all data (except inventory on hand) about all parts.
The other table is LPL with inventory on hand with one record for each part and each stock location.
Now I want a list, for each part where total inventory on hand for each part is less then the reorder point.
Is it possible to put these two SQL-statement into one statement?
SELECT PARTNO, SUM(QOH) AS QOH_SUM FROM LPL GROUP BY PARTNO
SELECT PARTNO, REORDEP, QOH_SUM FROM ART WHERE QOH_SUM < REORDP
As you see QOH_SUM a computed term and not a term in any table.

/Fim W.