• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Recent posts

#71
General Board / Re: FreeMyIp.Com very simple f...
Last post by Fredrick Ughimi - June 02, 2022, 06:48:05 AM
Hello CJ,

Thank you for putting your last response here.

I now have serious need to use SQLitening remotely.

QuoteFredrick,
I didn't see this old thread.
Hope you are doing great!!

I am doing fine. Thank you.
#72
Demonstrate using a UDT with a blob column

Read/write files to blob https://sqlitening.planetsquires.com/index.php?topic=9676.msg25764#msg25764
Upsert could also be used  https://sqlitening.planetsquires.com/index.php?topic=9709.msg26107#msg26107

#INCLUDE "sqlitening.inc"
%DropTable=1

TYPE RecordType
 num1 AS LONG
 num2 AS CUX
 time AS STRING * 8
END TYPE

FUNCTION PBMAIN AS LONG 'fim10.bas
LOCAL rec    AS RecordType
LOCAL sKey,s,sPrompt,sTitle AS STRING

slOpen "junk.db3","C"
IF %DropTable THEN slexe "drop table if exists VER"
slexe  "create table if not exists VER(key text unique,blob)"
sKey = "fim"
DO
 sKey = INPUTBOX$(sPrompt,sTitle,sKey)
 IF LEN(sKey) = 0 THEN EXIT FUNCTION
 slexe "begin immediate

 slSel "select key,blob from VER where key=" + WRAP$(sKey,$SQ,$SQ)

 IF slGetRow THEN      'UPDATE
  s = slfn("blob")    'put blob into dynamic string
  TYPE SET rec = s    'set UDT  with dynamic string

  'modify members
  INCR rec.num1
  INCR rec.num2
  rec.time = TIME$
  s=rec                'put UDT into dynamic string
  s=slBuildBindDat(s)  'bind dynamic string
  slexebind  "Update ver Set blob = ? where key=?",s + slBuildBindDat(sKey,"T")
  IF slGetChangeCount = 0 THEN sTitle = "update failed" ELSE sTitle = "Update"
 ELSE
  'modify members
  rec.num1 = 1          'modify member1
  rec.num2 = rec.num1 +1'modify member2
  rec.time = TIME$      'modify member3

  s=rec                'put UDT into into dynamic string
  s=slBuildBindDat(s)  'bind dynamic string
  slexebind "insert into VER values(?,?)",slBuildBindDat(sKey,"T") + s
  IF slGetChangeCount = 0 THEN sTitle = "insert failed" ELSE sTitle = "Insert"
 END IF

 slexe "end"

 slSel "select * from VER where key=" + WRAP$(sKey,$SQ,$SQ)
 DO WHILE slGetRow
  TYPE SET rec = slfn("blob")
  sPrompt = USING$("num1=#&num2=#&&",rec.num1,$CR,rec.num2,$CR,rec.time)
 LOOP
LOOP
END FUNCTION
#73
Insert multiple 50-column records using a 2-dimensional array

#INCLUDE "sqlitening.inc"
MACRO bindt(str)=slbuildbinddat(str,"T")

FUNCTION PBMAIN AS LONG

LOCAL c,r,rows,cols AS LONG
cols = 50
rows = 2

REDIM s(1 TO cols,1 TO rows) AS STRING
slOpen    "junk.db3","C"
slexe    "create table if not exists t1(c1 text,c2 text,c3 text,c4 text,c5 text,c6 text,c7 text,c8 text,c9 text,c10 text,c11 text,"  +_
          "c12 text,c13 text,c14 text,c15 text,c16 text,c17 text,c18 text,c19 text,c20 text,c21 text,c22 text,c23 text," +_
          "c24 text,c25 text,c26 text,c27 text,c28 text,c29 text,c30 text,c31 text,c32 text,c33 text,c34 text,c35 text," +_
          "c36 text,c37 text,c38 text,c39 text,c40 text,c41 text,c42 text,c43 text,c44 text,c45 text,c46 text,c47 text," +_
          "c48 text,c49 text,c50 text)
