Function works interpreted but fails if compiled

[PercivalOgg]

Hello!

I am using version 2024B and wanted to incorporate the new ability to load maps into an array. I have a function:

Code Select Expand

;~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
#DefineFunction _dbmCollection ( conn, sql )
;~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

    IntControl ( 73,1,0,0,0 )

    tbl                         = ArrDimension ( 5000 )
    tCnt                        = -1
    cmd                         = ObjectClrNew ( 'System.Data.Odbc.OdbcCommand' )
    cmd.Connection              = conn
    cmd.CommandText             = sql
    reader                      = cmd.ExecuteReader()

    While reader.Read()

        hash                    = mapCreate ()

        For i = 0 To ( reader.FieldCount - 1 )

            fld_name            = reader.GetName( i )
            fld_value           = _rsValue ( reader, i )

            If !MapKeyExist ( hash, fld_name ) then hash [ fld_name ] = fld_value

        Next

        tCnt                   += 1
        tbl [ tCnt ]            = hash

    EndWhile

    reader.Close()
    cmd.Cancel()
    cmd.Dispose()
    reader                      = 0
    cmd                         = 0

    Drop ( reader, cmd )

    If tCnt > -1 Then

        ArrayRedim ( tbl, tCnt + 1 )

    Else

        tbl                     = ArrDimension ( 0 )

    EndIf

    GoTo _dbCollectionExit

:WBErrorHandler

    _logError ( "_dbmCollection failed", sql )

:_dbCollectionExit

    Return tbl

#EndFunction

which works fine if I execute using CWinBatch_IF or WinBatch_IF (both 64 bit), but if I compile a (small executable for networked PC's) program that calls the function it fails on the statement:

Code Select Expand

        tbl [ tCnt ]            = hash

I have copied all the system DLL's to the Windows folder from the Winbatch/System folder and the Redistrb folders, but that didn't seem to help. I know I am doing something wrong so I am hoping someone can point me in the right direction.

Thanks!

[td]

You state that it failed but you did state the error. Please provide the error.

[edit] There is no reason to move WinBatch DLLs into the system directory and what is the version of your compiler?

[PercivalOgg]

Hello td,

The error message I get is:

3647: Cannot create embedded arrays
Winbatch 64 2024A
WIL Version 6.24axa

but if I click on 'About' from the Winbatch PopMenu I get:

Winbatch Version 2024B
Winbatch DLL Version 6.24BXB
WIL DLL WBDXB44I.DLL

I am guessing that's why this works with the interpreter but not compiled.
Should I reinstall - maybe I missed a step ...

Thanks! KDB

[PercivalOgg]

Hello td,

Downloading and reinstalling the latest and greatest seems to have resolved the issue. I should have tried that first - lol.

Thanks for the attention!

KDB

[spl]

Downloading and reinstalling the latest and greatest seems to have resolved the issue. I should have tried that first - lol.

KDB, glad it was an easy fix. If you don't mind some OT comments, it was interesting to see the sql=>map=>array method you employed. I assumed because you used ObjectClrNew('System.Data.Odbc.OdbcConnection') you were dealing with a source like MySQL. In the past I would code

Code Select Expand

dt = ObjectClrNew('System.Data.DataTable')
adapter = ObjectClrNew('System.Data.Odbc.OdbcDataAdapter',cmd)
adapter.fill(dt)

then create the array directly from the datatable. Will have to play around with maps.... thanks for the code.

[PercivalOgg]

Hello Stan,

lol - that is indeed what I do. I use this function quite a bit:

Code Select Expand

;ccc  - - - - - - - - - - - - - - - - - - - - - - -
#DefineFunction _dbList ( conn, sql )
;ccc  - - - - - - - - - - - - - - - - - - - - - - -

    IntControl ( 73, 1, 0, 0, 0 )

dList = ""
    cmd                         = ObjectClrNew ( 'System.Data.Odbc.OdbcCommand' )
    cmd.Connection              = conn
    cmd.CommandText             = sql
    adapter                     = ObjectClrNew ( 'System.Data.Odbc.OdbcDataAdapter' )
    table                       = ObjectClrNew ( 'System.Data.DataTable' )
    adapter.SelectCommand       = cmd
    numRows                     = adapter.Fill(table)
    numCols                     = table.Columns.Count
data = ""

    For _row = 0 To ( numrows - 1 )

valu = ""
        row                     = table.Rows.Item( _row )

        For _col = 0 To ( numCols - 1 )

            colIdx              = table.Columns.Item( _col )
valu = ItemInsert ( StrTrim ( row.Item( colIdx ) ), -1, valu, '|' )

        Next

      data                    = ItemInsert ( valu, -1, data, @Cr )

    Next

    cmd.Cancel()
    cmd.Dispose()
    Drop ( cmd )

    GoTo _dbListExit

:WBErrorHandler

    _logError ( "_baseDbList", sql )

:_dbListExit

    Return data

#EndFunction


I do the adapter fill thing then create a list from the elements. I don't work with millions of records, so the list is fine for bulk of what I am doing.

KDB

[spl]

I do the adapter fill thing then create a list from the elements. I don't work with millions of records, so the list is fine for bulk of what I am doing.

KDB

Makes sense. Then there is always 'old school' - plain ADODB.Connection/ADODB.Recordset - which have GetString() and GetRows() methods. Actually question why they are missing in ADO.Net.

[EDIT]
Should mention I used them for WB sources like SQLite, MySQL, dBase III and IV... well before WB came out with CLR in 2013. Still work well in 2024. Excellent for getting SQL data into Excel

[spl]

KDB;
This will end my hi-jacking your thread. But as rare as it is to see code based on CLR, especially for data parsing, thought it was relevant to differentiate ADO.Net from ADODB in terms of capability.  Here goes:

Code Select Expand

.NET DataTable
Supports Various Data Types:
A DataTable in .NET can handle a wide array of data types including but not limited to:
Primitives like int, float, double, char, etc.
Complex types like DateTime, GUID, and even custom objects.
Nullable types.

ADO Recordset
Limited Data Types:
An ADO Recordset, being part of older data access technologies, supports fewer data types, mostly primitive types such as:
adInteger
adDouble
adDate
adVarChar, etc.

Key Differences
Custom Types:
A DataTable can have columns of any type, including custom user-defined classes. This is not possible with ADO Recordset.
Nullability:
A DataTable supports nullable types directly, making it more versatile in handling database nulls.
Performance & Flexibility:
The .NET DataTable is integrated with ADO.NET, providing more features, flexibility, and better performance for .NET applications.
Overall, DataTable offers a richer and more flexible data model compared to an ADO Recordset, making it suitable for more complex and modern applications.