• Welcome, Guest. Please login.
 
September 18, 2020, 06:07:47 am

News:

Welcome to the SQLitening support forums!


Deleting encrypted item

Started by Fim, September 23, 2015, 12:59:11 pm

Previous topic - Next topic

Fim

I am having trouble removing an encrypted item.

Here is the DDL translated to english at the right:


CREATE TABLE Ordlista(
    Langd       Integer     not null ,   Length
    Ord         Text        not null,    Word
    Grupp       Text        not null,    Group
    Ordlista_nr Integer     not null,    Word list n:o
    Unique(Ord, Ordlista_nr)); 

   
I add a word to my tabel in this way:
 
 
FUNCTION SKRIV_ORD (BYREF ORD AS STRING, BYREF SPRAK AS STRING, BYREF GRUPP AS STRING, BYREF NR AS LONG) AS LONG
'===============================================================================================================
'
    DIM LANGD                           AS STRING
    DIM SVAR                            AS LONG

STARTSEKVENS:
    LANGD = STR$(LEN(TRIM$(ORD)))
    slSetProcessMods "K" + KRYPTERINGSNYCKEL_ORD

BYGG_UPP_POSTEN:
    sStatement  = "insert into Ordlista values(?,?,?,?)" '4-columns
    BIND_DATA1   = slBuildBindDat(LANGD,      "I")  + _
                   slBuildBindDat(TRIM$(ORD), "TN") + _
                   slBuildBindDat(GRUPP,      "T")  + _
                   slBuildBindDat(STR$(NR),   "I")

SKRIV_IN:
    SVAR = slExeBind(sStatement,BIND_DATA1, "E0")
    IF SVAR = 19 THEN
        SKRIV_ORD = 1
        EXIT FUNCTION
    END IF
    IF SVAR <> 0 THEN
        FELTEXT = "Fel i SKRIV_ORD. Svar=" + STR$(SVAR) + "  LANGD=" + LANGD + "  GRUPP=" + GRUPP + "  ORD=" + ORD
        CALL SQL_FEL
        EXIT FUNCTION
    END IF
    SKRIV_ORD = 0

END FUNCTION


and it works as i should.

Then I will delete the word and have tried this:
 

FUNCTION BORTTAG_ORD (BYREF ORD AS STRING, BYREF SPRAK AS STRING, BYREF GRUPP AS STRING, BYREF NR AS LONG) AS LONG
'===============================================================================================================
'
    DIM LANGD                           AS STRING
    DIM SVAR                            AS LONG

STARTSEKVENS:
    LANGD = STR$(LEN(TRIM$(ORD)))
    slSetProcessMods "K" + KRYPTERINGSNYCKEL_ORD

BYGG_UPP_POSTEN:
    sStatement  = "delete from Ordlista values(?,?,?,?)" '4-columns
    BIND_DATA1   = slBuildBindDat(LANGD,      "I")  + _
                   slBuildBindDat(TRIM$(ORD), "TN") + _
                   slBuildBindDat(GRUPP,      "T")  + _
                   slBuildBindDat(STR$(NR),   "I")

BORTTAGER:
    SVAR = slExeBind(sStatement,BIND_DATA1, "E0")    <=============    The message box is showing the value 1
    MSGBOX "BORTTAG_ORD SVAR=" + STR$(SVAR)
    IF SVAR = 19 THEN
        BORTTAG_ORD = 1
        EXIT FUNCTION
    END IF
    IF SVAR <> 0 THEN
        FELTEXT = "Fel i BORTTAG_ORD. Svar=" + STR$(SVAR) + "  LANGD=" + LANGD + "  GRUPP=" + GRUPP + "  ORD=" + ORD
        CALL SQL_FEL
        EXIT FUNCTION
    END IF
    BORTTAG_ORD = 0

END FUNCTION


Pls help me
Fim W.
   
Fim W

Bern Ertl

delete from Ordlista values(?,?,?,?)

