Problem Creating a SQLite DataBase

[Martin Francom]

I have a program that creates a SQlite DB with several tables.   The create function creates all the tables except one the  "Patients"  table.  I can not see anything wrong in the code.  Can you see any problem in the "Create Patient" section?

Code Select Expand


Function FORM1_COMMAND1_BN_CLICKED ( _
                                   ControlIndex     As Long,  _  ' index in Control Array
                                   hWndForm         As Dword, _  ' handle of Form
                                   hWndControl      As Dword, _  ' handle of Control
                                   idButtonControl  As Long   _  ' identifier of button
                                   ) As Long


'.........Create Apothesoft SQL Database..........

   Local lErr       As String
   Local t1       As String
   Local lDgF      As String
   Local lDrF      As String
   Local lInsF      As String
   Local lMCAF      As String
   Local lDCMF     As String 
   Local lDgMsgF      As String
   Local lPriF      As String
   Local lPtF      As String
   Local lPrcF     As String 
   Local lRfF      As String
   Local lRxF      As String
   Local lSgF      As String
   Local lZipF      As String
   Local lCfgF     As String
   Local lNRecF    As String 
   
   
   slOpen "ApotheSQL.db3", "CE0"
     
   lErr = slGetError
   If Val(lErr) Then
      MsgBox lErr & " In CreateDB Function", 0, "Database Open Error"
      Function = Val(lErr)
      Dialog End HWND_FORM1, 0   'End program
      'Exit Function
   End If

'--------------------------------------------------------------------------------

  lCfgF = "CfNum, CfName, CfStreet, CfCity, CfState, CfZIP, CfPhone, CfFax, CfNCPDP, CfStCSN, " & _
          "CfUseDDI, CfLblType, CfLblPath, CfPrtPath, CfFaxPath, CfClmPath, CFChkQOH, " & _
          "CfUsrLogn, CfUsrAudLog, CfEmail, CfMstPW, CfTecPW, CfAutoSSL, CfASAPtyp, CfASAPbin, " & _
          "CfASAPid, CfASAPpw, CfASAPex, CfASAPc2Lic, CfASAPopt1, CfASAPopt2, CfStaxPct, " & _
          "CfBlnkFld, CfFlag1, CfFlag2, CfFlag3, CfFlag4, CfFlag5"   
   
   t1 = "CREATE TABLE CfgRec ("   & lCfgF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX CfNum ON CfgRec (CfNum);", "E0"
      slExe "CREATE INDEX CFName ON CfgRec (CfName);", "E0"       
   slExe "End"
   MsgBox "Config Created"

'--------------------------------------------------------------------------------

   lDCMF = "DgMsgNum, DgMsg"
   
   t1 = "CREATE TABLE DgMsgs ("   & lDCMF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX DgMsgNum ON DgMsgs (DgMsgNum);", "E0"
   slExe "End"
   MsgBox "Drug Message Created"

