• Welcome, Guest. Please login.
 
July 17, 2019, 04:40:39 pm

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - Fredrick Ughimi

1
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.
2
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.
         
3
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.
4
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?
5
Hello Everyone,

Has anyone here worked with SQLitening & RMChart?

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

6
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.
7
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.
8
You've got Questions? We've got Answers! / Audit Trail
September 11, 2018, 02:51:33 pm
Hello,

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

Writing SQLite Triggers would do?
9
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,
10
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.


11
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.
12
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.

13
You've got Questions? We've got Answers! / Upsert
June 10, 2018, 02:45:35 pm
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
14
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.
15
Hello,

I use the codes (from CJ) below for both inserting and updating an image on a client/server application.
It works fine when the images are on the same client machine. But issues arises when
updating from a different client machine that don't have the images stored on the hard drive, even
when the images are already saved in the table, it gets cleared out when updating.


eCode = GetLocalFile(m_sPixName, m_sPicture)
If eCode Then ? "GetLocalFile error" + Str$(eCode),%MB_SYSTEMMODAL,"GetLocalFile Error"

16
Hello CJ,

I just changed the field name and voil
17
Hello,

I am getting a strange syntax error. I get this in my SQL Statement.

Is there a limit to the number of fields an SQL statement can take? My table has 237 fields. The table was originally 230 and I had no issues until I added 7 more fields. If I remove the 7 fields from my statement it runs without issues.