Is this correct SQL syntax?  Shouldn't it be:

delete from Ordlista WHERE Langd = ? AND Ord = ? AND Grupp = ? AND Ordlista_nr = ?

Are you able to select the rowID for the record and then delete using the rowid?

Fim

 I should be able to find the row id of the word I want to remove by reading each word with the current length. I'll tell you later how it goes.
Fim W.
Fim W

cj

September 29, 2015, 02:56:12 am #3 Last Edit: September 29, 2015, 03:03:23 am by cj
Does this help?
Good Morning.

$EncryptWord = "NC"
FUNCTION PBMAIN AS LONG
  IF SearchWord("WordTable","HEIDI",Record) THEN
    slexe "delete from WordTable where rowid="+STR$(record)
  END IF
END FUNCTION

FUNCTION SearchWord(sTableName AS STRING, sWord AS STRING,RecNum AS LONG) AS LONG
  LOCAL sEncrypt,sBind AS STRING
  sEncrypt = slConvertDat(sWord,$EncryptWord)
  sBind = slBuildBindDat(sEncrypt)
  slSelbind "select rec_num from " + sTableName + " where word=? limit 1",sBind
  IF slGetRow THEN
     RecNum = VAL(slf(1))
     FUNCTION = -1
  END IF
END FUNCTION                                   

Fim

Cj.

I have been successful to get the ROWID by reading each word with a certain length.
Then I deleted the entry using the ROWID.
But your solution with Serch to get the ROWID is much better.
Many thanks for the help.

Fim W.
Fim W

cj

Glad if it helped.

I was thinking about creating a similar file for sending large amounts of information by just using a number.
WITHOUT ROWID would cut the table in half because only 1 B-Tree would be needed and the one
B-Tree that is used would place the key and data into only the key area.
It should be twice as fast and half the size.  Not sure it applies to you, but it would be efficient.

Create table if not exists WordTable(rec_num Integer, word Primary Key UNIQUE) WITHOUT ROWID

Fim

Cj
I will test "Create table if not exists WordTable(rec_num Integer, word Primary Key UNIQUE) WITHOUT ROWID
Fim W

cj

#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
$BindFlag ="" 'C=compress,N=encrypt,CN,or blank
%InsertThisMany = 1000
%dropTable=1
$HighKey = CHR$(7,"<-- key count is in this unique key")
$EncryptKey = STRING$(32,0) 'only needed if 'N' used in $Bind

FUNCTION PBMAIN AS LONG
  LOCAL sKey,sMightBeEncrypted AS STRING
  LOCAL x,num AS LONG
  slSetProcessMods "K" + $EncryptKey
  slopen "dictionary.db3","C"
  IF %droptable THEN slexe "drop table if exists wordtable"
  slexe "create table if not exists WordTable(num integer,word text primary key) without rowid"
  slexe "begin exclusive"
  FindWord $HighKey,num  'get highest record
  FOR x = 1 TO %InsertThisMany
    INCR num               'increment it
    Insertword USING$("record #",num) 'insert
  NEXT
  slexe  "end"           'end transaction
  DisplayAll             'display all records
END FUNCTION
'
FUNCTION FindWord(sWord AS STRING,RecNum AS LONG) AS LONG
  LOCAL sBind AS STRING
  sBind = slBuildBindDat(sWord,$BindFlag)
  slSelbind "select num from Wordtable where word=? limit 1",sBind
  IF slGetRow THEN
     RecNum = VAL(slf(1))
     FUNCTION = -1
  END IF
END FUNCTION

FUNCTION InsertWord(skey AS STRING) AS LONG
  LOCAL num AS LONG, rsBindDats,sql AS STRING
  FindWord $HighKey,num   'highest record
  INCR num                      'increment it
  rsBindDats = slBuildBindDat(sKey,$BindFlag) 'replace ? mark(s)
  sql = "insert into wordtable values("+FORMAT$(num)+",?)"
  IF slexebind(sql,rsBindDats,"E0") = 0 THEN
    UpdateRowCounter
  ELSE 'error inserting word
    FUNCTION = slGetErrorNumber
  END IF