'--------------------------------------------------------------------------------     

   lDgF = "DgNum, DgName, DgIDQ, DgID, DgMfgName, DgGenFor, DgGenBrnd, DgUnitTyp, DgCScode, " & _
          "DgType, DgAAC, DgAWP, DgMAC, DgUnPkg, DgUDind, DgSordN, DgXMsg, DgPrcTbl, DgDefSIG, " & _
          "DgLstUpDt, DgQtyOH, DgInfoLnk, DgMsg1C, DgMsg2C, DgMsg3C, " & _
          "DgMsg4C, DgMsg5C, DgMsg6C, DgLockSKU, DgLockAAC, DgLockAWP, DgLockMAC, DgNote, " & _
          "DgRouteAdm, DgDispUnit, DgDoseForm, DgCmpFormTL, DgCmpFormUM, DgCmpDir, "  & _
          "DgCmpNam0, DgCmpIDQ0, DgCmpID0, DgCmpQty0, DgCmpUM0, DgCmpCost0, DgCmpBOC0, " & _
          "DgCmpNam1, DgCmpIDQ1, DgCmpID1, DgCmpQty1, DgCmpUM1, DgCmpCost1, DgCmpBOC1, " & _
          "DgCmpNam2, DgCmpIDQ2, DgCmpID2, DgCmpQty2, DgCmpUM2, DgCmpCost2, DgCmpBOC2, " & _
          "DgCmpNam3, DgCmpIDQ3, DgCmpID3, DgCmpQty3, DgCmpUM3, DgCmpCost3, DgCmpBOC3, " & _
          "DgCmpNam4, DgCmpIDQ4, DgCmpID4, DgCmpQty4, DgCmpUM4, DgCmpCost4, DgCmpBOC4, " & _
          "DgCmpNam5, DgCmpIDQ5, DgCmpID5, DgCmpQty5, DgCmpUM5, DgCmpCost5, DgCmpBOC5, " & _
          "DgCmpNam6, DgCmpIDQ6, DgCmpID6, DgCmpQty6, DgCmpUM6, DgCmpCost6, DgCmpBOC6, " & _
          "DgCmpNam7, DgCmpIDQ7, DgCmpID7, DgCmpQty7, DgCmpUM7, DgCmpCost7, DgCmpBOC7, " & _
          "DgCmpNam8, DgCmpIDQ8, DgCmpID8, DgCmpQty8, DgCmpUM8, DgCmpCost8, DgCmpBOC8, " & _
          "DgCmpNam9, DgCmpIDQ9, DgCmpID9, DgCmpQty9, DgCmpUM9, DgCmpCost9, DgCmpBOC9"       
         
   'We are doing a transaction, sending several SQL statements to the server
   'Build tables and indexes
   t1 = "CREATE TABLE Drugs ("   & lDgF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX DgNum ON Drugs (DgNum);", "E0"
      slExe "CREATE INDEX DgName ON Drugs (DgName);", "E0"         'DgName-DrNum must be unique
      slExe "CREATE INDEX DgID ON Drugs (DgID);", "E0"             'NDC must be made unique
   slExe "End"
   MsgBox "Drug Table Created"

'--------------------------------------------------------------------------------

   lDrF = "DrNum, DrName collate NoCase, DrStreet, DrCity, DrState, DrZip, DrPhoneOff, " & _
          "DrFax, DrSpec, DrBnkCrd, DrID1specQ, DrID1spec, DrID2specQ, DrID2spec, DrDEA, DrMedicaid, " & _
          "DrNPI, DrStateLic, DrTIN, DrMedicare, DrLocCode, DrEmail, DrMsg"
         
   t1 = "CREATE TABLE Doctors ("   & lDrF & ");"
   slExe "Begin"  'This starts the transaction      'Note All Index Fields have Unique Number added
      slExe t1, "E0"
      slExe "CREATE INDEX DrNum ON Doctors (DrNum);", "E0"
      slExe "CREATE INDEX DrName ON Doctors (DrName);", "E0"       'Lname-Fname-DrNum
      slExe "CREATE INDEX DgDEA ON Doctors (DrDEA);", "E0"         'DrDEA-DrNum
      slExe "CREATE INDEX DgNPI ON Doctors (DrNPI);", "E0"         'DrNPI-DrNum
      slExe "CREATE INDEX DgPhone ON Doctors (DrPhoneOff);", "E0"  'DrPhoneOff-DrNum
   slExe "End"
   MsgBox "Doctor Table Created"

'--------------------------------------------------------------------------------

   lInsF = "InsNum, InsName, InsBin, InsPhIDQ, InsPhID, InsPCN, InsVndrCert, InsDefGrp, " & _
           "InsVrsnFrmt, InsHelpPh, InsDrIDQ, InsPTnumR, InsPTnumG, InsLstDtRR, InsCobRule, " & _
           "InsSegExc, InsBOC"           

   t1 = "CREATE TABLE Insurances ("   & lInsF & ");"
   slExe "Begin"  'This starts the transaction         'Note All Index Fields have Unique Number added
      slExe t1, "E0"
      slExe "CREATE INDEX InsNum ON Insurances (InsNum);", "E0"
      slExe "CREATE INDEX InsName ON Insurances (InsName);", "E0"     'InsName-InsNum
      slExe "CREATE INDEX InsBin ON Insurances (InsBin);", "E0"       'InsBin-InsNum
   slExe "End"
   MsgBox "Insurance Table Created"

'--------------------------------------------------------------------------------

   lMCAF = "maPtNum, maQ, msKey, maCode, maDesc, maClass"

   t1 = "CREATE TABLE MedCondAllgs ("   & lMCAF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX maPtNum ON MedCondAllgs  (maPtNum);", "E0"    'Pt-Num is unique
   slExe "End"
   MsgBox "MedConAllgs Table Created"

