Recent Posts

Pages: [1] 2 3 ... 10
1
You've got Questions? We've got Answers! / Re: Saving Images Remotely
« Last post by Fredrick Ughimi on August 21, 2017, 06:38:23 PM »
Hello CJ,

Quote
When "REPLACE INTO" finds a duplicate unique key (other than rowid) the record is deleted.
A new ROWID is assigned and columns passed are insert.  Columns not named become NULL by default.

--If duplicate key found a new rowid is created and only columns passed are inserted;
drop table if exists junktable;
create table if not exists junktable(mykey unique);
replace into junktable values('one');
replace into junktable values('one'');
select rowid, * from junktable;

rowid   mykey
2         one

I always use named columns and I have read a bit of the REPLACE SQL Statement.

I think it suits my style of coding. I intend using it on forms that not heavily used by different users. For instance forms under my settings menu that are only used by Administrator of the application. I am finding ways to reduce my codes in some of my applications especially my Hospital Management Software and ERP. So heaving that they take over 15 minutes to compile.
2
General Board / Upsert
« Last post by cj on August 20, 2017, 09:48:51 PM »
#INCLUDE "sqlitening.inc"
%DropTable = 1
%ShellView = 0

'  INSERT or REPLACE may not handle updates like wanted so wrote
'  GetRowID to return the RowID if a unique key exists.
'
'  Read alot of posts wanting a function like Upsert instead of REPLACE.
'  because REPLACE deletes a row then inserts a new row only with values passed.
'  Any values not passed will become NULL!

'  GetRowID(sTableName,sUniqueKeyColumnName,sKeyValue,sRowId)

FUNCTION PBMAIN () AS LONG   'CJ 8/20/17 no rights reversed. Use at your own risk.

 LOCAL sql,sKey,sRowId AS STRING
 LOCAL sPrompt,sTitle,sTemp AS STRING

 slOpen "junk.db3","C"
 IF %DropTable THEN slexe "drop table if exists t1"
 slexe  "create table if not exists t1(c1 UNIQUE)"
 sTitle= "GetRowID of UNIQUE Column Example"
 sKey = "Don't forget to handle quote ' in input."
 DO
  sKey = INPUTBOX$(sPrompt,sTitle,sKey)
  IF LEN(sKey)=0 THEN EXIT DO
  GetRowId "t1","c1",skey,sRowId
  IF slGetErrorNumber THEN EXIT FUNCTION
  IF LEN(sRowId) THEN
   sTemp = sKey 'orginal value needs to be retained for inputbox
   REPLACE $SQ WITH $SQ + $SQ IN sTemp
   sql = "update t1 set c1='" + sTemp+ "' where rowid ="+sRowId
   slexe sql
   IF slGetChangeCount <> 1 THEN sPrompt = "Update failed" ELSE sPrompt = "Update Success"
  ELSE
   sTemp = sKey 'original value needs to be retained for inputbox
   REPLACE $SQ WITH $SQ + $SQ IN sTemp
   slexe "insert into t1 values('"+sTemp+"')"
   IF slGetChangeCount <> 1 THEN sPrompt = "Insert failed" ELSE sPrompt = "Insert Success"
  END IF
  IF %ShellView THEN Viewer "select rowid,* from t1"
 LOOP

END FUNCTION

SUB GetRowId(sTableName       AS STRING, _
             sColName         AS STRING, _
             BYVAL sKeyValue  AS STRING, _
             sRowId           AS STRING)

 LOCAL sql AS STRING
 sRowID = ""
 REPLACE $SQ WITH $SQ + $SQ IN sKeyValue

 sql = "select rowid from " + sTableName + " where " + sColName + "='" + sKeyValue + "'"
 slSel sql,0,"E1"
 IF slGetErrorNumber THEN EXIT SUB
 IF slGetRow THEN sRowID = slf(1) 'when RowId returns nothing INSERT instead of UPDATE
END SUB

FUNCTION Viewer(SQL AS STRING) AS LONG
 LOCAL hFile AS LONG, sTempfile, sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9 E2") THEN EXIT FUNCTION
 sTempFile=GUIDTXT$(GUID$) + ".tmp"
 hFile = FREEFILE
 OPEN sTempFile FOR OUTPUT AS #hFile
 IF ERR THEN ? ERROR$,,FUNCNAME$:EXIT FUNCTION
 PRINT #hFile, sArray()
 IF ERR THEN ? ERROR$,,FUNCNAME$:EXIT FUNCTION
 CLOSE #hFile
 SHELL "write.exe " + sTempfile
 SLEEP 500
 KILL sTempfile
 IF ERR THEN ? ERROR$,,FUNCNAME$
END FUNCTION


Code: [Select]
#INCLUDE "sqlitening.inc"
%DropTable = 1
%ShellView = 0

'  INSERT or REPLACE may not handle updates like wanted so wrote
'  GetRowID to return the RowID if a unique key exists.
'
'  Read alot of posts wanting a function like Upsert instead of REPLACE.
'  because REPLACE deletes a row then inserts a new row only with values passed.
'  Any values not passed will become NULL!

'  GetRowID(sTableName,sUniqueKeyColumnName,sKeyValue,sRowId)

FUNCTION PBMAIN () AS LONG   'CJ 8/20/17 no rights reversed. Use at your own risk.

 LOCAL sql,sKey,sRowId AS STRING
 LOCAL sPrompt,sTitle,sTemp AS STRING

 slOpen "junk.db3","C"
 IF %DropTable THEN slexe "drop table if exists t1"
 slexe  "create table if not exists t1(c1 UNIQUE)"
 sTitle= "GetRowID of UNIQUE Column Example"
 sKey = "Don't forget to handle quote ' in input."
 DO
  sKey = INPUTBOX$(sPrompt,sTitle,sKey)
  IF LEN(sKey)=0 THEN EXIT DO
  GetRowId "t1","c1",skey,sRowId
  IF slGetErrorNumber THEN EXIT FUNCTION
  IF LEN(sRowId) THEN
   sTemp = sKey 'orginal value needs to be retained for inputbox
   REPLACE $SQ WITH $SQ + $SQ IN sTemp
   sql = "update t1 set c1='" + sTemp+ "' where rowid ="+sRowId
   slexe sql
   IF slGetChangeCount <> 1 THEN sPrompt = "Update failed" ELSE sPrompt = "Update Success"
  ELSE
   sTemp = sKey 'original value needs to be retained for inputbox
   REPLACE $SQ WITH $SQ + $SQ IN sTemp
   slexe "insert into t1 values('"+sTemp+"')"
   IF slGetChangeCount <> 1 THEN sPrompt = "Insert failed" ELSE sPrompt = "Insert Success"
  END IF
  IF %ShellView THEN Viewer "select rowid,* from t1"
 LOOP

END FUNCTION

SUB GetRowId(sTableName       AS STRING, _
             sColName         AS STRING, _
             BYVAL sKeyValue  AS STRING, _
             sRowId           AS STRING)

 LOCAL sql AS STRING
 sRowID = ""
 REPLACE $SQ WITH $SQ + $SQ IN sKeyValue

 sql = "select rowid from " + sTableName + " where " + sColName + "='" + sKeyValue + "'"
 slSel sql,0,"E1"
 IF slGetErrorNumber THEN EXIT SUB
 IF slGetRow THEN sRowID = slf(1) 'when RowId returns nothing INSERT instead of UPDATE
END SUB

FUNCTION Viewer(SQL AS STRING) AS LONG
 LOCAL hFile AS LONG, sTempfile, sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9 E2") THEN EXIT FUNCTION
 sTempFile=GUIDTXT$(GUID$) + ".tmp"
 hFile = FREEFILE
 OPEN sTempFile FOR OUTPUT AS #hFile
 IF ERR THEN ? ERROR$,,FUNCNAME$:EXIT FUNCTION
 PRINT #hFile, sArray()
 IF ERR THEN ? ERROR$,,FUNCNAME$:EXIT FUNCTION
 CLOSE #hFile
 SHELL "write.exe " + sTempfile
 SLEEP 500
 KILL sTempfile
 IF ERR THEN ? ERROR$,,FUNCNAME$
END FUNCTION
3
You've got Questions? We've got Answers! / REPLACE into when duplicate found
« Last post by cj on August 20, 2017, 11:40:31 AM »
When "REPLACE INTO" finds a duplicate unique key (other than rowid) the record is deleted.
A new ROWID is assigned and columns passed are insert.  Columns not named become NULL by default.

--If duplicate key found a new rowid is created and only columns passed are inserted;
drop table if exists junktable;
create table if not exists junktable(mykey unique);
replace into junktable values('one');
replace into junktable values('one'');
select rowid, * from junktable;

rowid   mykey
2         one

4
General Board / Re: CopyDB
« Last post by Fredrick Ughimi on August 19, 2017, 09:16:45 AM »
Hello CJ,

Is it possible to backup a db in remote mode outside the use of SQLiteningServerMonitor?

I tried your DBCopy routines and I got the usual error -8 Access Denied.

Best regards, 
5
General Board / Re: FreeMyIp.Com very simple free DNS routing
« Last post by Fredrick Ughimi on August 19, 2017, 08:38:42 AM »
Hello CJ,

I tried running the code above twice and I got error -16 twice. My internet is up and SQLitening Server is running.

Is there something I am missing?
6
You've got Questions? We've got Answers! / Re: Saving Images Remotely
« Last post by Fredrick Ughimi on August 18, 2017, 03:21:57 PM »
Quote
Sounds like one of the ddoc routines isn't getting the complete path?

Its actually in my save routine and wasn't fully patted.

Works when fully patted.

Code: [Select]
m_sPixName = VD_GetText (hfrmDPRRegistrationForm, %ID_FRMDPRREGISTRATIONFORM_TXTPICTURENAME)
 m_sPixNamePath = Exe.Path$ & "Pictures\" & m_sPixName
 GetLocalFile(m_sPixNamePath, m_sPicture)
       
 m_sSignatureName = VD_GetText (hfrmDPRRegistrationForm, %ID_FRMDPRREGISTRATIONFORM_TXTSIGNATURENAME)
 m_sSignaturePath = Exe.Path$ & "Signatures\" & m_sSignatureName
 GetLocalFile(m_sSignaturePath, m_sSignature)
       

This is beautiful. Thanks a lot, CJ!
7
You've got Questions? We've got Answers! / Re: Saving Images Remotely
« Last post by cj on August 18, 2017, 02:59:02 PM »
Sounds like one of the ddoc routines isn't getting the complete path?
8
You've got Questions? We've got Answers! / Re: Saving Images Remotely
« Last post by Fredrick Ughimi on August 18, 2017, 02:41:20 PM »
CJ

Quote
I don't understand the question since ecode& = GetLocalFile(sFilename as string, s as string) only reads.
Checked ecode& = 0  when calling GetLocalFile?
Checked ecode& = 0 when calling PutLocalFile?
Get rid of slSetProcessMods "L0" and "L1"?

All tested ok and I have long gotten rid of slSetProcessMods "L0" and "L1".

I noticed that having the Signature and Picture images in different directory is what seems to be causing the issue.
The issue goes away when they are Placed in the same directory. Don't know why this is.
9
You've got Questions? We've got Answers! / Re: Saving Images Remotely
« Last post by cj on August 18, 2017, 01:51:05 PM »
I don't understand the question since ecode& = GetLocalFile(sFilename as string, s as string) only reads.

Checked ecode& = 0  when calling GetLocalFile?
Checked ecode& = 0 when calling PutLocalFile?
Get rid of slSetProcessMods "L0" and "L1"?

10
You've got Questions? We've got Answers! / Re: Saving Images Remotely
« Last post by Fredrick Ughimi on August 18, 2017, 12:42:18 PM »
CJ,

I tried using the GetLocalFile() and I noticed that it only saves one image out of two images presented to it.
For instance I want to save a Picture and Signature images:

Code: [Select]
m_sPixName = Ughimi.jpg
 GetLocalFile(m_sPixName, m_sPicture)
'--------------------------------------------'
 m_sSignatureName = Signature1.jpg
 GetLocalFile(m_sSignatureName, m_sSignature)

Only the Signature1.jpg would saved in its field. But if I comment out:

Code: [Select]
m_sPixName = "Ughimi.jpg"
 GetLocalFile(m_sPixName, m_sPicture)
'--------------------------------------------'
 'm_sSignatureName = "Signature1.jpg"
 'GetLocalFile(m_sSignatureName, m_sSignature)

Ughimi.jpg would be saved in it's field.

How do I fix that?


     
Pages: [1] 2 3 ... 10