Weird Array-related issue.

[Erlkoenig]

I'm not sure what's going on, but the following code conks out at the 4th iteration (of 12) and I can't figure out why.

Short version: I am trying to set up a table map ahead of creating a SQLite database. The data for this is pulled from the following declarations:

        ReDim TABLE_ACTUAL_NAME(COUNT_TABLE)
        ReDim TABLE_COLUMN_LIST(COUNT_TABLE)
        ReDim TABLE_COLUMN_TYPE(COUNT_TABLE)
        ReDim TABLE_CREATE_CODE(COUNT_TABLE)
        ReDim TABLE_DESCRIPTION(COUNT_TABLE)
        ReDim TABLE_DISPLAYNAME(COUNT_TABLE)
        ReDim TABLE_INDEXCOLUMN(COUNT_TABLE)

        ' ---[ Table 00 - DO NOT USE/COUNT! ]--------------------------------------------
        TABLE_ACTUAL_NAME(0) = flEncapsulate(MSG_MSC_INVALID, "[")
        TABLE_COLUMN_LIST(0) = MSG_MSC_INVALID
        TABLE_COLUMN_TYPE(0) = "NULL"
        TABLE_DESCRIPTION(0) = MSG_MSC_INVALID
        TABLE_DISPLAYNAME(0) = MSG_MSC_INVALID & " Table"
        TABLE_INDEXCOLUMN(0) = MSG_MSC_INVALID

        ' ---[ Table 01 - FILETYPE          ]--------------------------------------------
        TABLE_ACTUAL_NAME(1) = "FileType"
        TABLE_COLUMN_LIST(1) = "Extension|Description|MIME|Usage"
        TABLE_COLUMN_TYPE(1) = "VC4|VC40|VC80|VC1"
        TABLE_DESCRIPTION(1) = "Supported File Formats"
        TABLE_DISPLAYNAME(1) = "File Types"
        TABLE_INDEXCOLUMN(1) = "Extension"

        ' ---[ Table 02 - FORMAT            ]--------------------------------------------
        TABLE_ACTUAL_NAME(2) = "Format"
        TABLE_COLUMN_LIST(2) = "ID|Lock|Name|MediaClass|Digital|FileType"
        TABLE_COLUMN_TYPE(2) = "PK|BL0|VC25|IN|BL0|VC4"
        TABLE_DESCRIPTION(2) = "Media Formats"
        TABLE_DISPLAYNAME(2) = "Formats"
        TABLE_INDEXCOLUMN(2) = "ID"

        ' ---[ Table 03 - GENRE             ]--------------------------------------------
        TABLE_ACTUAL_NAME(3) = "Genre"
        TABLE_COLUMN_LIST(3) = "ID|Lock|Name|Audio|Digital|Image|Other|Print|Video"
        TABLE_COLUMN_TYPE(3) = "PK|BL0|VC30|BL0|BL0|BL0|BL0|BL0|BL0"
        TABLE_DESCRIPTION(3) = "Genres"
        TABLE_DISPLAYNAME(3) = "Genres"
        TABLE_INDEXCOLUMN(3) = "ID"

        ' ---[ Table 04 - HONORIFIC         ]--------------------------------------------
        TABLE_ACTUAL_NAME(4) = "Honorific"
        TABLE_COLUMN_LIST(4) = "ID|Lock|Title"
        TABLE_COLUMN_TYPE(4) = "PK|BL0|VC20"
        TABLE_DESCRIPTION(4) = "Titles and Honorifics"
        TABLE_DISPLAYNAME(4) = "Titles"
        TABLE_INDEXCOLUMN(4) = "Title"

        ' ---[ Table 05 - ISO-3166          ]--------------------------------------------
        TABLE_ACTUAL_NAME(5) = "ISO-3166"
        TABLE_COLUMN_LIST(5) = "ID|Lock|Name|Code"
        TABLE_COLUMN_TYPE(5) = "PK|BL0|VC50|VC2"
        TABLE_DESCRIPTION(5) = "Countries of the World (per ISO-3166)"
        TABLE_DISPLAYNAME(5) = "Countries"
        TABLE_INDEXCOLUMN(5) = "Code"

        ' ---[ Table 06 - MEDIA             ]--------------------------------------------
        TABLE_ACTUAL_NAME(6) = "Media"
        TABLE_COLUMN_LIST(6) = "ID|Lock|Name"
        TABLE_COLUMN_TYPE(6) = "PK|BL0|VC20"
        TABLE_DESCRIPTION(6) = "Source Media"
        TABLE_DISPLAYNAME(6) = "Media"
        TABLE_INDEXCOLUMN(6) = "ID"

        ' ---[ Table 07 - PERIOD            ]--------------------------------------------
        TABLE_ACTUAL_NAME(7) = "Period"
        TABLE_COLUMN_LIST(7) = "ID|Lock|Name|Start|End|Print|Music|Visual"
        TABLE_COLUMN_TYPE(7) = "PK|BL0|VC15|VC10|VC10|BL0|BL0|BL0"
        TABLE_DESCRIPTION(7) = "Creative Periods"
        TABLE_DISPLAYNAME(7) = "Periods"
        TABLE_INDEXCOLUMN(7) = "ID"

        ' ---[ Table 08 - SCHOLAR           ]--------------------------------------------
        TABLE_ACTUAL_NAME(8) = "Scholar"
        TABLE_COLUMN_LIST(8) = "ID|Lock|CatalogName|Scholar|Token"
        TABLE_COLUMN_TYPE(8) = "PK|BL0|VC60|VC80|VC15"
        TABLE_DESCRIPTION(8) = "Scholarly Catalogs"
        TABLE_DISPLAYNAME(8) = "Scholars"
        TABLE_INDEXCOLUMN(8) = "ID"

        ' ---[ Table 09 - SPARS             ]--------------------------------------------
        TABLE_ACTUAL_NAME(9) = "SPARS"
        TABLE_COLUMN_LIST(9) = "ID|Lock|Code"
        TABLE_COLUMN_TYPE(9) = "PK|BL0|VC3"
        TABLE_DESCRIPTION(9) = "SPARS Codes"
        TABLE_DISPLAYNAME(9) = "SPARS Codes"
        TABLE_INDEXCOLUMN(9) = "ID"

        ' ---[ Table 10 - TALENT            ]--------------------------------------------
        TABLE_ACTUAL_NAME(10) = "Talent"
        TABLE_COLUMN_LIST(10) = "ID|Honorific|GivenName|Surname|Suffix|Alias|ABorn|DOB|POB|ADeath|DOD|POD|Actor|Arranger|Author|Composer|ThematicCatalog|Conductor|Dancer|Director|Instrumentalist|Instrument|Narrator|Visual|Vocalist|VocalRegister|Other|WebSite"
        TABLE_COLUMN_TYPE(10) = "PK|VC20|VC50|VC50|VC10|VC50|BL0|VC10|VC40|BL0|VC10|VC40|BL0|BL0|BL0|BL0|IN|BL0|BL0|BL0|BL0|VC50|BL0|BL0|BL0|VC30|VC40|IN"
        TABLE_DESCRIPTION(10) = "Artistic Talents"
        TABLE_DISPLAYNAME(10) = "Artists"
        TABLE_INDEXCOLUMN(10) = "ID"

        ' ---[ Table 11 - VOICE             ]--------------------------------------------
        TABLE_ACTUAL_NAME(11) = "Voice"
        TABLE_COLUMN_LIST(11) = "ID|Lock|Name"
        TABLE_COLUMN_TYPE(11) = "PK|BL0|VC30"
        TABLE_DESCRIPTION(11) = "Vocal ranges and registers"
        TABLE_DISPLAYNAME(11) = "Vocal Registers"
        TABLE_INDEXCOLUMN(11) = "ID"

        ' ---[ Table 12 - WEBSITE           ]--------------------------------------------
        TABLE_ACTUAL_NAME(12) = "Website"
        TABLE_COLUMN_LIST(12) = "ID|Lock|Name|URL|Secure"
        TABLE_COLUMN_TYPE(12) = "PK|BL0|VC60|VC250|BL0"
        TABLE_DESCRIPTION(12) = "Reference Web Sites"
        TABLE_DISPLAYNAME(12) = "Web Sites"
        TABLE_INDEXCOLUMN(12) = "ID"

