SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Pages: 1 ... 8 9 [10]
 91 
 on: August 20, 2018, 12:14:01 PM 
Started by Fredrick Ughimi - Last post by Bern Ertl
Fredrick, is SQLitening logging connections to the SQLientingServer.LOG file?  Have you inspected it to verify what connections are being logged?  Is it possible that your application is opening more than one connection (ie. in a threaded function)?

 92 
 on: August 20, 2018, 12:11:01 PM 
Started by Fredrick Ughimi - Last post by Bern Ertl
Thanks cj.  I missed the single quotes.  That's what I get for replying when I was in a hurry. 

 93 
 on: August 17, 2018, 05:40:38 PM 
Started by Fredrick Ughimi - Last post by Fredrick Ughimi
Hello CJ,

Thank you for throwing more light.

Quote
The purpose of Upsert is to update the other columns if the key is found without changing the key or rowid

Yeah I found that out earlier. I am a bit 'disappointed'. Not what I earlier thought it was.

Quote
I like the Upsert statement, but will probably stick with searching for a key and acting upon the search result.

I need to go the search path as well.

Best regards.

 94 
 on: August 17, 2018, 11:12:23 AM 
Started by Fredrick Ughimi - Last post by cj
Quote
Now the above code only Inserts a new record. Supposing I want to Update (Replace) a record?
« Last Edit: Today at 03:02:38 AM by Fredrick

The purpose of Upsert is to update the other columns if the key is found without changing the key or rowid

This demo shows using Upsert where rowid stays the same if the key is found
vs
Replace Into where rowid will change if the key is found

There is nothing wrong using 2 steps since it is your program and this is an embedded database system.

I find nothing wrong with this:
1. Search by a  key/subject
2. If found UPDATE using rowid else INSERT

Note:
The key/subject can also be changed using Upsert so the rowid won't change.
But, if the new key (in the ON Conflict) exists an error will occur.
I don't think Upsert is meant to be used to change the key if it could cause a CONFLICT of the CONFLICT.
Example:
sql = "INSERT INTO t1 VALUES(" +_    'UPSERT
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"     +_
         "ON CONFLICT(c1_key) DO UPDATE SET c1_key = 'NewKey', c2_data = '" + sData + "'"

We can do anything in PowerBASIC after checking for an error using IF or CASE.
I like the Upsert statement, but will probably stick with searching for a key and acting upon the search result.
Others may HAVE to use all sql statements.  Upsert may not be the best solution.

In this demo program set %Upsert=0 to test REPLACE INTO instead of Upsert method.

Code: [Select]
'Method1. Upsert retains the rowid if an insert conflict and updates columns using ON CONFLICT
'Method2. Replace Into deletes existing row if found and inserts a new row

'Method3. Two step method may give you more options in my opinion (no example in this program)
'   Search for key/sSubject
'   If found THEN
'     UPDATE Table Set ... Where rowid=found
'   ELSE
'     Insert Into Table ...
'   End If
'
' -------------------------------------------------------------------
'   Set %Upsert=1 to test usng Upsert method
'   Set %Upsert=0 to test using Replace Into (which many do not like)
'--------------------------------------------------------------------

'  Notice rowid stays the same with Upsert if the key is found
'  Notice rowid changes if the key is found with Replace Into

#DIM ALL
#COMPILE EXE 'upsert2.bas
#INCLUDE "sqlitening.inc"
%Upsert = 0 '0=Use Replace Into which may do not like  '1=Use Upsert method to retain rowid
FUNCTION PBMAIN () AS LONG
 LOCAL sql,sSubject,sData,sTitle AS STRING
 slOpen "junk.db3","C"
 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1_Key unique, c2_Data)"
 sSubject = "Chemistry"
 sTitle = IIF$(%Upsert,"UPSERT METHOD (rowid's stay the same)","REPLACE INTO (rowid's change)")
 DO
  sSubject = INPUTBOX$("Please enter subject",sTitle,sSubject)
  IF LEN(sSubject) = 0 THEN EXIT DO
  sData    = "New time is " + TIME$
  IF %Upsert THEN
  sql = "INSERT INTO t1 VALUES(" +_    'UPSERT
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"     +_
         "ON CONFLICT(c1_key) DO UPDATE SET c2_data = '" + sData + "'"
  ELSE
  sql = "REPLACE INTO t1 VALUES(" +_   'REPLACE INTO
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"
  END IF

  slexe sql
  IF slGetChangeCount = 0 THEN ? "Serious Error"
  REDIM sArray(0) AS STRING
  slSelAry "select rowid,* from t1",sArray(),"Q9c"
  ?  sql + STRING$(2,$CR) + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL, sTitle
 LOOP
 ? "If you don't like Upsert or Replace Into, see method 3",,"Have a good day"
