[RESOLVED] VbRichClient5 and SQLLite datatype-VBForums
Results 1 to 3 of 3

Thread: [RESOLVED] VbRichClient5 and SQLLite datatype

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2008
    Posts
    7

    Resolved [RESOLVED] VbRichClient5 and SQLLite datatype

    Good morning.
    I am learn to use vbRichClient with SQLite. I am newbie with both.
    I have this tiny table (found in a forum as example):

    Code:
    CREATE TABLE MyTable( ID INTEGER PRIMARY KEY, Name TEXT, Other STUFF, Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP )
    I have wrote this small routine for identify the data type of fields using vbRichClient5:
    Code:
    Private Sub MyRoutine()
        Dim cCn       As cConnection
        Dim cRs       As cRecordset
        Dim cFld      As cField
        Set cCn = New_c.Connection(App.Path & "\mySmallDb.db")
        Set cRs = cCn.OpenRecordset("Select * From myTable")
        For Each cFld In cRs.Fields
            Select Case cFld.ColumnType
                Case vbRichClient5.FieldType.SQLite_BLOB
                    Debug.Print cFld.Name & " " & "SQLite_BLOB"
                Case vbRichClient5.FieldType.SQLite_DOUBLE
                    Debug.Print cFld.Name & " " & "SQLite_DOUBLE"
                Case vbRichClient5.FieldType.SQLite_INTEGER
                    Debug.Print cFld.Name & " " & "SQLite_INTEGER"
                Case vbRichClient5.FieldType.SQLite_NULL
                    Debug.Print cFld.Name & " " & "SQLite_NULL"
                Case vbRichClient5.FieldType.SQLite_TEXT
                    Debug.Print cFld.Name & " " & "SQLite_TEXT"
                Case vbRichClient5.FieldType.VB_Boolean_AutoConverted
                    Debug.Print cFld.Name & " " & "VB_Boolean_AutoConverted"
                Case vbRichClient5.FieldType.VB_ShortDate_AutoConverted
                    Debug.Print cFld.Name & " " & "VB_ShortDate_AutoConverted"
                Case vbRichClient5.FieldType.VB_Time_AutoConverted
                    Debug.Print cFld.Name & " " & "VB_Time_AutoConverted"
                Case Else
                    Debug.Print cFld.Name & " " & cFld.ColumnType
            End Select
        Next
    
    End Sub
    Happens that the timestamp field is not identified as "VB_Time_AutoConverted".

    Also.

    What is STUFF type?

    Thanks in advance.

  2. #2
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,358

    Re: VbRichClient5 and SQLLite datatype

    Quote Originally Posted by VBDevelopper View Post
    I am learn to use vbRichClient with SQLite. I am newbie with both.
    No problem - but probably better, when you search for SQLite-examples directly here in the Forum.
    (there's a lot of them to find in the meantime, especially when you use the [Google Custom Search] field in the TopCenter of this WebPage).

    Quote Originally Posted by VBDevelopper View Post
    I have this tiny table (found in a forum as example):
    Code:
    CREATE TABLE MyTable( ID INTEGER PRIMARY KEY, Name TEXT, Other STUFF, Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP )
    Yep - found this too (on Stack-Overflow) - but as said, better to use the search here, to find VB6-examples.
    And LOL (to answer your question what "Other STUFF" means)...
    The OP on Stack-Overflow placed this in the Create Table-Statement, to signify "other column-defs" he had cut out (for the sake of simplicity of his posting).

    Quote Originally Posted by VBDevelopper View Post
    I have wrote this small routine for identify the data type of fields using vbRichClient5:
    Happens that the timestamp field is not identified as "VB_Time_AutoConverted".
    An auto-generated "record-timestamp" (defined over the default-value of the Column) should not only contain the time IMO (as e.g. 23:15:00 ) -
    so the definition DATETIME ensures (correctly IMO) a "full Date- and Time-Value" when a new Record is inserted (automatically).

    To reflect that in your little routine, you should have included a Case for:
    - VB_DATE_AutoConverted
    (which ensures and converts such a DB-FieldType-Value into a "full Date- and Time"-VB6-Date-Type)

    If you want only the "pure Date" (without the Time), then define the Field as: "... , MyPlainDateField SHORTDATE, ..."
    If you want only the Time (without the Date-Part), then define the Field as: "... , MyTimeOnlyField TIME, ..."

    A tip for your further experiments...

    In my experience you learn faster (and create your DBs faster, after some time), when you will not
    use an external "SQLite-DBManager-App", to define the Tables in your DB.

    The usage of the Create Table Statement is not that difficult to learn (especially since SQLite does not
    have dozens of different FieldTypes - it will all boil down to the types in your Select Case below:

    Integers (from Byte up to 64 Bit signed) are defined per: FieldName INTEGER (or just INT)
    Real-Numbers (always in Double-precision) are defined per: FieldName DOUBLE (or also REAL)
    Blob-content (to put your uninterpreted ByteArrays in) are defined per: FieldName BLOB
    Text-content (is always "variable length" and "unicode-aware") - defined per: FieldName TEXT
    Bool-Values (are mapped to an Integer-Type with 0 and 1) and are defined per: FieldName BIT (or BOOLEAN)

    And what remains then, are the 3 different Date- and Time-FieldTypes:
    - DATETIME (or just DATE) for full-date+time values
    - SHORTDATE for just the Date-part of a Date
    - TIME for just the Time-Part of a Date
    All 3 Date-Types are auto-mapped to VB6-Date-Variables (in case of a ShortDate only containing the Integer-part, in case of a Time only the Fractional-Part of a VB-Date (which under the covers is just a Double-Type).

    What's also useful to learn for your future SQLite-experiments (especially when you want to experiment with different Table-Creation-Strings)
    is, to use an InMemory-DB (which behaves exactly the same as a FileDB - only faster - and you can start fresh on your next IDE-Run (with potentially changed Table-Defs).

    Here your Demo-Code (I've adjusted your Object-Variables to prefixes of 'o', because 'c'-Prefixes usually mean Class(Types) and not ClassInstances (aka 'Objects').

    Code:
    Option Explicit
    
    Private oCn As cConnection
     
    Private Sub Form_Load()
      Set oCn = New_c.Connection(, DBCreateInMemory)
          oCn.Execute "CREATE TABLE MyTable(ID INTEGER PRIMARY KEY, Name TEXT, Timestamp DATETIME current_timestamp)"
      PrintRsFieldTypes oCn.OpenRecordset("Select * From myTable")
    End Sub
     
    Private Sub PrintRsFieldTypes(oRs As cRecordset)
      Dim oFld As cField
      For Each oFld In oRs.Fields
        Select Case oFld.ColumnType
          Case FieldType.SQLite_BLOB:                Debug.Print oFld.Name & " " & "SQLite_BLOB"
          Case FieldType.SQLite_DOUBLE:              Debug.Print oFld.Name & " " & "SQLite_DOUBLE"
          Case FieldType.SQLite_INTEGER:             Debug.Print oFld.Name & " " & "SQLite_INTEGER"
          Case FieldType.SQLite_NULL:                Debug.Print oFld.Name & " " & "SQLite_NULL"
          Case FieldType.SQLite_TEXT:                Debug.Print oFld.Name & " " & "SQLite_TEXT"
          Case FieldType.VB_Boolean_AutoConverted:   Debug.Print oFld.Name & " " & "VB_Boolean_AutoConverted"
          Case FieldType.VB_DATE_AutoConverted:      Debug.Print oFld.Name & " " & "VB_DATE_AutoConverted"
          Case FieldType.VB_ShortDate_AutoConverted: Debug.Print oFld.Name & " " & "VB_ShortDate_AutoConverted"
          Case FieldType.VB_Time_AutoConverted:      Debug.Print oFld.Name & " " & "VB_Time_AutoConverted"
          Case Else:                                 Debug.Print oFld.Name & " " & oFld.ColumnType
        End Select
      Next
    End Sub
    The above prints out:
    Code:
    ID SQLite_INTEGER
    Name SQLite_TEXT
    Timestamp VB_DATE_AutoConverted
    HTH

    Olaf
    Last edited by Schmidt; Jan 10th, 2018 at 03:19 PM.

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2008
    Posts
    7

    Re: VbRichClient5 and SQLLite datatype

    Hi Olaf.
    No problem - but probably better, when you search for SQLite-examples directly here in the Forum.
    (there's a lot of them to find in the meantime, especially when you use the [Google Custom Search] field in the TopCenter of this WebPage).
    You have been very clear. You would be a good teacher. I will follow your suggestions.
    I will continue to learn the use of vbrichclient and SqlLite. If I have other issues, can you tell me if it is appropriate to continue this post or close it?
    And LOL (to answer your question what "Other STUFF" means)...
    The OP on Stack-Overflow placed this in the Create Table-Statement, to signify "other column-defs" he had cut out (for the sake of simplicity of his posting).
    I laughed a lot for not understanding the example of the post on StackOverFlow. I have been a very dummy. In my country one would say: very chicken.
    TVM for the suggestions.

    R.

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.