The part in question is where TABLE_COLUMN_LIST/TABLE_COLUMN_TYPE are processed; the variable, COUNT_TABLE is declared as 12:

   Public Sub GenerateTableCode()
       Dim __arrNames() As Array 
       Dim __arrNamesExploded() As String
       Dim __arrTypes() As Array
       Dim __arrTypesExploded() As String
       Dim __strColumnDefinition As String
       Dim __strFullTableMap As String

       For __TableCount = 0 To COUNT_TABLE - 1
           __strFullTableMap = ""
           __arrNamesExploded = TABLE_COLUMN_LIST(__TableCount).Split("|"c)
           __arrTypesExploded = TABLE_COLUMN_TYPE(__TableCount).Split("|"c)

           ReDim __arrNames(UBound(__arrNamesExploded))
           ReDim __arrTypes(UBound(__arrTypesExploded))
           __arrNames(__TableCount) = flTableGenGetElements(__arrNamesExploded(__TableCount))
           __arrTypes(__TableCount) = flTableGenGetElements(__arrTypesExploded(__TableCount))

           For __ColumnCount = 0 To UBound(__arrNames)
               __strColumnDefinition = flEncapsulate(__arrNamesExploded(__ColumnCount), "[") & " " & flTableGenExpandType(__arrTypesExploded(__ColumnCount))
               If __ColumnCount < UBound(__arrNames) Then __strColumnDefinition = __strColumnDefinition & ","
               __strFullTableMap = __strFullTableMap & __strColumnDefinition
           Next
           TABLE_CREATE_CODE(__TableCount) = __strFullTableMap

       Next __TableCount

