[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

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

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.

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

Quote:

---

Originally Posted by asymetrix

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

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

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

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,

Quote:

---

Originally Posted by yogiyang

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

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

Attachment 111407

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

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

Quote:

---

Originally Posted by yogiyang

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

Quote:

---

Originally Posted by Schmidt

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

Quote:

---

Originally Posted by yogiyang

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

Quote:

---

Originally Posted by yogiyang

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

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

Quote:

---

Originally Posted by yogiyang

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

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

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

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

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