SQLitening Support Forum

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") 


Hello,

Running SQLitening in Client/Server mode, is it possible to send a notification (a Beep or play a wave file) to another computer on the network when I save a file?

Anyone done this before?

Best regards.

Hello,

Just wondering if SQLitening can be used on a LAN and at the same time over the Internet. I imagine a scenario where users (staff) use the SQLitening Application on a LAN while the Boss accesses or uses the application over the Internet from a remote location.

Any ideas? Thanks.

Hello,

I am trying to figure out how one can get the list of debtors from the three tables below using the SQLite Statement.

Insignt:

TotalBill = ServicesBill (Amount) +  tblPharmacyBill (Quantity*SellingPrice-Discount)

If TotalBill > Payments (Amount) then
     ? "Patient is a debtor"
Else
     ? "Patient not a debtor"
End If


'ServicesBill
 slExe "Create Table If Not Exists tblServicesBill(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, Amount REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill
 slExe Build$("Create Table If Not Exists tblPharmacyBill(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Name TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")   

 'Payment
 slExe Build$("Create Table If Not Exists tblPayments(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

Any pointers would be appreciated. Thank you.

Hello,

I am stump trying to figure out how to automatically 'trigger' this sequence of SQL startements. I wonder if Trigger is the best condidate.

'Free room
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $Nul & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)

'Copy record to History Table
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")

Delete Record     
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime <= time('12')"

But triggers are only triggered when an INSERT, DELETE or UPDATE occurs. I want the statements triggered when this conditions are met:

If (sDepartureDate < Date$) Or (sDepartureDate = Date$ And Time$ > "12") Then
....
End If

Any help would be appreciated.

Hello Everyone,

Has anyone tried using SQLitening with Gary Beene's gbChartMaster here?

http://www.garybeene.com/sw/gbchartmaster.htm

Kind regards.

Hello,

With the following slSelAry statement:

slSelAry "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
 "WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName", rsAssets(),"Q9c"

How do I return only AccountName and DebitSUM columns?

Kind regards.

Hello,

I want to group and sum like items. For instance, from the list below:

Cash 1,500.00
Cash 2,500.00
Account Receivable 2,000.00
Account Receivable 3,000.00
Interest Receivable 4,500.00

I  want to get:

Cash 4,000.00
Account Receivable 5,000.00
Interest Receivable 4,500.00

using SQL statement. Grouping and summing like items.

I tried:

slSel "Select * From tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName HAVING SUM(Debit)"

Its grouping, but did not summing. The summing part is where I am having issues.
         

Hello,

I have a tricky situation trying to save values from a textbox and MLG on a FORM into a table at the same time.

On the FORM we have 3 values from Textboxes and 4 values from the MLG. On MLG there are two or three
rows to be saved at a time.

Has anyone done this? Any pointer would be appreciated.

Hello,

Wondering if these two scenarios could work. Program running over the internet with the client machines having their own database as well.

1. Automatically switch between Local and Remote access depending on the availability of internet connection. If internetconnection do remote else do local.

2. Automatically upload records entered in local database to the remote server when the internet connection is available.

Are these scenarios feasible?

Hello Everyone,

Has anyone here worked with SQLitening & RMChart?

I am currently looking to the possibility of creating charts from SQLitening Data.

Hello,

I have an application that runs on a LAN. A Listview is populated with sqlite records by another system on the network.
How can I display the records saved without needing to close and reopen the Listview - auto display the records
saved while Listview is on display.

Best regards.

Hello CJ,

I tried importing csv file to sqlite using your code here:

https://www.sqlitening.planetsquires.com/index.php?topic=9362.msg24699#msg24699

I got Incorrect Function err.


#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
'
FUNCTION PBMAIN () AS LONG     'CSV2Sqlite.bas
  LOCAL sCSVFile,sDataBaseName,sTableName AS STRING
  LOCAL DropTable,QuoteNumbers, counter AS LONG
  sCSVFile      = "DrugsSetup.csv"   'input csv file
  sDataBaseName = "HospitalProDB.db3"  'sqlite database to write to
  sTableName    = "tblDrugsSetup"          'table to write to
  DropTable     = 1             '0=append, 1= start fresh
  QuoteNumbers  = 1             'enclose all columns with $SQ
  counter = Csv2Sqlite _
    (sCsvFile,sDatabaseName,sTableName,DropTable,QuoteNumbers)
  ? USING$("Records in table #,",counter),,"Csv2Sqlite"