The flTableGenElements and flTableGenExpandType functions are:

    Public Function flTableGenGetElements(ByVal _strElementList As String) As String()
        Dim __arrList() As String
        __arrList = _strElementList.Split("|"c)
        Return __arrList
    End Function

    Public Function flTableGenExpandType(ByVal _strColumnCode As String) As String
        Dim __strColumnType As String

        Select Case Left(UCase(_strColumnCode), 2)
            Case "BB"
                __strColumnType = "BLOB"
            Case "BI"
                __strColumnType = "BIGINT"
            Case "BL"
                __strColumnType = IIf(Len(_strColumnCode) = 2, "BOOLEAN DEFAULT (0)", "BOOLEAN DEFAULT (" & Mid(_strColumnCode, 3) & ")")
            Case "BU"
                __strColumnType = "UNSIGNED BIG INT"
            Case "CH"
                __strColumnType = "CHARACTER(" & Mid(_strColumnCode, 3) & ")"
            Case "CL"
                __strColumnType = "CLOB"
            Case "DA"
                __strColumnType = "DATE"
            Case "DB"
                __strColumnType = "DOUBLE"
            Case "DE"
                __strColumnType = IIf(Len(_strColumnCode) = 2, "DECIMAL", "DECIMAL (" & Mid(_strColumnCode, 3) & ")")
            Case "DP"
                __strColumnType = "DOUBLE PRECISION"
            Case "DT"
                __strColumnType = "DATETIME"
            Case "FL"
                __strColumnType = "FLOAT"
            Case "I2"
                __strColumnType = "INT2"
            Case "I8"
                __strColumnType = "INT8"
            Case "IM"
                __strColumnType = "MEDIUMINT"
            Case "IN"
                __strColumnType = "INTEGER"
            Case "IS"
                __strColumnType = "SMALLINT"
            Case "IT"
                __strColumnType = "TINYINT"
            Case "NC"
                __strColumnType = "NATIVE CHARACTER(" & Mid(_strColumnCode, 3) & ")"
            Case "NO"
                __strColumnType = "NUMERIC"
            Case "NV"
                __strColumnType = "NVARCHAR(" & Mid(_strColumnCode, 3) & ")"
            Case "PK"
                __strColumnType = "INTEGER PRIMARY KEY AUTOINCREMENT"
            Case "RE"
                __strColumnType = "REAL"
            Case "TE", "TX"
                __strColumnType = "TEXT"
            Case "VA"
                __strColumnType = "VARYING CHARACTER(" & Mid(_strColumnCode, 3) & ")"
            Case "VC"
                __strColumnType = "VARCHAR(" & Mid(_strColumnCode, 3) & ")"
            Case Else
                __strColumnType = "NULL"
        End Select

        Return __strColumnType
    End Function

The flEncapsulate function encloses the passed string with whatver token is passed to it (L/R applied as necessary, such as with the square brackets in this implementation).

