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:
Code:
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:
Code:
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:
Code:
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?
Reply With Quote