END FUNCTION
'
FUNCTION Csv2SQLite ( _
  sCSVFILE AS STRING, _
  sDataBaseName AS STRING , _
  sTableName    AS STRING,  _
  DropTable     AS LONG,    _
  QuoteNumbers  AS LONG) AS LONG

  'Create table with sColumnNames$ = "C1,C2, ..."

  LOCAL x, hFile,cols AS LONG
  LOCAL sInputLine,sColumnNames,sOutputLine,s,sInsert AS STRING
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  LINE INPUT #hFile, sInputLine 'read first line
  cols = PARSECOUNT(sInputLine) 'number of columns
  CLOSE #hFile
  'first line could be column names, using C1,C2, ..."
  FOR x = 1 TO cols
    sColumnNames = sColumnNames + "C" + FORMAT$(x)+ ","
  NEXT
  sColumnNames = LEFT$(sColumnNames,-1) 'your column names
  slOpen sDataBaseName,"C"
  IF DropTable THEN slexe "Drop table if exists " + sTableName
  slexe "Create Table if not exists " + sTableName + "(" + sColumnNames + ")"
  slexe "BEGIN EXCLUSIVE"
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  DO UNTIL EOF(#hFile) 'process line
    LINE INPUT #hFile, sInputLine
    RESET sOutputLine
    FOR x = 1 TO cols
      s = PARSE$(sInputLine,x)
        IF QuoteNumbers THEN        'enclose all columns in $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      ELSEIF IsNumeric(s) = 0 THEN 'only enclose strings with $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      END IF
        sOutputLine = sOutPutLine +  s + ","
    NEXT
    sOutPutLine = LEFT$(sOutputLine,-1)
    sInsert = "Insert into " + sTableName + " values (" + sOutputline + ")"
    REM ? sOutputLine:end 'look at first output record and end
    slExe sInsert
  LOOP
  slEXE "END"
  LOCAL sArray() AS STRING 'return number of inserts
  slSelAry "select count(*) from " + sTableName,sArray(),"Q9c"
  FUNCTION = VAL(JOIN$(sArray(),$CRLF))
END FUNCTION
'
FUNCTION isNumeric(Answer AS STRING) AS LONG
  Answer = TRIM$(Answer) 'may be required if right-justified text
  IF (LEN(Answer) = 0)                  OR _
     (VERIFY (Answer , ".-0123456789")) OR _
     (RIGHT$(Answer,1) = ".")           OR _
     (INSTR(-1,Answer,"-") > 1)         OR _
     (TALLY(Answer,".") > 1) THEN
     EXIT FUNCTION  'exit, return 0 not-numeric
  END IF
  FUNCTION = -1 'numeric
END FUNCTION



Could find such error in the help file.

Hello,

Has anyone done Audit Trail using SQLitening? I searched but I did see anything of that sort here.

Writing SQLite Triggers would do?

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,

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.


#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.

Hello,

I have a table with records and a date column with the date format in this form: Thursday, April 27, 2017.
I would like to change it to 2017-04-27. Been having difficulties dealing with that date format.

Any ideas please.

Hello,

What do I need to do to use SQLite Database on Linux/Apache Web Server PHP5?

I have a desktop application I used SQLitening and PowerBASIC. Now the client wants the SQLite database uploaded on an Apache Web Server and queried from there.

Any hint would be appreciated.

Best regards.

Fredrick Ughimi:
Hello,

I was just playing around with SQLite UPSERT and I got the same error from my codes and as well as those here:

http://www.sqlitening.planetsquires.com/index.php?topic=9709.0

1 = near ON: syntax error

Hi,

I would love to use SQLitening over the internet on a Windows Server Machine.

Please, can someone point me in the right direction.

Best regards.