My issue is that in the 4th iteration of the loop, I get an "System.IndexOutOfRangeException: 'Index was outside the bounds of the array.'" error. The array sizes correspond to the extrapolated sizes, etc. I have "played swapsies" between datasets (eg. switched table 4 with one of the other 11) to no avail. It always fails at the 4th iteration.

What I am looking to ending up with is:

__arrNames holding an exploded list of column names for the current iteration; it gets reset for the next iteration.
__arrTypes holding an exploded list of column types for the current iteration; it gets reset for the next iteration.
TABLE_CREATE_CODE(__TableCount) holding a construct derived from the two exploded lists (i.e. [ID] INTEGER PRIMARY KEY AUTOINCREMENT,[Lock] BOOLEAN DEFAULT (0),[Name] VARCHAR(25),[MediaClass] INTEGER,[Digital] BOOLEAN DEFAULT (0),[FileType] VARCHAR(4) in the case of Table 02), and I am ending up with this for TABLE_CREATE_CODE(0-3), but then the error occurs.

Any idea why, and what I need to do to fix it?

[Zvoni]

I'm not sure what's going on, but the following code conks out at the 4th iteration (of 12) and I can't figure out why.

Nitpicking: It's the 5th iteration of 13 tables in total --> It's at "Index" 4 (Table "Honorifics")
-->

and I am ending up with this for TABLE_CREATE_CODE(0-3), but then the error occurs.

As to your issue: It's called "debugging" for a reason.
Your Error says "Index out of bound".
Well, then, You ARE out of bounds.
Go step by step through it, and look at your debugging-output, watch-window, whatever you use...

FWIW, i've always been laughed at, why i keep my SQL-Code outside the Frontend.
In your case: Use a third-party SQLite-Client (e.g. DB Browser for SQLite), create your table in there, in such a client you actually can copy the SQL-Create-Statement for a Table despite having only used the visual tools,
put that statement in a Textfile (e.g. "Create_Table_Honorific.sql"), on first start read that file, throw it at your SQlite-Connection, Execute, done.

Ne need to jump through burning hoops in Frontend-code

[Erlkoenig]

Nitpicking: It's the 5th iteration of 13 tables in total --> It's at "Index" 4 (Table "Honorifics")

I know where it is, not WHY it is!

As to your issue: It's called "debugging" for a reason.

No need to be snarky! Statements like this are what gives support forums a bad reputation. ...but whatever.

FWIW, i've always been laughed at, why i keep my SQL-Code outside the Frontend.
In your case: Use a third-party SQLite-Client (e.g. DB Browser for SQLite), create your table in there, in such a client you actually can copy the SQL-Create-Statement for a Table despite having only used the visual tools,
put that statement in a Textfile (e.g. "Create_Table_Honorific.sql"), on first start read that file, throw it at your SQlite-Connection, Execute, done.

Ne need to jump through burning hoops in Frontend-code

...which misses the entire point of doing what I'm doing!

In the first place, there are reasons why the database gets created on the fly. Sure, creating it externally to have it available has its benefits, but that isn't always the case. I posted because I'm stuck and need help with the specific issue.

[Zvoni]

I know where it is, not WHY it is!

It's not about you, but the others here, who read your description.
I was looking at your Index "3" ("Genre"), while the following Index is at fault

No need to be snarky! Statements like this are what gives support forums a bad reputation. ...but whatever.

Funny how you quote the "offending" part, but totally ignore the important part following it

Your Error says "Index out of bound".
Well, then, You ARE out of bounds.
Go step by step through it, and look at your debugging-output, watch-window, whatever you use...

...which misses the entire point of doing what I'm doing!

In the first place, there are reasons why the database gets created on the fly. Sure, creating it externally to have it available has its benefits, but that isn't always the case. I posted because I'm stuck and need help with the specific issue.

And you missed the point i was trying to tell you: i never said, to KEEP that externally created Database, but to use it as a "cheatcode".
You arrive in your Frontend-code at the position you want to create the Database on the Fly: Load the Textfile.......

EDIT: Or if you really don't want to lug around several textfiles, i'd include them as ressourcestrings, and then load them from there.
Because, as far as i can see it, those are "static" Strings

EDIT: FWIW
This

For __TableCount = 0 To COUNT_TABLE - 1

ignores the last table "Website"