FOR r = 1 TO rows
  FOR c= 1 TO cols
  s(c,r) = bindt(".")  '<--- INSERT DATA (COLUMN,ROW)
  NEXT
NEXT
slexe "begin exclusive"
slexebind "insert into t1 values(" + REPEAT$(cols-1,"?,") + "?)",JOIN$(s(),""),USING$("V#",UBOUND(s,1))
slexe "end"
LOCAL sRecordSet() AS STRING
slSelAry "select rowid, * from t1 order by rowid",sRecordSet(),"Q44"
? JOIN$(sRecordSet(),$CR)
END FUNCTION

#74
Hello CJ,

Thank you for your response and the brilliant solution you provided.

I should have had a search on the forum before asking the question. I ones asked the same question some years ago and you helped me solved the issue. Thank you.

https://sqlitening.planetsquires.com/index.php?topic=3541.msg23962#msg23962

Kind regards.
#75
Eliminate 50-concatenations (increase ? marks to 50)
#INCLUDE ONCE "sqlitening.inc"
MACRO bind(str)=slbuildbinddat(str,"T")

FUNCTION PBMAIN AS LONG
 LOCAL sHospitalNo,sSurName AS STRING
 slOpen "junk.db3","C"
 slexe  "create table if not exists t1(c1 text, c2 text)"
 REDIM s(1 TO 50) AS STRING
 s(1) = bind(sHospitalNo)
 s(2) = bind(sSurName)
 slexebind "insert into t1 values(?,?)",JOIN$(s(),"")
END FUNCTION
#76
Hello Guys!

Been a while in here. I still use SQLitening for most of my desktop applications.

I am having the "Statement to long/complex Error". in the code below.

Glad if someone can point me in the right direction into solving this.

Kind regards.

eCode = GetLocalFile(sPicturePath, sPicture)
If eCode Then ? "Picture not found error." + Str$(eCode), %MB_SYSTEMMODAL Or %MB_ICONINFORMATION, VD_App.Title
Errorcode& = slExeBind(slBuildInsertOrUpdate("tblAntenatalBioData", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & "?"), _
slBuildBindDat(sHospitalNo, "T") & _
slBuildBindDat(sSurname, "T") & _
slBuildBindDat(sMaidenSurname, "T") & _
slBuildBindDat(sOthernames, "T") & _     
slBuildBindDat(SQLiteDate(sDate), "T") & _     
slBuildBindDat(sCategory, "T") & _
slBuildBindDat(SQLiteDate(sBirthDate), "T") & _     
slBuildBindDat(sAge, "T") & _     
slBuildBindDat(sConsultant, "T") & _ 
slBuildBindDat(SQLiteDate(sLMP), "T") & _
slBuildBindDat(SQLiteDate(sEDD), "T") & _ 
slBuildBindDat(sAddress, "T") & _ 
slBuildBindDat(sPhoneNo, "T") & _ 
slBuildBindDat(sEthnicGroup, "T") & _ 
slBuildBindDat(sOccupation, "T") & _ 
slBuildBindDat(sGravada, "T") & _ 
slBuildBindDat(sPara, "T") & _ 
slBuildBindDat(sKinName, "T") & _
slBuildBindDat(sKinAddress, "T") & _
slBuildBindDat(sKinPhoneNo, "T") & _ 
slBuildBindDat(sReligion, "T") & _ 
slBuildBindDat(sEnroleeNo, "T") & _ 
slBuildBindDat(SQLiteDate(sCardExpiryDate), "T") & _
slBuildBindDat(sPicturePath, "T") & _     
slBuildBindDat(sMaritalStatus, "T") & _ 
slBuildBindDat(SQLiteDate(sMarriageDate), "T") & _
slBuildBindDat(sHusbandName, "T") & _
slBuildBindDat(sHusbandPhoneNo, "T") & _ 
slBuildBindDat(sHusbandOccupation, "T") & _ 
slBuildBindDat(sBloodGroup, "T") & _ 
slBuildBindDat(sBloodTransfusion, "T") & _
slBuildBindDat(sMenstrualCycle, "T") & _ 
slBuildBindDat(sGenotype, "T") & _
slBuildBindDat(sRhesus, "T") & _
slBuildBindDat(sSpecialComments, "T") & _
slBuildBindDat(sCardiacDisease, "T" ) & _
slBuildBindDat(sKidneyDisease, "T") & _
slBuildBindDat(sRheumaticDisease, "T" ) & _   
slBuildBindDat(sMeasles, "T") & _
slBuildBindDat(sTuberculosis, "T") & _
slBuildBindDat(sOtherIllnesses, "T") & _
slBuildBindDat(sOperations, "T") & _
slBuildBindDat(sFamilyTuberculosis, "T") & _
slBuildBindDat(sDiabetes, "T") & _
slBuildBindDat(sHypertension, "T") & _
slBuildBindDat(sTwins, "T") & _
slBuildBindDat(sOtherDiseases, "T") & _
slBuildBindDat(sPicture, "B") & _
slBuildBindDat(gUsername, ("T")),"E") 