END FUNCTION
'
FUNCTION UpdateRowCounter AS LONG
  LOCAL sKeyEncrypt,sBind,sql AS STRING
  LOCAL RecNum AS LONG
  FindWord($HighKey,RecNum) 'highest num
  sBind = slBuildBindDat($HighKey,$BindFlag)
  sql = "update wordtable set num=" + FORMAT$(RecNum+1) + " where word=?"
  slexebind sql,sBind$
  IF slGetChangeCount = 0 THEN   'update failed
    sql = "insert into wordtable values("+FORMAT$(RecNum+1)+",?)"
    slexebind sql,sBind$
    IF slGetChangeCount = 0 THEN 'insert failed
      ? sql,%MB_ICONERROR,FUNCNAME$ + " unable to update or insert"
    END IF
  END IF
END FUNCTION

FUNCTION Displayall AS LONG
  LOCAL sMightBeEncrypted AS STRING
  LOCAL sArray() AS STRING
  IF INSTR($BindFlag,"C") THEN sMightBeEncrypted = "U2"  'column 2
  IF INSTR($BindFlag,"N") THEN sMightBeEncrypted+= "D2"
  slSelAry "select * from wordtable order by num",sArray(),"Q9" + sMightBeEncrypted
  Array2Disk sArray(),"temp.txt",1
END FUNCTION

FUNCTION Array2Disk(sArray() AS STRING, sFileName AS STRING, OPTIONAL ViewFile AS LONG) AS LONG
  LOCAL hFile AS LONG
  hFile = FREEFILE
  OPEN sFileName FOR OUTPUT AS #hFile
  IF ERR THEN FUNCTION = ERR:BEEP:EXIT FUNCTION
  PRINT #hFile, sArray()
  IF ERR THEN BEEP:FUNCTION = ERR
  CLOSE #hFile
  IF ERR = 0 AND ISMISSING(ViewFile) = 0 THEN
     SLEEP 500
     Sheller sFileName
  END IF
END FUNCTION

SUB Sheller(sFileName AS STRING)
  ShellExecute (%NULL, "OPEN", sFileName+$NUL, BYVAL %NULL, CURDIR$, %SW_SHOWNORMAL)
END SUB

Fim

cj
Right now I'm moving to Ystad, so it takes a while before I engage the issue again.
Fim W

Fim

I chose the solution by reading the words of the current length, and then use the row IDs for removal.
Fim W.
Fim W

cj

If words are unique what was wrong with using the index?
Scanning the table for all words with the same length then comparing again to the word?
Something else needs the word length or the searches are not unique?






Fim

Now I have found the final solution:

SOK_ROWID:
    sStatement  = "SELECT ROWID from ordlista where ord = ? and Ordlista_nr =" +  str$(ordlista_nr)
    BIND_DATA1   = slBuildBindDat(TRIM$(ORD), "TN")
    SVAR = SlSelBind(sStatement, BIND_DATA1)
    SVAR = slGetRow
    RADNR = VAL(SLFX(1))

BORTTAGET:
    SVAR = SLEXE ("DELETE FROM ORDLISTA WHERE ROWID = " + $SQ + STR$(RADNR) + $SQ)
    IF SVAR = 0 THEN BORTTAG_ORD = 0


Fim W

cj

WHERE ROWID = " + $SQ + STR$(RADNR) + $SQ
ROWID is numeric so $SQ is not needed.



cj

slexe "delete ..." does not return the results of the delete.
Example using slGetChangeCount.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL result AS LONG
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe "create table if not exists t1(c1 integer primary key)"
  slexe "insert into t1 values(8)"
  slexe "insert into t1 values(9)"
  slexe "insert into t1 values(10)"
  slexe("delete from t1")
  ? "changes " + STR$(slGetChangeCount) '3
END FUNCTION