sqlite question

[hessu]

input "välj song ";i$

query$ = "select * FROM songs WHERE song LIKE '";i$;"' "

I got no answer to this query - what's wrong with this

[metro]

Oct 6, 2019 23:09:15 GMT -5 hessu said:

input "välj song ";i$

query$ = "select * FROM songs WHERE song LIKE '";i$;"' "

I got no answer to this query - what's wrong with this

Our resident SQL expert is missing in action ATM.

I'm no expert but you could try a wildcard either side of your variable ie
query$ = "select * FROM songs WHERE song LIKE '%";i$;"%' "

[hessu]

Thank you Metro

Unfortunately I get all then records.

but I'll try more later.

[metro]

Seems to me "like" can be a bit fussy and without the wild card acts the same as "=" (happy to be proved wrong)
 DIM LineItem$(1000):  dim     info$(10,10)      :    dim    d$(2)                    '
   global    DB$,hDB
input " enter Genre say Co for Country or Cl for Classic then press enter  "; a$
   GOSUB [setStruct]
   GOSUB [openDll]

   hDB=createDB(":memory:")

    SQL$ = "CREATE TABLE Singers(Artist,Song,Genre)"
   GOSUB [executeSQL] ' CREATE the TABLE

   GOSUB [INSERTRECORDS]
   GOSUB [DISPLAYALL]
   close #sq3

   WAIT



[LOADINFO] 'GET DISK DATA
   z=1
   open "wid2.txt" for input as #inF
   WHILE eof(#inF)=0
     line input #inF, LineItem$(z)
     z=z+1
   WEND
   close #inF

   RETURN

[INSERTRECORDS]
   row$(1) = "Waylon Jennings ,My Dogs Dead,Country"
   row$(2) = "Willie,Seven Spanish Angels,Country"
   row$(3) ="Fred,Wife has left me,Country"
   row$(4) = "Elton,Leaving on a jet Plane,Classic"

   for  a = 1 to 4
    row$(a) =  "'" ; row$(a) ; "'"
    row$(a) = strRep$(row$(a),",","','")

    SQL$ = "INSERT INTO Singers VALUES (" ; row$(a) ; ")"

    gosub [executeSQL]
   next
   RETURN

[DISPLAYALL]
'SQL$ = "SELECT * FROM Singers where Genre  Like  'Classic'"
'SQL$ = "SELECT * FROM Singers where Genre  Like  'Cla%'"
   SQL$ = "SELECT * FROM Singers where Genre like'%";a$;"%'"

   calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,hDB as long, RS as struct, result as long
   calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
   msg$ = Winstring(result)

   IF instr(msg$,"Error") THEN
       notice msg$
   END IF

   IF RS.Rows.struct THEN
     PRINT str$(RS.Rows.struct); "  Records Found:  ":print

     FOR n = 1 TO RS.Rows.struct
     CALLDLL #sq3, "SQ3_4_LB_RecordsetMoveToRow",n AS long, result AS void
     CALLDLL #sq3, "SQ3_4_LB_GetRecordsetValueOfRow", "_" AS ptr,result AS long

   IF result THEN
     RecFound$=    Winstring(RS.StrAdr.struct)
     print RecFound$
   end if

   CALLDLL #sq3, "SQ3_4_LB_RecordsetMoveNext", result AS void
   NEXT n
   END IF

   CALLDLL #sq3, "SQ3_4_LB_ReleaseRecordset", result AS void
   RETURN

[executeSQL]
   calldll #sq3, "SQ3_4_LB_Execute",SQL$ as ptr, hDB as long,result as long
   IF result = 0 THEN
    calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
    print Winstring(result)
  END IF
  RETURN


[setStruct]
   Struct RS,_
       BOF     as long,_     ' is True when CurrPos = 1
       EOF     as long,_     ' is True when CurrPos = Rows
       Handle  as long,_     ' address of recordset data returned by sqlite3.dll (dont't use)
       Rows    as long,_     ' number of rows in recordset
       Cols    as long,_       ' number of columns in recordset
       CurrPos as long,_     ' current row in recordset
       StrAdr  as long         ' address of data item (pointer to a string)
   RETURN

[openDll]

   Open "SQ3_4_LB.dll" for DLL As #sq3
   calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
   msg$ = Winstring(result)                                                      ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'"
   if instr(msg$,"Error") then                                                     ' If we have an error
       notice msg$ : close #sq3                                                 '    close sqlite and end program
       end
   end if
   RETURN

[connectDB]
   calldll #sq3, "SQ3_4_LB_OpenDB",DB$ as ptr, overwrite as long, hDB as long    ' see if it exist
   If hDB = 0 then
   calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
   connectDB$ = Winstring(result)
   notice connectDB$
   end if
   RETURN

FUNCTION createDB(DB$)
   calldll #sq3, "SQ3_4_LB_CreateDB",DB$ as ptr, overwrite as long, hDB  as ulong
   calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long   'message for testing

    If hDB  = 0 then
     calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
   end if
   createDB=hDB
END FUNCTION

FUNCTION strRep$(str$,rep$,with$)
   ln  = len(rep$)
   ln1 = ln - 1
   i = 1
   while i <= len(str$)
       if mid$(str$,i,ln) = rep$ then
           strRep$ = strRep$ + with$
           i = i + ln1
       else
           strRep$ = strRep$ + mid$(str$,i,1)
       end if
       i = i + 1
   WEND
END FUNCTION




[hessu]

I found then solution at last I think

 This is it    '%"+v$+"%'

for me it was perfeckt. (not my english, I'm living in sweden

[tsh73]

hessu,
Are you program in Liberty BASIC?
Because for Liberty BASIC
 '%"+i$+"%'
and
'%";i$;"%'
is exactly the same.
(; works as string concatenation)

[hessu]

I tried this '%";i$;"%' without luck.

purebasic has these + signs that's why I tried them too

I like liberty basic more than purebasic.

purebasic is complicated

[metro]

Take a look at these options in my code , just use one wildcard (to the right) to see if you achieve what you want
'SQL$ = "SELECT * FROM Singers where Genre Like 'Classic'"
'SQL$ = "SELECT * FROM Singers where Genre Like 'Cla%'"
SQL$ = "SELECT * FROM Singers where Genre like'%";a$;"%'"

[hessu]

I have a good solution as I showed to you, but thanks anyway. It's nice to help others.

I'm waiting the 5.0 Final. I will compile my programs to exe fil