#77
General Board / MOD is % in SQLite
Last post by cj - April 10, 2022, 11:47:45 PM
Example: Get every 7th row
select rowid from parts where rowid%7=0 order by rowid
#78
General Board / MonthName macro
Last post by cj - April 08, 2022, 04:10:20 AM
'https://stackoverflow.com/questions/650480/get-month-from-datetime-in-sqlite

MACRO monthname(ColumnName)="substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m',"+ColumnName+"), -3) month"

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 slopen ":memory:"
 slexe  "create table t1(c1 text)
 slexe  "insert into t1 values(date('now'))"

 MSGBOX slselstr("select " + monthname("c1") + " from t1")
 MSGBOX slselstr("select substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m',c1),-3)as month from t1")

END FUNCTION
#79
General Board / printf with "AS" using same co...
Last post by cj - February 14, 2022, 10:54:17 AM
This example shows 3-ways to get price column to order by numerically
if printf with "AS" using the "price" column name is used.
1. Use no AS and ignore the long column name
2. Specify parts.price in the order by
3. Use a different name with "AS" than the price column

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN AS LONG
 slOpen "junk.db3"
 slexe  "drop table if exists parts"
 slexe  "create table if not exists parts(price integer)"
 slexe  "insert into parts values(1995),(1300),(4995),(101),(703)"

 'if AS is used with same name as column with printf the order by will be alpha
 rs "select printf('%.2f',price*.01) AS price from parts order by price"

 'these order numerically
 rs "select printf('%.2f',price*.01) from parts order by price"                'no AS
 rs "select printf('%.2f',price*.01) AS price from parts order by parts.price" 'use table.column
 rs "select printf('%.2f',price*.01) AS XYZ from parts order by price"         'AS different
 ? gs
END FUNCTION

FUNCTION rs(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF ISFALSE(slSelAry(sql,sArray(),"Q9 E2")) THEN
  gs+= JOIN$(sArray(),$CR) + $CR + $CR
 ELSE
  gs = "error:" + $CR + sql + $CR + $CR
 END IF
END FUNCTION



#80
General Board / FreeMyIp.Com very simple free ...
Last post by cj - February 09, 2022, 09:05:10 AM
Setup machine running SQLiteningServer.exe
1 type ipconfig at command prompt
2 look for IpV4 address under ethernet adapter, example 192.168.0.2
3 router software, port forward 51234 to 192.168.0.2
4 allow an app through windows firewall, click change settings, add sqliteningserver

Create shortcut
1 https://freemyip.com
2 enter unique word "klum", check availability, claim it and copy link provided
3 create new shortcut and paste link provided into location and name it "remote"
4 click "remote" shortcut and browser must return "Ok"
5 slconnect "klum.freemyipcom",51234

Execute "remote" shortcut at Windows startup if remote ip address changes

Fredrick,
I didn't see this old thread.
Hope you are doing great!!