'--------------------------------------------------------------------------------

   lPrcF = "PrcNum, PrcDesc, PrcDsal, PrcRound, PrcForm, PrcMin"
   
   t1 = "CREATE TABLE PriceTbls ("   & lPrcF & ");"
   slExe "Begin"  'This starts the transaction    'Note All Index Fields have Unique Number added
      slExe t1, "E0"
      slExe "CREATE INDEX PrcNum ON PriceTbls (PrcNum);", "E0"
      slExe "CREATE INDEX PrcDesc ON PriceTbls (PrcDesc);", "EO"
   slExe "End"
   MsgBox "Price Tables Created"

'--------------------------------------------------------------------------------

   lPtF = "PtNum, PtName collate NoCase, PtMI, PtDOB, PtSex, PtStreet, PtCity, PtState, PtZip, " & _
          "PtPhoneH, PtPhoneC, PtPhoneW, PtLOC, PtSmoke, PtPregC, PtDlvr, PtEZO, PtPrcTbl, PtHippa, " & _
          "PtEmail, PtIDQ, PtSSN, PtAltID, PtCSID, PtBnkCrd, PtSigLng, PtPrimCareDrQ, PtPrimCareLN, " & _
          "PtPrimCareDrID, PtIns1Act, PtIns1PlanN, PtSpecial, PtIns1HomPl, PtIns1GrpC, PtIns1CrdN, PtIns1EmpID, PtIns1PCode, " & _
          "PtIns1CdHldRel, PtIns1CdHldLN, PtIns1CdHldFN, PtIns1EligClrC, " & _
          "PtIns2Act, PtIns2PlanN, PtSpecial, PtIns2HomPl, PtIns2GrpC, PtIns2CrdN, PtIns2EmpID, PtIns2PCode, " & _
          "PtIns2CdHldRel, PtIns2CdHldLN, PtIns2CdHldFN, PtIns2EligClrC, " & _
          "PtRecGrp, PtSpecCond, PtDisc, PtLastDr, PtMsg" 
         
   t1 = "CREATE TABLE Patients ("   & lPtF & ");"
   slExe "Begin"  'This starts the transaction     'Note All Index Fields have Unique Number added
      slExe t1, "E0"
      slExe "CREATE INDEX PtNum ON Patients (PtNum);", "E0"
      slExe "CREATE INDEX PtName ON Patients (PtName);", "E0"     'LName-Fname-PtNum
      slExe "CREATE INDEX PtPhone ON Patients (PtPhone);", "E0"   'Phone must be made unique
   slExe "End"
   MsgBox "Patient Created"

'--------------------------------------------------------------------------------
   
   lRfF = "RfStatus, RfNum, RfInsPlanN, RfFillDt, RfQtyFilled, RfDaysSup, RfAAC, RfTotAmtRcd, RfPtPayAmt, " & _
          "RfClmRcnC, RfRPhI, RfTecI, RfClm1InsN, RfClm1Rsp, RfClm1AuthNum, RfInsPay1, " & _
          "RfClm2Ins, RfClm2Rsp, RfClm2AuthNum, RfInsPay2, " & _
          "RfUseByDt, RfDispStatus, RfDurReason, RfDurProf, RfDurResult, RfDurLevEff"
         
   t1 = "CREATE TABLE Refills ("   & lRfF & ");"
   slExe "Begin"  'This starts the transaction     'Note All Index Fields have Unique Number added
      slExe t1, "E0"
      slExe "CREATE INDEX RfNum ON Refills (RfNum);", "E0"
      slExe "CREATE INDEX RfDtNum ON Refills (RfFillDt);", "E0"    'RfFillDate-RfNum
   slExe "End"
   MsgBox "RfRec Table Created"

