[VBRichClient] How to import old Data in new Database?-VBForums
Results 1 to 18 of 18

Thread: [VBRichClient] How to import old Data in new Database?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    [VBRichClient] How to import old Data in new Database?

    I have already programmed this part but it is taking very long time. Approximately 3 hours to import and process 1.3 million records. There are 26 fields and from these 7 are Blob fields.

    Ok let me try and in greater details.

    I have developed a software which contains a lot of data in it.

    After two years I was asked to upgrade the software. In the upgrade process I have added many new tables and fields in existing Tables as requested.

    The client has started using this software. Now the client wants to import old data in the new software.

    I have programmed this part but it is taking very long because we have to verify all the records that are existing in the new version and update all the data that is already existing, insert all the data that is not existing, etc. This checking existing of record and verifying each field and updating those fields as needed, etc. is taking a lot of time.

    Can someone please provide tips that will help in speeding up the import of old data.

    TIA

    Yogi Yang

  2. #2
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,846

    Re: [VBRichClient] How to import old Data in new Database?

    If you only have to import it once and it takes 3 hours, then why spend time to optimize this?

  3. #3
    Lively Member
    Join Date
    May 2009
    Location
    UK
    Posts
    68

    Re: [VBRichClient] How to import old Data in new Database?

    if you can show some data of new data and old data we could look at it. using a seperator would be best like csv or use | scan the data into memory and processing duplicates etc should take seconds to process.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    The that is imported comes from SQLite's. I am using VBRichClient in my project.

    The project automatically compresses the database and backs it up when exited.

    The program in question has been sold to many users. Approx 120 users.

    So I want to build a upgrade/update utility that will take allow the user to select old database and import it into the current database.

    TIA

    Yogi Yang

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by asymetrix View Post
    if you can show some data of new data and old data we could look at it. using a seperator would be best like csv or use | scan the data into memory and processing duplicates etc should take seconds to process.
    It want to be possible to provide as it is in local language (ANSI) and contains a lot of RTF text/Images in Blog fields.

    Here is a sample row of one of the table:
    
    Code:
    wbf_id	wbf_num	wbf_date	wbf_event	wbf_persons	wbf_name	wbf_address	wbf_phones	wbf_related_person	wbf_booked_for_occasion	wbf_bride_mother	wbf_bride_father	wbf_bride_name	wbf_bride_dob	wbf_bride_age	wbf_bride_off_address	wbf_bride_off_phone	wbf_bride_resi_address	wbf_bride_resi_phone	wbf_groom_mother	wbf_groom_father	wbf_groom_name	wbf_groom_dob	wbf_groom_age	wbf_groom_off_address	wbf_groom_off_phone	wbf_groom_resi_address	wbf_groom_resi_phone	wbf_cancelled	wbf_remarks	wbf_proof_doc	wbf_updated
    1	413	2013-03-06	›†	350	*۽† ›”۽† **	"""  ""  • ,  ۦ, * , › ."	9898321804	* . **	False																			False			False
    Exporting to CSV is a problem as it would not be possible to export images and RTF text properly.

    TIA

    Yogi Yang

  6. #6
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,846

    Re: [VBRichClient] How to import old Data in new Database?

    The most simple things I can think off:
    - Use a memory db for importing the data
    - Turn off the indices while importing the data
    - Create the indices
    - Write memory db to disk

  7. #7
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: [VBRichClient] How to import old Data in new Database?

    Arnoutdv's last advise is a good one (if the DBs which need to be importet, do not bloat-up the Process-memory too much (are below 500-800MB).

    As for writing out the (temporary) InMemory-Import-DB "pagewise in one go", there's the cConnection.CopyDataBase-method (under the hood addressing Sqlites Backup-API)... usage then (after Import into the InMemDB):

    Code:
    InMemDBCnn.CopyDataBase YourPathToTheFileBased_SQLite.db
    If the Inmemory-approach cannot be applied (when the Source-DBs are too large), then you should take care, that you use Command-Objects for the Insert-Loops into the new DB - and that you wrap those copy-over-loops within a Transaction, applied on the Destination-Cnn.

    If you haven't done so already, this should speed the whole thing up by factor 20-50 or so...

    An example, how to perform fast "bulk-inserts" per Command-Objects (achieving about a quarter Mio newly inserted records per second into an 8-Column-Table) is contained in the "official Nwind-Demo", which I recently posted a Link to here:
    http://www.vbforums.com/showthread.p...=1#post4602093


    HTH

    Olaf

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    Olaf,

    Thanks for your advise and inights.

    I am using Transaction while updating database.

    But I have observed that searching the db for existing data and then updating it if found or inserting it if not found seems to take a lot of time.

    Regards,

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by yogiyang View Post
    Olaf,
    I have observed that searching the db for existing data and then updating it if found or inserting it if not found seems to take a lot of time.
    Not sure, how you currently go about that special topic...
    (which is not uncommon, and called an "Upsert" in common "SQL-speach").

    The SQLite-engine also offers a special command for that: "Insert Or Replace ....".

    But as always there's a lot of ways to skin that special cat in concrete scenarios.
    What I found performancewise paying off at such occasions is:

    - to either dump everything into the destination-table "as it comes" - and cleanup Duplicates later
    (in one go, with a single statement)

    - or (with much more fine-control), to store the Keys or IDs you need for later Exists-Checks in
    one or more cSortedDictionaries, which allow much faster Lookups, compared with e.g.
    Selecting single Records from the DB with an appropriate Where-Clause to perform the Exists-check...

    Easiest way (I think we could find factor 20-100) would be, when you post a small
    extract of your real data (maybe overwrite some non-publishable columns with dummy-data) -
    and the code you're currently using for the copy-over (including your current duplicate-checks
    for the Upserts).

    Your current timings are surely way too high for that kind of stuff... am sure the RichClient-lib
    can do much better especially with some help from the fast Dictionary or Collection-Classes...


    Olaf

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    Hello,

    I am using following code to import data from old database to new database

    Code:
    Private Sub ImportTableDataAll(oldDBConn As cConnection, TableName As String, FieldName As String)
    
      Dim OldRS As cRecordset
      Dim NewRS As cRecordset
    
      Dim i As Long
      
      Dim DummyStr As String
      Dim SQL As String
        
      If Len(Trim$(SQL)) = 0 Then Exit Sub
        
      'First Delete All Records in Table
      Conn.Execute "Delete From " & TableName
      
      Set OldRS = oldDBConn.OpenRecordset(SQL, True)
      Set NewRS = Conn.OpenRecordset(SQL, False)
      
      If OldRS.RecordCount > 0 Then
        
        OldRS.MoveFirst
        Do
          'Read agree_name and then check if it is duplicate
          DummyStr = OldRS.Fields(FieldName).Value
          
          If Len(Trim$(DummyStr)) > 0 Then
            'Add new Record
            NewRS.AddNew
            
            For i = 0 To OldRS.Fields.Count - 1
              DummyStr = OldRS.Fields(i).Name
              If NewRS.Fields.Exists(DummyStr) Then
                NewRS.Fields(DummyStr).Value = OldRS.Fields(DummyStr).Value
              End If
            Next i    'For i = 0 To OldRS.Fields.Count - 1
                    
          End If    'If Len(Trim$(DummyStr)) > 0 Then
          OldRS.MoveNext
        Loop While Not OldRS.EOF
        NewRS.UpdateBatch
      End If    'If OldRS.RecordCount > 0 Then
    End Sub
    But here I am facing a new problem. Here all fields whose data type I have set to Currency are not saved.

    Here is one simple and small table's structure:
    Code:
    CREATE TABLE [function_booking] (
      [fb_id] INTEGER PRIMARY KEY AUTOINCREMENT, 
      [fb_order_num] VARCHAR(20), 
      [fb_order_date] DATE, 
      [fb_cust_id] INTEGER, 
      [fb_order_booked_by] VARCHAR(50), 
      [fb_function_name] VARCHAR(50), 
      [fb_function_days] VARCHAR(3), 
      [fb_delivery_date] DATE, 
      [fb_delivered] BOOLEAN, 
      [fb_actual_delivery_date] DATE, 
      [fb_charges] CURRENCY, 
      [fb_charges_in_words] TEXT, 
      [fb_is_inquiry] BOOLEAN DEFAULT 1, 
      [fb_remarks] TEXT, 
      [fb_advance_received] CURRENCY);
    So what is wrong with my code?

    This code is working just fine except for speed and it does not save the valued of all the fields that have field type set to Currency!

    I am attaching sample databases that are in questions.

    As the original database is very large with many tables the attached database contains only a sub set of actual database.


    TIA

    Yogi Yang

    SMData.zip
    Last edited by yogiyang; Mar 5th, 2014 at 05:06 AM.

  11. #11
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,846

    Re: [VBRichClient] How to import old Data in new Database?

    There is a limited set of datatypes in SQLite, check paragraph 2.2 Affinity Name Examples:
    http://www.sqlite.org/datatype3.html

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    I know that but basically SQLite supports Dynamic type or let us say it is type less so we can use Currency as fields data type.

    We have been using this data type for quite some time now without any problem.

    This is for the first time that we have run into this problem.

    Regards,

    Yogi Yang

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by yogiyang View Post
    Hello,

    I am using following code to import data from old database to new database

    Code:
    Private Sub ImportTableDataAll(oldDBConn As cConnection, TableName As String, FieldName As String)
    
      Dim OldRS As cRecordset
      Dim NewRS As cRecordset
    
      Dim i As Long
      
      Dim DummyStr As String
      Dim SQL As String
        
      If Len(Trim$(SQL)) = 0 Then Exit Sub
        
      'First Delete All Records in Table
      Conn.Execute "Delete From " & TableName
      
      Set OldRS = oldDBConn.OpenRecordset(SQL, True)
      Set NewRS = Conn.OpenRecordset(SQL, False)
      
      If OldRS.RecordCount > 0 Then
        
        OldRS.MoveFirst
        Do
          'Read agree_name and then check if it is duplicate
          DummyStr = OldRS.Fields(FieldName).Value
          
          If Len(Trim$(DummyStr)) > 0 Then
            'Add new Record
            NewRS.AddNew
            
            For i = 0 To OldRS.Fields.Count - 1
              DummyStr = OldRS.Fields(i).Name
              If NewRS.Fields.Exists(DummyStr) Then
                NewRS.Fields(DummyStr).Value = OldRS.Fields(DummyStr).Value
              End If
            Next i    'For i = 0 To OldRS.Fields.Count - 1
                    
          End If    'If Len(Trim$(DummyStr)) > 0 Then
          OldRS.MoveNext
        Loop While Not OldRS.EOF
        NewRS.UpdateBatch
      End If    'If OldRS.RecordCount > 0 Then
    End Sub
    This code is working just fine except for speed
    In above code, you are constantly stressing the internal Fields-Collection with
    Name-Resolution and Exists-Checks ... without the consideration, that this
    check delivers the very same results on each and every Record-Shift.

    So - this job (determining "the intersection of Field-Names" in old and new table)
    needs to be moved out of the Row-based record-loop.

    But if you have that (a List of Fields, which surely exists in both tables) - then
    another thought instantly comes to mind: why not use this nice FieldList in an
    Insert-Into-Statement - and thus avoiding all those COM-MethodCalls, which
    can Sum Up quite fast.

    Below is code, which separates the FieldList-Intersection-Retrieval from the
    Main-Import-Function - and also introduces a Transaction-Wrapping for more speed.

    Please let me know, how that payed off performance-wise on your large tables.

    Into an empty Form - and into the App.Path of the TestProject the two DBs.

    Code:
    Option Explicit
    
    Private Conn As cConnection
    
    Private Sub Form_Load()
      Set Conn = New_c.Connection(App.Path & "\SMData_NEW.db3")
      
      'we don't need to open two connections - instead we attach the old DB
      Conn.DataBases.AttachDataBase App.Path & "\SMData_OLD.db3", "Old"
          
      ImportTableDataAll "function_booking", "fb_order_booked_by"
    End Sub
     
    'requires the Old-DB attached in Conn under the name 'Old'
    Private Sub ImportTableDataAll(TableName As String, FieldName As String)
    On Error GoTo RollBack
    
       Conn.BeginTrans
          Dim FldLst$: FldLst = Join(GetFieldsExistingInBothTbls(TableName), ",")
    
          Conn.Execute "Delete From Main.[" & TableName & "]" 'First Delete All Records in Destination-Table (DB 'Main')
          Conn.Execute "Insert Into Main.[" & TableName & "] (" & FldLst & ") " & _
                       "Select " & FldLst & " From Old.[" & TableName & "] Where Length(Trim(" & FieldName & ")) > 0"
                     
       Conn.CommitTrans
    Exit Sub
    
    RollBack:
       MsgBox Err.Description
       Conn.RollbackTrans
    End Sub
    
    'requires the Old-DB attached in Conn under the name 'Old'
    Private Function GetFieldsExistingInBothTbls(TableName As String) As String()
    Dim NewRS As cRecordset, OldCol As cColumn, FldArr As cArrayList
    
      Set NewRS = Conn.OpenRecordset("Select * From Main.[" & TableName & "] Where 1=0")
      
      Set FldArr = New_c.ArrayList(vbString) 'create a new, still empty string-array-list
      
      For Each OldCol In Conn.DataBases("Old").Tables(TableName).Columns
         If NewRS.Fields.Exists(OldCol.Name) Then FldArr.Add OldCol.NameInBrackets
      Next
      
      FldArr.CopyToArray GetFieldsExistingInBothTbls 'return content as an array of String()
    End Function
    Olaf

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by Schmidt View Post
    In above code, you are constantly stressing the internal Fields-Collection with
    Name-Resolution and Exists-Checks ... without the consideration, that this
    check delivers the very same results on each and every Record-Shift.

    So - this job (determining "the intersection of Field-Names" in old and new table)
    needs to be moved out of the Row-based record-loop.

    But if you have that (a List of Fields, which surely exists in both tables) - then
    another thought instantly comes to mind: why not use this nice FieldList in an
    Insert-Into-Statement - and thus avoiding all those COM-MethodCalls, which
    can Sum Up quite fast.

    Below is code, which separates the FieldList-Intersection-Retrieval from the
    Main-Import-Function - and also introduces a Transaction-Wrapping for more speed.

    Please let me know, how that payed off performance-wise on your large tables.

    Into an empty Form - and into the App.Path of the TestProject the two DBs.

    Code:
    Option Explicit
    
    Private Conn As cConnection
    
    Private Sub Form_Load()
      Set Conn = New_c.Connection(App.Path & "\SMData_NEW.db3")
      
      'we don't need to open two connections - instead we attach the old DB
      Conn.DataBases.AttachDataBase App.Path & "\SMData_OLD.db3", "Old"
          
      ImportTableDataAll "function_booking", "fb_order_booked_by"
    End Sub
     
    'requires the Old-DB attached in Conn under the name 'Old'
    Private Sub ImportTableDataAll(TableName As String, FieldName As String)
    On Error GoTo RollBack
    
       Conn.BeginTrans
          Dim FldLst$: FldLst = Join(GetFieldsExistingInBothTbls(TableName), ",")
    
          Conn.Execute "Delete From Main.[" & TableName & "]" 'First Delete All Records in Destination-Table (DB 'Main')
          Conn.Execute "Insert Into Main.[" & TableName & "] (" & FldLst & ") " & _
                       "Select " & FldLst & " From Old.[" & TableName & "] Where Length(Trim(" & FieldName & ")) > 0"
                     
       Conn.CommitTrans
    Exit Sub
    
    RollBack:
       MsgBox Err.Description
       Conn.RollbackTrans
    End Sub
    
    'requires the Old-DB attached in Conn under the name 'Old'
    Private Function GetFieldsExistingInBothTbls(TableName As String) As String()
    Dim NewRS As cRecordset, OldCol As cColumn, FldArr As cArrayList
    
      Set NewRS = Conn.OpenRecordset("Select * From Main.[" & TableName & "] Where 1=0")
      
      Set FldArr = New_c.ArrayList(vbString) 'create a new, still empty string-array-list
      
      For Each OldCol In Conn.DataBases("Old").Tables(TableName).Columns
         If NewRS.Fields.Exists(OldCol.Name) Then FldArr.Add OldCol.NameInBrackets
      Next
      
      FldArr.CopyToArray GetFieldsExistingInBothTbls 'return content as an array of String()
    End Function
    Olaf
    @Olaf,

    Thanks it did the trick.

    My primary testing show almost 40% improvement in speed.

    Once again thanks for helping in solving this problem.

    Regards,

    Yogi yang

  15. #15
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by yogiyang View Post
    Thanks it did the trick.

    My primary testing show almost 40% improvement in speed.
    Oh - would have expected (much) more increase in speed.

    Do we talk about DBs, sitting both (the Source and the Target-DB) on a Network-Share?

    That could be a reason in this case.

    But maybe your DBs (if they are both on a local drive) are really large (in the 300MB+ range)?
    Then you could improve performance, when you change the Cache-Size to a higher value
    (the current default for Wrapper-created DBs is 64MB max-cache-size).

    Demo, which explains a few things, and shows how you can increase the cache for such
    an "import-session on large data".

    Code:
    Option Explicit
    
    Private Sub Form_Load()
    Dim FileName As String: Environ ("Temp") & "\testnew.db3"
    
    On Error Resume Next: Kill FileName: On Error GoTo 0
    
    Dim Cnn As New cConnection
        Cnn.CreateNewDB FileName
     
    Dim CurPageSize As Long, CurCacheSizeInPages As Long, CurCacheSizeInBytes As Long
        CurPageSize = Cnn.OpenRecordset("pragma page_size")(0)
        CurCacheSizeInPages = Cnn.OpenRecordset("pragma cache_size")(0)
     
        CurCacheSizeInBytes = CurPageSize * CurCacheSizeInPages
        Debug.Print "Current CacheSize in MByte: "; CurCacheSizeInBytes \ 1024 \ 1024
    
    'so, since 8KB is the default page-size my wrapper applies to new created DBs
    '(this page_size cannot be changed anymore per pragma, as soon as a DB was written into)
    
    'the default-size of 8192 pages as the defined default-cache-size will offer a compromise
    'of 64MByte (a good compromise these days) maximum cache-size for the DB within the SQLite-engine
    
    
        'Though we can increase the SQLite-Cache to e.g. 10 times that (to 640MB)
        'by changing the page-count per: 'pragma cache_size = NewValue'
        Cnn.Execute "pragma cache_size=" & CurCacheSizeInPages * 10
        
        'let's check again, if we were successful
        'note, that the new cache_size reverts to its default again, when you re-open the DB...
        'though it will be kept as long as you have the Cnn open as an Object (not yet set to Nothing)
        CurPageSize = Cnn.OpenRecordset("pragma page_size")(0)
        CurCacheSizeInPages = Cnn.OpenRecordset("pragma cache_size")(0)
     
        CurCacheSizeInBytes = CurPageSize * CurCacheSizeInPages
        Debug.Print "New CacheSize for this session in MByte: "; CurCacheSizeInBytes \ 1024 \ 1024
    End Sub
    Another thing which could be improved perhaps is, when you find a faster way or alternative
    for your current "Extra-Field-check" in the Where-Clause:
    "...Where Length(Trim(" & FieldName & ")) > 0"

    Maybe not each and every table needs that - or a (then faster) check for Column Is Null
    is sufficient too.

    And the last thing which comes to mind with regards to performance:
    Try to create the Indexes if you have any (which I assume), after all the table-imports
    went through - even a temporary delete of those indexes - then import - then re-creation
    per priorily "safed Index-Create-Statement" (enumerable per Cnn.Databases.Tables.Indexes)
    could be worhtwhile IMO.

    Olaf

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by yogiyang View Post
    I know that but basically SQLite supports Dynamic type or let us say it is type less so we can use Currency as fields data type.
    We have been using this data type for quite some time now without any problem.
    This is for the first time that we have run into this problem.
    Maybe a fe additional words to the above...

    SQLite distinguishes only 4 different types:
    - Text (UnicodeCapable - in a variable length up to 2GB )
    - Double (the Standard-Type with 8 Bytes)
    - Integer (accepting anything from 8Bit to 64Bit dynamically)
    - Blob (for Binary-Data up to 2GB)

    And although the Standard-SQLite-lib allows mixing of those 4 recognized types in a
    single Column, my wrapper tries to enforce a strong(er) Type-Affinity - and with
    a few tricks accomplishes even ShortDate and DateTime as well as Boolean-Field-support
    (all ensured over the cRecordset and the cCommand - if you use textbased executes,
    then you are again free to put anything - also mixed types into your columns).

    For unrecognized TypeStrings in a FieldDef (as e.g. Currency) the wrapper tries an automatism,
    to recognize and ensure a fixed affinity also for these so far unknown (untyped) Columns,
    as soon as it finds the first value in them... that usually works well enough, so that I'm
    not surprised that you had no problems with your (unrecognized) Currency-Column-Def so far.

    The SQL-String in my changed import-function (the "Insert Into...") is bypassing
    the wrappers recordsets (which are the "strong Typing-Enforcers") and therefore
    works also with your Currency-Type.

    You could either re-define all those Currency-Fields to 'Integer' or 'Double' Field-Definitions -
    or suggest, to which of the 4 Base-Types I should map the Column, as soon as I recognize
    the 'Currency' FieldType-String.

    I have already existing mappings (e.g. FieldDef-TypeStrings 'Float', 'Real', 'Double' are all
    mapped to SQLites Double - the longest list is with String-Content:
    Text, String, VarChar, NVarChar, CLob, Memory are all mapped to SQLites Text-Type.

    As said - for Currency I have no such mapping (yet) - but I'm not yet sure if such a
    thing is required or useful, depending a bit, why the Table-Creator choose this Field-Type
    in his special case.

    In "VB-Think" it is sometimes choosen, just to come close to the integer-value-range,
    a true 64Bit-Type would offer - though that's not necessary with SQLite, because the
    Integer-Type can take up and deal with signed 64Bit-Values natively (that's supported
    also in the cRecordset and cCommand, to feed 64Bit-Values encosed in a CDEC-Variant).

    But mostly it is choosen because of its 4Digit fixed-point behaviour - and due to
    this fractional part, the Double-Type would be the best candidate for a (potential) mapping
    here on my end.

    As said, I have a few qualms about, if that is such a good idea... don't want to break anything
    in scenarios for other users, who maybe used this unrecognized FieldDef-TypeString
    for other purposes in their App... and would be surprised, when it suddenly enforces
    "a Double-Value on it".


    Olaf

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: [VBRichClient] How to import old Data in new Database?

    I cannot advise you as to how to map currency type. You are the best judge because you seem to know the ins and outs of SQLite more than me.

    Here we use SQLite manager called SQLite Expert and this manage seems to allow us a very long list of data types for fields/columns.

    So the default VB mentality suggest that we should always store all values that are decimal values in Currency format for accuracy. So our developers will always use this data type while designing SQLite DB.

    One of our developers have also observed that when we use insert and update query if we can put single quotes around all values and all of the are getting saved properly! I don't know why but it just works!

    In fact we have built a small framework around your implementation of SQLite and have been using it on all almost all our recent projects.

    Regards,

    Yogi Yang

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: [VBRichClient] How to import old Data in new Database?

    Quote Originally Posted by yogiyang View Post
    I cannot advise you as to how to map currency type. You are the best judge because you seem to know the ins and outs of SQLite more than me.
    Thinking about it, although a relatively easy thiing to do - I would like to continue to not map this fixed-comma Integer-Type to e.g. a Double.

    I just cannot truly hold that "promise" (Currency is mapped and "handled") - especially not for calculations and number-comparisons which work at the level of SQLites SQL-ParserEngine "Lemon" (e.g. in your own Where - Clauses) that the whole thing (the fixed-comma-based "integer-alignment" and rounding) would behave like you are wont to with VBs Currency-Datatype.

    One well-working way - easy to accomplish on *new* projects is, to put Currency-Values into the DB as Cents -
    and then assign and store them in an Integer-Field (large enough since working up to 64Bit) - then in Reporting-scenarios just format
    the Cents with the appropriate divider into your currency.

    The other way is to use the Double-Type - and then making sure, that e.g. when using aggregates like Sum(...) you feed rounded Values into it from your Double-Fields (wrapping them in a Round-Function on the Field or expression), so that e.g. tax-calculation-expressions within that aggreagate, don't sum up with wrong (too long and unrounded) fractional-parts.

    Quote Originally Posted by yogiyang View Post
    Here we use SQLite manager called SQLite Expert and this manage seems to allow us a very long list of data types for fields/columns.
    SQLite has no problem when you specify other Field-Type-Strings, which it doesn't recognize (for the Types it recognizes and natively ensures a mapping - an "affinity" for, look at the link Arnoutdv has already postet) - so, other TypeStrings than those listed on the SQLite-page are unrecognized and simply ignored and SQLite will treat this column then as a kind of "Variant"-Column with "no affinity" (which can affect performance). The TypeStrings are still kept in the Create Table-Statements, to allow the developer a lookup of those types, to give him "a hint", how to treat this type in their wrappers...

    And that's what I do in my automatic mappings for Date, DateTime, Time (all mapped to Text-affinity and autotranslated from ISO-SQL-DateStrings into VB-Date-Values at the Recordset-Level) - and the Boolean Type with its 8Bit-Integervalues of 0 or 1 on the SQLite-end, I map to a VB-Boolean then appropriately.

    Quote Originally Posted by yogiyang View Post
    So the default VB mentality suggest that we should always store all values that are decimal values in Currency format for accuracy. So our developers will always use this data type while designing SQLite DB.
    As said, this will cause only the effect of an unrecognized Type-String, ending up with "no-affinity" - in formulas and SQL-expressions then treated "as it comes", depending on what you put into it - if it has a fractional part, then it will be treated dynamically as a Double-Value - if it has no fractional part, it will be treated as an Integer in calculations at the SQL-Parser-level.

    Quote Originally Posted by yogiyang View Post
    One of our developers have also observed that when we use insert and update query if we can put single quotes around all values and all of the are getting saved properly! I don't know why but it just works!
    Ummhh - Ok - it will work - but not with a good performance, since with "single quote wrapping" you force a storage of any value as Text (even in Integer-Columns, which could store the Value of 123 in a single byte - whereas storing it as '123' would require a Len-Descriptor + 3 CharBytes in storage.
    Also much slower in queries, because Sqlites SQL-parser-engine will (have to) perform an autoconversion then for each single TextValue you've put into a Column - e.g. when you have an expression as "... Where MyIntCol > 5 ..."


    Quote Originally Posted by yogiyang View Post
    In fact we have built a small framework around your implementation of SQLite and have been using it on all almost all our recent projects.
    Glad to hear that - maybe with that framework it will be a bit easier (since the DB-interaction is more concentrated in appropriate Classes or Code-Modules) to change to a true "typed behaviour" and replace the occurences where you used Cnn.Execute "Insert Into..." or "Update ..." (using your own built-up Strings) to Command-Objects which can ensure proper Typing - or simply to Recordsets which automatically perform the needed Conversions within their Value-Property-Methods, so that anything you pass into an Rs("FieldName").Value will be correctly autotranslated (already at the wrappers VB-Level) to the correct Type for which an affinity exists in the Sqlite-engine.

    Olaf
    Last edited by Schmidt; Mar 11th, 2014 at 05:16 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.