END FUNCTION

 95 
 on: August 17, 2018, 02:11:22 AM 
Started by Fredrick Ughimi - Last post by Fredrick Ughimi
Hi CJ,

That worked! Thanks.

Code: [Select]
#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 LOCAL sSubject AS STRING
 slOpen "junk.db3","C"
 'slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 unique)"
 sSubject = "Chemistry"


 slEXE "INSERT INTO t1(c1) VALUES('" + sSubject + "') ON CONFLICT(c1) DO NOTHING;"

 IF slGetChangeCount = 0 THEN

    ? "Record Already Exist!", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$

    EXIT FUNCTION

 ELSE

    ? "Record Saved!", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$

 END IF

END FUNCTION
                               


Now the above code only Inserts a new record. Supposing I want to Update (Replace) a record?

 96 
 on: August 16, 2018, 07:36:18 PM 
Started by Fredrick Ughimi - Last post by cj
>slEXE "insert into t1 values(" + sSubject  + ") on conflict(c1) do nothing"
  slEXE "insert into t1 values('" + sSubject + "') on conflict(c1) do nothing"

Attempt 20 inserts
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 LOCAL x,duplicates AS LONG
 slOpen "junk.db3","C"
 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 unique)"

 FOR x = 1 TO 10
  slEXE "insert into t1 values('Maths')","E0"                       'ignore error
  IF slGetChangeCount = 0 THEN INCR duplicates

  slEXE "insert into t1 values('Maths') on conflict(c1) do nothing" 'or upsert
  IF slGetChangeCount = 0 THEN INCR duplicates
 NEXT

 Viewer "select rowid,* from t1",duplicates

END FUNCTION

SUB Viewer(sql AS STRING,duplicates AS LONG)
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9"
 ? sql + $CR + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Duplicates" + STR$(duplicates)
END SUB


 97 
 on: August 16, 2018, 05:01:56 PM 
Started by Fredrick Ughimi - Last post by Fredrick Ughimi
Hello Bern,

Thank you for your response. i tried some other options except except the one you provided.

Now I get a new error:


 98 
 on: August 16, 2018, 04:30:54 PM 
Started by Fredrick Ughimi - Last post by Bern Ertl
slEXE "insert into t1 values(" + sSubject + ") on conflict(c1) do nothing"

 99 
 on: August 16, 2018, 09:37:50 AM 
Started by Fredrick Ughimi - Last post by Fredrick Ughimi
Hello,

Has anyone used the MaxConnections in the SqliteningServer settings to a good effect. I assume the MaxConnections is equal to total number of systems on the network including the server using the application.

I have this scenario:

1. Total Computers on the network (including the server) = 5
2. MaxConnections=5
3. Actual Connections = 4 (I get maximum connection exceeded error) *


*I noticed that if there are just 4 computers connected and using the application. I get maximum connection exceeded error.

Note: I made sure no multiple instances of the application is allowed to run on a system.

Has anyone experience this before?

Best regards,

 100 
 on: August 16, 2018, 09:25:08 AM 
Started by Fredrick Ughimi - Last post by Fredrick Ughimi
Hello,

I am trying my hands on the new UPSERT and I am having issues trying to use a string variable to represent my value.

Code: [Select]
#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 LOCAL sSubject AS STRING
 slOpen "junk.db3","C"
 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 unique)"
 sSubject = "Maths"

 slEXE "insert into t1 values(sSubject) on conflict(c1) do nothing"

END FUNCTION         


Any push in the right direction would be very much appreciated.

Best regards.



Pages: 1 ... 8 9 [10]