Recent Posts

Pages: [1] 2 3 ... 10
1
You've got Questions? We've got Answers! / Re: Two databases
« Last post by Fim on October 05, 2017, 02:26:49 AM »
I did not intend to have two databases open at the same time. Instead, I had thought to be able to switch between them. But thanks for the tips.
/Fim W.
2
Note: slOpen automatically closes any previous open database.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING
FUNCTION PBMAIN () AS LONG
 LOCAL hThread AS LONG
 THREAD CREATE Other(0) TO hThread
 LOCAL s() AS STRING
 slOpen "sample.db3","C"
 slselAry "select count(*) from parts",s(),"Q9c"
 addIt FUNCNAME$ + " " + JOIN$(s(),$CR)
 DO:SLEEP 50:LOOP UNTIL THREADCOUNT = 1
 ? gs,,"Both"
 'PBMAIN 10000
 'Other 2

'Switch databases
 slOpen "junk.db3"
 slselAry "select count(*) from t1",s(),"Q9c"
 ? JOIN$(s(),$CR)

 slOpen "sample.db3"
 slselAry "select count(*) from parts",s(),"Q9c"
 ? JOIN$(s(),$CR)  '10000
END FUNCTION

SUB AddIt(s AS STRING) THREADSAFE
 gs+=s + $CR
END SUB

THREAD FUNCTION Other(BYVAL x AS LONG)
 LOCAL s() AS STRING
 slOpen "junk.db3","C"
 slexe "create table if not exists t1(c1)
 slexe "insert into t1 values('test')"
 slselAry "select count(*) from t1",s(),"Q9c"
 AddIt FUNCNAME$ + " " + JOIN$(s(),$CR)
END FUNCTION
                           
3
You've got Questions? We've got Answers! / Re: Two databases simultaneously requires ATTACH
« Last post by cj on October 04, 2017, 12:24:01 PM »
2 databases cannot be open in the same thread without ATTACH so simultaneously is in question.

Each thread gets its own set of handles as demonstrated here by using multiple threads.
The other methods would be to slClose/slOpen each database or use multiple processes.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING
FUNCTION PBMAIN () AS LONG
 LOCAL hThread AS LONG
 THREAD CREATE Other(0) TO hThread
 LOCAL s() AS STRING
 slOpen "sample.db3","C"
 slselAry "select count(*) from parts",s(),"Q9c"
 addIt FUNCNAME$ + " " + JOIN$(s(),$CR)
 DO:SLEEP 50:LOOP UNTIL THREADCOUNT = 1
 ? gs,,"Both"
 'PBMAIN 10000
 'Other 2
END FUNCTION

SUB AddIt(s AS STRING) THREADSAFE
 gs+=s + $CR
END SUB

THREAD FUNCTION Other(BYVAL x AS LONG)
 LOCAL s() AS STRING
 slOpen "junk.db3","C"
 slexe "create table if not exists t1(c1)
 slexe "insert into t1 values('test')"
 slselAry "select count(*) from t1",s(),"Q9c"
 AddIt FUNCNAME$ + " " + JOIN$(s(),$CR)
END FUNCTION
4
You've got Questions? We've got Answers! / Re: Two databases
« Last post by Fim on October 04, 2017, 09:47:50 AM »
Thanks.
/Fim W.
5
You've got Questions? We've got Answers! / Re: Two databases
« Last post by Bern Ertl on October 04, 2017, 09:28:50 AM »
As long as you have the hard drive space, you can have as many databases as you want.  The app I'm developing right now uses multiple databases to manage different things (licensing, users/logins, global data, individual project data).
6
You've got Questions? We've got Answers! / Two databases
« Last post by Fim on October 04, 2017, 07:29:34 AM »
Can I have two databases simultaneously on a server, such as "Winova.db3" and "Winova-test.db3" without doing anything special on the server? In my progarm, I open one or other database. I think right?

Fim W.
7
You've got Questions? We've got Answers! / Re: Easy Insert/Update using array
« Last post by cj on September 28, 2017, 09:27:53 PM »
In the docs nothing is wrapped with $SQ (single quotes) at the top of the page.

slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123")
slBuildInsertOrUpdate("TableA", "ABC", "ColA")
slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB", "Rowid=1")
slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB", "*")
slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB") and slBuildInsertOrUpdate("TableA", "ABC" & $NUL & "123", "ColA,ColB", "")

At the bottom of the same page the numeric column 003214523 is wrapped.
vStr = "MyCompany" & $NUL & "'003214523'" & $NUL & "MyProduct" & $NUL & "MyType"
8
You've got Questions? We've got Answers! / Upsert using any primary key
« Last post by cj on September 28, 2017, 05:36:13 PM »
It appears to me that Fred wrote a routine to  format for an Upsert when he wrote slBuildInsertOrUpdate!
No duplicate values are allowed for a primary key so the correct 1-record should be found when updating.

The extra work here is because primary key can be in any column and rowid may not exist.
The two causes might be using integer primary key or if without rowid is used.

I could only get to work on any column by wrapping each column of the incoming data in sValues (which doesn't seem correct)
and searching on the primary column without wrapping the search field because the data is already wrapped.
I think I'm doing this backwards, but have struggled getting it to work any other way.

1. Is it normal to wrap each column before passing to slBuildInsertOrUpdate?
sValues = WRAP$("pear" + FORMAT$(recnum),$SQ,$SQ) + $NUL + _   'fruit
             WRAP$(FORMAT$(recnum),$SQ,$SQ)                    + $NUL + _   'random num
             WRAP$(FORMAT$(TIMER),$SQ,$SQ)                                           'timer

Upsert sTable,sValues,sColumns,sWhere,sPrimaryKey



If i didn't wrap each column (parsed out of sValues$) then a search for '1' would not return 1 so record not found would
cause an insert error because the primary column must be unique.
I think everything may be backward, but it works.  If you see an error, please let me know.

Pretty sure which column is the primary key and getting the correct data could be handled better.
sWhere = sPrimaryKey + "=" & WRAP$(slf(colnum),$SQ,$SQ)   'could use slfnx, but have the colnum.

FUNCTION Upsert(sTable AS STRING,sValues AS STRING,sColumns AS STRING, sWhere AS STRING,sPrimaryKey AS STRING) AS LONG

 LOCAL sSql AS STRING, sSearch AS STRING, s AS STRING, i,colnum AS LONG

 FOR i = 1 TO PARSECOUNT(sColumns)  'need primary column number so we use correct values
  s = PARSE$(LCASE$(sColumns),i)   
  IF s = LCASE$(sPrimaryKey) THEN   
   colnum= i                       
   EXIT FOR             
  END IF
 NEXT
 IF colnum = 0 THEN ? "Could not find primary key",,FUNCNAME$:EXIT FUNCTION

 sSearch  = PARSE$(sValues,$NUL,colnum)    'use primary key data parsed from sValues
 s = "SELECT * FROM " + sTable + " WHERE "+ sPrimaryKey + "="+sSearch
 slSEL s

 IF slGetRow THEN
   sWhere = sPrimaryKey + "=" & WRAP$(slf(colnum),$SQ,$SQ)
   slCloseSet
   sSQL = slBuildInsertOrUpdate(sTable, sValues, sColumns,sWhere)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 ELSE
   sSQL = slBuildInsertOrUpdate(sTable, sValues)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 END IF
END FUNCTION
Code: [Select]
$Title = "SQLitening Upsert using primary key in any column"
#INCLUDE "sqlitening.inc"
GLOBAL gs_Show_Executed_Statements AS STRING

'Upsert2.bas
'1 sColumn$     =  fruit,cool,timer
'2 sPrimaryKey$ =  cool

FUNCTION PBMAIN () AS LONG
 slSetProcessMods "E2"
 LOCAL sTable,sValues,sColumns,sWhere,sPrimaryKey AS STRING
 LOCAL WriteLoop,recnum AS LONG

 slOpen  "test.db3","C"
 sTable  = "Food"

 slexe "drop table if exists " + sTable
 '----------------------------------------------------------------------------------------------------
 slexe "create table if not exists " + sTable +"(fruit,cool primary key,timer)without rowid"
1 sColumns = "fruit,Cool,timer"  'column names required
2 sPrimaryKey = "COOL"           'primary key  required
 '----------------------------------------------------------------------------------------------------
 FOR WriteLoop = 1 TO 2 'do this twice to get duplicates
  FOR recnum = 1 TO 11  'write 11 records
   sValues = WRAP$("pear" + FORMAT$(recnum),$SQ,$SQ) + $NUL + _   'fruit
             WRAP$(FORMAT$(recnum),$SQ,$SQ)          + $NUL + _   'random num
             WRAP$(FORMAT$(TIMER),$SQ,$SQ)                        'timer
   Upsert sTable,sValues,sColumns,sWhere,sPrimaryKey
  NEXT
 NEXT
 DIM sArray() AS STRING
 'rowid may not exist with integer primary key or without rowid clause
 slSelAry "select * from " + sTable + " order by " + sPrimaryKey,sArray(),"Q9"
 ? "(Primary key " + sPrimaryKey + ")" + $CR + $CR + _
                    JOIN$(sArray(),$CR) + $CR + $CR +  gs_Show_Executed_Statements,,_
                    $Title
END FUNCTION

FUNCTION Upsert(sTable AS STRING,sValues AS STRING,sColumns AS STRING, sWhere AS STRING,sPrimaryKey AS STRING) AS LONG
 'matches call to slBuildInsertOrUpdate, but needs additional parameter PrimaryKey
 LOCAL sSql AS STRING, sSearch AS STRING, s AS STRING, i,colnum AS LONG
 FOR i = 1 TO PARSECOUNT(sColumns)  'find what column is primary
  s = PARSE$(LCASE$(sColumns),i)    'so case matches
  IF s = LCASE$(sPrimaryKey) THEN   'found primary key
   colnum= i                        'set colnum
   EXIT FOR                         'success exit
  END IF
 NEXT
 IF colnum = 0 THEN ? "Could not find primary key",,FUNCNAME$:EXIT FUNCTION
 sSearch  = PARSE$(sValues,$NUL,colnum) 'svalues are NUL delimited
 s = "SELECT * FROM " + sTable + " WHERE "+ sPrimaryKey + "="+sSearch
 slSEL s
 IF slGetRow THEN
   sWhere = sPrimaryKey + "=" & WRAP$(slf(colnum),$SQ,$SQ)
   slCloseSet
   sSQL = slBuildInsertOrUpdate(sTable, sValues, sColumns,sWhere)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 ELSE
   sSQL = slBuildInsertOrUpdate(sTable, sValues)
   slExe "Begin Immediate"
   slExe sSQL
   slExe "End"
 END IF
 gs_Show_Executed_Statements+=sSQL + $CR
END FUNCTION
9
You've got Questions? We've got Answers! / Re: Easy Insert/Update using array
« Last post by Fredrick Ughimi on September 27, 2017, 05:25:04 PM »
Hello CJ,

This seems to be doing what I want. Do you see any caveat here? This is just the strip down amongst other things.

Code: [Select]
slSEL "SELECT RowID as RecordNo, * FROM tblName WHERE Company = '" + m_sCompany + "' "   
If Len(sRecordNo) <> 0 Then
   sSQL = slBuildInsertOrUpdate("tblName", m_sCompany, "Company", "rowid=" & sRecordNo)
      slExe "Begin"
         slExe sSQL
      slExe "End" 
         Else
    sSQL = slBuildInsertOrUpdate("tblName", m_sCompany)
      slExe "Begin"
          slExe sSQL
      slExe "End"
End if   

Regards,
10
You've got Questions? We've got Answers! / Re: Easy Insert/Update using array
« Last post by Fredrick Ughimi on September 27, 2017, 04:05:29 PM »
Code: [Select]
I am going back to regular insert/update code because of the possible side-effects of INSERT OR REPLACE.

Sure. Me too. Its cool to play safe.
Pages: [1] 2 3 ... 10