slSel "SELECT " & _
            "   tblBioData.HospitalNo as BioDataHospitalNo," & _
            "   tblChemicalPathologyRoutine.HospitalNo as ChemicalPathologyRoutineHospitalNo," & _
            "   tblBioData.Surname," & _
            "   tblBioData.Othernames," & _
            "   tblBioData.Age," & _
            "   tblBioData.Sex," & _
            "   tblBioData.HomeAddress," & _
            "   LabNo," & _
            "   Date," & _
            "   Time," & _
            "   Comments," & _ 
            "   Details," & _
            "   ColTime," & _
            "   ColDate," & _
            "   DrName," & _
            "   lSodium," & _
            "   rSodium," & _
            "   sSodium," & _
            "   uSodium," & _
            "   lPotasium," & _
            "   rPotasium," & _
            "   sPotasium," & _
            "   uPotasium," & _
            "   lChloride," & _
            "   rChloride," & _
            "   sChloride," & _
            "   uChloride," & _
            "   lBicarbon," & _
            "   rBicarbon," & _
            "   sBicarbon," & _
            "   uBicarbon," & _
            "   lLithium," & _
            "   rLithium," & _
            "   sLithium," & _
            "   uLithium," & _
            "   lUrea," & _
            "   rUrea," & _
            "   sUrea," & _
            "   uUrea," & _
            "   lUricAcid," & _
            "   rUricAcid," & _
            "   sUricAcid," & _
            "   uUricAcid," & _
            "   lCreatinin," & _
            "   rCreatinin," & _
            "   sCreatinin," & _
            "   uCreatinin," & _
            "   lTCalcium," & _
            "   rTCalcium," & _
            "   sTCalcium," & _
            "   uTCalcium," & _
            "   lICalcium," & _
            "   rICalcium," & _
            "   sICalcium," & _
            "   uICalcium," & _
            "   lMgPhospha," & _
            "   rMgPhospha," & _
            "   sMgPhospha," & _
            "   uMgPhospha," & _
            "   lInOrgPhos," & _
            "   rInOrgPhos," & _
            "   sInOrgPhos," & _
            "   uInOrgPhos," & _
            "   lTCholeste," & _
            "   rTCholeste," & _
            "   sTCholeste," & _
            "   uTCholeste," & _
            "   lHDLCholes," & _
            "   rHDLCholes," & _
            "   sHDLCholes," & _
            "   uHDLCholes," & _
            "   lLDLCholes," & _   
            "   rLDLCholes," & _
            "   sLDLCholes," & _
            "   uLDLCholes," & _
            "   lTriglycer," & _
            "   rTriglycer," & _
            "   sTriglycer," & _
            "   uTriglycer," & _
            "   lFGlucose," & _
            "   rFGlucose," & _
            "   sFGlucose," & _
            "   uFGlucose," & _
            "   lRGlucose," & _
            "   rRGlucose," & _
            "   sRGlucose," & _
            "   uRGlucose," & _
            "   lGlyHeamo," & _
            "   rGlyHeamo," & _
            "   sGlyHeamo," & _
            "   uGlyHeamo," & _
            "   lBenceJonesProtein," & _
            "   rBenceJonesProtein," & _
            "   sBenceJonesProtein," & _
            "   uBenceJonesProtein," & _
            "   lGlucose," & _
            "   rGlucose," & _
            "   sGlucose," & _
            "   uGlucose," & _
            "   lProtein," & _
            "   rProtein," & _
            "   sProtein," & _
            "   uProtein," & _
            "   lCChloride," & _
            "   rCChloride," & _
            "   sCChloride," & _
            "   uCChloride," & _
            "   lOccultBld," & _
            "   rOccultBld," & _
            "   sOccultBld," & _
            "   uOccultBld," & _
            "   lFOthers," & _
            "   rFOthers," & _
            "   sFOthers," & _
            "   uFOthers," & _
            "   lAmylase," & _
            "   rAmylase," & _
            "   sAmylase," & _
            "   uAmylase," & _
            "   lTAcidPhos," & _
            "   rTAcidPhos," & _
            "   sTAcidPhos," & _
            "   uTAcidPhos," & _
            "   lPAcidPhos," & _
            "   rPAcidPhos," & _
            "   sPAcidPhos," & _
            "   uPAcidPhos," & _
            "   lAlkPhos," & _
            "   rAlkPhos," & _
            "   sAlkPhos," & _
            "   uAlkPhos," & _
            "   lCPK," & _
            "   rCPK," & _
            "   sCPK," & _
            "   uCPK," & _
            "   lGammaGT," & _
            "   rGammaGT," & _
            "   sGammaGT," & _
            "   uGammaGT," & _
            "   lLDH," & _
            "   rLDH," & _
            "   sLDH," & _
            "   uLDH," & _
            "   lASAT," & _
            "   rASAT," & _
            "   sASAT," & _
            "   uASAT," & _
            "   lALAT," & _ 
            "   rALAT," & _
            "   sALAT," & _
            "   uALAT," & _
            "   lPSA," & _ 
            "   rPSA," & _
            "   sPSA," & _
            "   uPSA," & _
            "   lTBilirubi," & _
            "   rTBilirubi," & _
            "   sTBilirubi," & _
            "   uTBilirubi," & _
            "   lCBilirubi," & _
            "   rCBilirubi," & _
            "   sCBilirubi," & _
            "   uCBilirubi," & _
            "   lTProtein," & _
            "   rTProtein," & _
            "   sTProtein," & _
            "   uTProtein," & _
            "   lAlbumin," & _ 
            "   rAlbumin," & _
            "   sAlbumin," & _
            "   uAlbumin," & _
            "   lGlobulin," & _   
            "   rGlobulin," & _
            "   sGlobulin," & _
            "   uGlobulin," & _
            "   l0Min," & _ 
            "   s0Min," & _
            "   u0Min," & _
            "   l30Min," & _
            "   s30Min," & _
            "   u30Min," & _
            "   l60Min," & _
            "   s60Min," & _
            "   u60Min," & _
            "   l90Min," & _
            "   s90Min," & _
            "   u90Min," & _
            "   l120Min," & _   
            "   s120Min," & _
            "   u120Min," & _
            "   l150Min," & _
            "   s150Min," & _
            "   u150Min," & _
            "   l180Min," & _
            "   s180Min," & _
            "   u180Min," & _
            "   lUProtein," & _
            "   uUProtein," & _
            "   lUGlucose," & _
            "   uUGlucose," & _
            "   lUBilirubi," & _
            "   uUBilirubi," & _
            "   lUrobilin," & _
            "   uUrobilin," & _
            "   lKetones," & _
            "   uKetones," & _
            "   lpH," & _
            "   upH," & _
            "   lSG," & _
            "   uSG," & _
            "   lBlood," & _
            "   uBlood," & _
            "   lPregTest," & _
            "   uPregTest," & _
            "   lUrProtein," & _
            "   uUrProtein," & _
            "   lUrSodium," & _
            "   uUrSodium," & _
            "   lUrPotasiu," & _
            "   uUrPotasiu," & _
            "   lCreClear," & _
            "   uCreClear," & _
            "   lAsorbicAcid," & _
            "   uAsorbicAcid," & _
            "   lAppearance," & _
            "   uAppearance," & _
            "   lNitrite," & _
            "   uNitrite," & _
            "   lUOthers," & _
            "   uUOthers," & _ 
            "   PusCells," & _ 
            "   RBChpf," & _
            "   Cast," & _ 
            "   YeastCells," & _   
            "   Crystal," & _
            "   Bacteria," & _ 
            "   mOthers," & _
            "   MedLabName," & _
            "   MedLabDate" & _
            "   FROM" & _
            "  tblBioData" & _
            "  INNER JOIN tblChemicalPathologyRoutine ON (BioDataHospitalNo=ChemicalPathologyRoutineHospitalNo)" & _
            "  WHERE ChemicalPathologyRoutineHospitalNo = '" + sHospitalNo + "' AND Date = '" + SQLiteDate(sDate) + "'"
       
[code]

Any suggestions would be appreciated.

Best regards.
18
Hello,

I noticed recently in one of my applications running in a Client/Server mode, users managed to enter duplicate values for fields I had already defined as unique (HospitalNo and PhoneNo). I guess I am doing something wrong. Here is an insight:


slOpen ("HospitalProDB.db3", "C")