'--------------------------------------------------------------------------------
   
   lRxF = "RxStatus, RxUpdtC, RxNum, RxPtNum, RxDrNum, RxDgNum, RxSgNum, RxPrcNum, RxTotAmtAuth, RxTotAmtAuth2, " & _
          "RxTolAmtDisp, RxLblCnt, RxPayLock, RxDAW, RxDtWrtn, RxSig1, RxSig2, RxSig3, RxSigX, RxPAuthQ, RxAuth, " & _
          "RxBtyp, RxOrgnC, RxOthrCovC, RxSchdRxID, RxMsg"
           
   t1 = "CREATE TABLE RxRecs ("   & lRxF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX RxNum ON RxRecs (RxNum);", "E0"            'Unique
      slExe "CREATE INDEX RxPtNum ON RxRecs (RxPtNum);", "E0"        'Unique
      slExe "CREATE INDEX RxDtNum ON RxRecs (RxDtNum);", "E0"        'Unique
      slExe "CREATE INDEX RxDgNum ON RxRecs (RxDgNum);", "E0"        'Unique
   slExe "End"
   MsgBox "RxRex Table Created"

'--------------------------------------------------------------------------------

   lSgF = "SgNum, SgCode, SgLine1, Sgline2, SgLine3, " & _
          "SgLine1a, Sgline2a, SgLine3a, " & _
          "SgLine1b, Sgline2b, SgLine3b, " & _
          "SgLine1c, Sgline2c, SgLine3c, "
         
   t1 = "CREATE TABLE SigRecs ("   & lSgF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX SgNum ON SigRecs (SgNum);", "E0"          'Unique
      slExe "CREATE INDEX SgCode ON SigRecs (SgCode);", "E0"        'Unique
   slExe "End"
   MsgBox "Sig Table Created"

'--------------------------------------------------------------------------------

   lZipF = "ZipCode, ZipCity, ZipSate"
   
   t1 = "CREATE TABLE ZipRecs ("   & lZipF & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX ZipCode ON ZipRecs (ZipCode);", "E0"      'Unique
   slExe "End"
   MsgBox "ZipCode Table Created"

'--------------------------------------------------------------------------------

'  lNRecF = "LastDgN, LastDrN, LastPtN, LastSgN, LastPrcN, LastInsN, LastDgMsgN"
' 
'  t1 = "CREATE TABLE LastNumRec ("   & lNRecF & ");"
'  slExe "Begin"  'This starts the transaction
'     slExe t1, "E0"
'     slExe "CREATE INDEX Lnum ON LastNumRec (ROWID);", "E0"     'No index Required just 1 record
'  slExe "End"
'--------------------------------------------------------------------------------

   slClose



End Function

[\code]

I have attached a small FF3 project that demonstrates the problem.  Run the program the look at the database with SQLiteExpert.   You will find that the "Patients" table is
missing.


[Rolf Brandt]

I'll look over it tonight, Marty.

[Paul Squires]

Here is how I would handle such a problem.... I would create the database with all of its indexes, etc using a data manager program like SQLiteExpert. I would then ship that database with my application and use it as a template anytime that I needed to have a new database created. You would do that by simply copying the template database to the name of the new database. Doing it this way takes all of the tedious details (and extra code) out of your application.

(1) Create database using SQLiteExpert.
(2) Save the database in the application's folder (e.g. dbtemplate.bin)
(3) Copy the database when needed (e.g.  FILECOPY "dbtemplate.bin", "ApotheSQL.db3")

Simple, Easy, and way less complicated.

This is the approach that I am using with the accounting application that i am writing. Whenever I create new company files, i simply copy and rename the company database based on an empty template database.

[Martin Francom]

Rolf,  Thanks.  I would appreciate that.  I am very curous as to why this code fails.  I sure can't see any errors in the code.  Must be something I am over looking.

Paul,  The reason I am using this approach to to better understand the workings of SQLitening/SQLite.  When I get to production, I think your method will be the way I will handle it. 

[Rolf Brandt]

Hi Marty,

check line 4 and line 6 of your var 'lPtF'. You have the field 'PtSpecial' defined twice.
See SQLite error attached.

[Paul Breen]

hey everybody:
sqlite expert is 50% off until new years with this coupon HOLIDAY2009
paste in the coupon code box
ho ho ho
pb

[Martin Francom]

Hi Marty,

check line 4 and line 6 of your var 'lPtF'. You have the field 'PtSpecial' defined twice.
See SQLite error attached.

Rolf,  Thanks...   How did you turn on that error checking?

[Rolf Brandt]

The simple way:
slExe t1  ', "E0"
The program will show the error and terminate.

Better:
slExe t1, "E0"
lErr = slGetError
if val(lErr) then
   MsgBox(lErr)
end if
This shows the error, program continues.