• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu
Menu

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.

Show posts Menu

Messages - Jeffrey Smith

#1
Thanks for the post CJ.  I'll read the documentation on the sqlite3_progress_handler.  The C example might as well be in Klingon for me :).  Powerbasic here.

Jeff
#2
I have rather large databases that sometimes take 15 seconds to gather distinct records and sort them.  Is there anyway to stop the query once it has started?  I would like the user to be able to tap the ESC key to interrupt the process if needed.  Several of these queries are ran back to back, so interrupting in between is not a problem.  I guess what I'm asking is if the Query can allow the application to check for keyboard and other events during a query?

Jeff
#3
I guess I didn't need to use slAttach.  I opened all 10 databases I wanted to read from did, a slSel for each database with a different set number.  I opened the database I wanted to write to last.  Using slGetRow requires a setnumber and so does slf().

#4
I would like to open up to 10 databases read only and one more database to write the results.   I need to access all databases at once, sum the values from each record, then write the results.  I'm guessing I need to use slAttach.  The 10 databases have one table each, all named "Habitat".  I think that using the set number I can access each of the 10 databases using "slGetrow SetNum".  How do I reference the database I want to write to on the last line without creating a new table in one of the other 10 databases?


B = Dir$(CombinePath$)
If Len(B) > 0 Then
Kill CombinePath$
End If
SlOpen CombinePath$, "C"
 
  For Y = 1 To NumRfiles
    Afile$ = ReachPaths(Y) & ReachFiles(Y)
    A = "D" & Using$("#",Y)
    slAttach AFile$,A
    slSel "SELECT * FROM Habitat", Y    'With set number
    If Y = 1 Then
    ColumnNames = slGetTableColumnNames("Habitat")
    Replace $Nul With "," In ColumnNames
    End If
  Next Y
 
'CombinePath$
  slEXE "Create Table If Not Exists HabOut (" & ColumnNames & ")"
#5
Thank you.  Too Nice!
#6
Is it possible to get Unique records?  I would like to save the different values from a field into an array.  In the example below, the field "Node" has thousands of values that are the same.

Example Code

HabFields = "WYType,Node,HydSet,FS,Year,Month,JulDay,Discharge"
  For X = 1 To NumLS%
    HabFields = HabFields & "," & LSNames$(L%)
  Next X
  slEXE "Create Table If Not Exists Habitat (" & HabFields & ")"

'Write data to the records

slSel "Select Node from Habitat WHERE Node is Unique"     '< something like that



#7
Thanks Fred.  I took your suggestions.  The first snippet used to take 0.75 seconds to parse out 300 records from 1.69 million; now it takes less than a measurable amount of time.  The second snippet takes about 0.68 to 0.70 seconds to divide the data into chart series.  A gain of about 6% to 10%.  Oh, and I did discover the BETWEEN parameter from a previous post.


      b = "HydSet,Percentile,PV" & LTrim$(Str$(CurSpecNum))
      A = "Select " & b & " from Exceedence Where "
      A = A & "NodeNum =" & Str$(CurNodeNum)
      A = A & " AND WYType =" & Str$(CurWYType)
      A = A & " AND MonthNum =" & Str$(CurMonthNum)
      A = A & " AND Percentile BETWEEN" & Str$(ExMin) & " AND" & Str$(ExMax)
      A = A & " AND (HydSet < 3"
      A = A & " OR HydSet = 3 AND AltFlow =" & Str$(AFLOW) & ")"
      slSel A



Do While SlGetRow
    Perc = Val(Slfn("Percentile"))*100
    PValue = Val(slfn(PVstr))
    H = Val(slfn("HydSet"))
    AUC(H) = AUC(H) + PValue
    Select Case H
          Case 1
    Incr XP1
    Series1Y(XP1) = PValue
    Series1X(XP1) = Perc
    Case 2
    Incr XP2
    Series2Y(XP2) = PValue
    Series2X(XP2) = Perc
    Case 3
    Incr XP3
    Series3Y(XP3) = PValue
    Series3X(XP3) = Perc
    End Select
    MinY = Min(MinY,PValue)
    MaxY = Max(MaxY, PValue)
  Loop

#8
First of all I got to say that SQLitening rocks!  It took me some time to wrap my brain around the SQL calls.

I've created a charting tool to display results for a fisheries habitat model.  One of my tables in the database is over 1.6 million records.  It takes about 0.75 seconds to retrieve 300 records into an array.  I then have to sort them into new arrays for the charting portion. 

Have I created an efficient method to retrieve the records?  Is it faster to use the SlGetRow command instead of loading the results into an array?  Thank you for any help.

Jeff


    Global EDQry() As String

      ReDim EDQry(0)
      A = "Select * from Exceedence Where "
      A = A & "NodeNum =" & Str$(CurNodeNum)
      A = A & " AND WYType =" & Str$(CurWYType)
      A = A & " AND MonthNum =" & Str$(CurMonthNum)
      A = A & " AND Percentile >=" & Str$(ExMin)
      A = A & " AND Percentile <=" & Str$(ExMax)
      A = A & " AND (HydSet < 3"
      A = A & " OR HydSet = 3 AND AltFlow =" & Str$(AFLOW) & ")"
      slSelAry A, EDQry(), "E2"


#9
Thanks Fred.  Most of the fields in my database will be a rather short length.
#10
Ok, I found ZLib.dll and pasted it into the app folder.  That worked.  Now another issue has come up.

After getting the compression to work, the database file size is much larger.  Original = 332,800 bytes, Compressed = 833,536 bytes.  What?  Confused!?

Jeff
#11
I'm embarking on a new application.  I used Tsunami on my last project.  I was frustrated with SQLitening at first glance, but I pushed through and I'm finding it to be pretty easy.  Had to get my mind wrapped around a few new ideas.

I got a new database created with several tables, so I thought I would add some compression in the mix.  When I try to compress some values into a table, I get the following error: Can Not Load "SQLiteningAuxRuts.Dll".

I have SQLiteningAuxRuts.Dll and SQLitening.Dll in the same folder as the app.  I'm using Powerbasic. 

Any suggestions?