slExe Build$("Create Table If Not Exists tblBioData(HospitalNo TEXT COLLATE NOCASE, Registration TEXT, Title TEXT, Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE,", _
"DateCreated TEXT, PhoneNo TEXT, Email TEXT, HomeAddress TEXT, OfficeAddress TEXT, Company TEXT, Occupation TEXT, Sex TEXT, Age INTEGER, AgeClass TEXT,", _
"MaritalStatus TEXT, BirthDate TEXT, Religion TEXT, StateOrigin TEXT, Tribe TEXT, EnroleeNo TEXT, Category TEXT COLLATE NOCASE, Plan TEXT, Entitlements TEXT, EnroleeExpiry TEXT,", _
"HMOName TEXT, LGA TEXT, BirthPlace TEXT, Nationality TEXT, PixName TEXT, KinName TEXT, KinPhone TEXT, KinEmail TEXT, KinAddress TEXT, Relationship TEXT, Username TEXT, Picture BLOB)")

slExe "Create UNIQUE Index If Not Exists BioDatandx1 ON tblBioData(HospitalNo, PhoneNo)"
     
slExe "Create Index If Not Exists BioDatandx2 ON tblBioData(Surname, Othernames, DateCreated)"

'Save Routine:
Errorcode& = slExeBind(slBuildInsertOrUpdate("tblBioData", "?" & $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(sRegistration, "I") & _
      slBuildBindDat(sTitle, "T") & _
      slBuildBindDat(sSurname, "T") & _
      slBuildBindDat(sOthernames, "T") & _     
      slBuildBindDat(SQLiteDate(sDateCreated), "T") & _     
      slBuildBindDat(sPhoneNo, "T") & _     
      slBuildBindDat(sEmailAddress, "T") & _     
      slBuildBindDat(sHomeAddress, "T") & _ 
      slBuildBindDat(sOfficeAddress, "T") & _ 
      slBuildBindDat(sCompany, "T") & _ 
      slBuildBindDat(sOccupation, "T") & _ 
      slBuildBindDat(sSex, "T") & _ 
      slBuildBindDat(sAge, "T") & _ 
      slBuildBindDat(sAgeClass, "T") & _ 
      slBuildBindDat(sMaritalStatus, "T") & _ 
      slBuildBindDat(SQLiteDate(sBirthDate), "T") & _ 
      slBuildBindDat(sReligion, "T") & _ 
      slBuildBindDat(sStateOrigin, "T") & _ 
      slBuildBindDat(sTribe, "T") & _
      slBuildBindDat(sEnroleeNo, "T") & _ 
      slBuildBindDat(sCategory, "T") & _ 
      slBuildBindDat(sPlan, "T") & _
      slBuildBindDat(sEntitlements, "T") & _ 
      slBuildBindDat(sEnroleeExpiry, "T") & _ 
      slBuildBindDat(sHMOName, "T") & _ 
      slBuildBindDat(sLGA, "T") & _
      slBuildBindDat(sBirthPlace, "T") & _
      slBuildBindDat(sNationality, "T") & _
      slBuildBindDat(sPixName, "T") & _
      slBuildBindDat(sKinPhone, "T") & _
      slBuildBindDat(sKinEmail, "T") & _
      slBuildBindDat(sKinAddress, "T" ) & _
      slBuildBindDat(sKinName, "T") & _
      slBuildBindDat(sRelationship, "T") & _
      slBuildBindDat(gUsername, "T") & _                             
      slBuildBindDat(sPicture, ("B")),"E") 

   If Errorcode& = 19 Then
      MsgBox "HospitalNo Already Exists", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title 
      Control Set Focus hfrmBioData1, %ID_FRMBIODATA1_TXTHOSPITALNO
      Exit Method
   End If
     


19
Hello,

I am trying to delete duplicate records in tblBioData from HospitalProDB.


#COMPILE EXE
#DIM ALL
#INCLUDE "SQLitening.Inc"

FUNCTION PBMAIN () AS LONG

    slOpen ("HospitalProDB.db3")

    slExe "Begin"
        slEXE "delete from tblBioData where rowid not in(select  min(rowid) from tblBioData group by HospitalNo, PhoneNo)
    slExe "End"
   
   ? "Duplicates Deleted!"

END FUNCTION         


Then try to copy RowID to HospitalNo Column:


#COMPILE EXE
#DIM ALL

#INCLUDE "SQLitening.INC"

FUNCTION PBMAIN () AS LONG

    slOpen ("HospitalProDB.db3")

    slExe "Begin"
        slEXE "update tblBioData set HospitalNo = RowID"
    slExe "End"

    MSGBOX "Column Copied Successfully!", %MB_TASKMODAL OR %MB_ICONINFORMATION, "Mega-Net HospitalPro"

END FUNCTION
                   


I still get error 19 - HospitalNo and PhoneNo not unigue.

Any suggestion would be appreciated
20
You've got Questions? We've got Answers! / Timestamp
September 06, 2017, 10:04:48 pm
Hello,

How can one insert a timestamp ("datetime('now','localtime')") into a table using slBuildInsertOrUpdate and Insert statements?

This works pretty well:


slExe  "insert into Table (TimeSent) values(datetime('now','localtime'));


But I have issues with using multiple fields:


slExe  "insert into Table (TimeSent, Sender, Receiver, Message) values(datetime('now','localtime'), sSender, sReceiver, sMessage);