News:

Masm32 SDK description, downloads and other helpful links
Message to All Guests
NB: Posting URL's See here: Posted URL Change

Main Menu

Microsoft ADO (Advanced Data Objects)

Started by jj2007, January 19, 2024, 11:59:04 AM

Previous topic - Next topic

jj2007

MS ADO is an incredible mess, but I got something running and would appreciate feedback if it also works on other machines and Windows versions.

Extract the attached files to a temp folder, then drag database files over the executable. Please tell me what works and what doesn't, and your Windows version. Thanks a lot :thup:

Please tell me also if MS Office is installed on your machine; it should work without it, but I'd love to get confirmation.

The source is an ugly fat mess, no chance to publish that. However, here is a simpler use of OpenAdo (builds with MasmBasic version 19 January 2024):

include \masm32\MasmBasic\MasmBasic.inc
  Init                ; can be xls, xlsx, mdb or dbf files
  .if OpenAdo(Chr$("\Masm32\examples\IczelionSQL\jj\LifeExOECD.xls"))
    For_ ecx=0 To Min(9, OpenAdo(0))-1    ; 0=first sheet
        Print Str$("%_i  ", ecx), AdoTable$(ecx, 0)
        PrintLine At(60) Spc2$, AdoTable$(ecx, 1)
    Next
  .else
    MsgBox 0, AdoErr$(), "OpenAdo:", MB_OK
  .endif
EndOfCode

jj2007

Still fighting with M$ ADO, but it's working fine now, and handles xls, xlsx, mdb, dbf, wks and wk4 files.

I am having a lot of fun. For example, there is an obscure error 80040E21h which means "Errors occurred" (!). You can find it mentioned 7400 times googling Microsoft.JET.OLEDB "80040E21". However, 90% of the "solutions" offered are bollocks.

I chased it down on this page (a rare look at a closed source library :biggrin: ):



It turns out that the error occurs in cells with "Vermont" in the attached AdoDemoSup.xls - they have a very special cell property that is, as far as I can see, neither desired nor documented :badgrin:

There is no fix for this error (the JET driver can't handle it); however, OpenAdo will find it and insert #error# into the bad cell.

P.S.: Building the source requires MB 22 January 24

sinsi

Can't get any .xls files from the zip to work, popup dialog says
"OpenAdo: Remote ADO Error: ADO could not find the specified provider"

The ODBC data source app shows no DSNs at all but does show an xls driver

Fresh Windows 10 Home installed on VMWare Workstation
QuoteEdition   Windows 10 Home
Version   22H2
Installed on   ‎21/‎01/‎2024
OS build   19045.3803
Experience   Windows Feature Experience Pack 1000.19053.1000.0

jj2007

Thanks for the feedback, Sinsi :thup:

Mine shows no system DNS but 3 drivers on the user tab, see below. I am using the recommended strings:
    pvXls db 'Provider="Microsoft.ACE.OLEDB.12.0"', 59, 'Data Source=#;Extended Properties="Excel 12.0 Xml;"', 0
    pvMdb db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#', 0
    pvDbf db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=.;Extended Properties="dBASE IV;"', 0
    pvWkx db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#;Extended Properties="Lotus WK4"', 0

My notebook is a recent Windows 10 machine, almost virgin. However, I did install a) the Microsoft Office Excel viewer and b) the Microsoft Access Runtime 2013.

Further investigation shows that the Excel viewer does not install the missing driver; it's the MS Access runtime that makes xls and xlsx files work with OpenAdo.

The great majority of web pages dealing with MsAdo recommends the Microsoft.JET.OLEDB.4.0 provider, so I had assumed it was installed :sad:

OpenAdo uses ACE.OLEDB.12 for xls and xlsx, but for *.xls files, this also works:
pvXls db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#;Extended Properties="Excel 8.0;"', 0
I've made a test with a Windows 7 VM, which appears to be virgin. So I can confirm that the Excel files don't work without the Access Runtime, but mdb (Access), dbf, wk4 and wks work fine. I realise I hadn't included any Access files to the test package - here they are, attached below.

TimoVJL

Just a simple test:
"Data Source=WorldCo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""nRecs = -1
TABLE_NAME      Database
TABLE_NAME      World$
Excel 2003 connection strings
May the source be with you


TimoVJL

same old schema code
#define UNICODE
#define _UNICODE
#define WIN32_LEAN_AND_MEAN
#include <windows.h>
#define INITGUID
#include <ole2.h>
#include <stdio.h>
#include <tchar.h>

#include "msado15.h"

#pragma comment(lib, "user32.lib")
#pragma comment(lib, "ole32.lib")
#pragma comment(lib, "oleaut32.lib")

LPTSTR ErrorString(DWORD errno)
{
    static TCHAR szMsgBuf[0x1000];
    //DWORD msglen =
    FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM|FORMAT_MESSAGE_IGNORE_INSERTS,
        NULL, errno, MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), (LPTSTR) & szMsgBuf, 0x1000 * sizeof(TCHAR), NULL);
    //MessageBox(0, szMsgBuf, szAppName, MB_OK);
    return szMsgBuf;
}

void ADOError(Connection15 *pCn)
{
    HRESULT hr;
    Errors *pErrors = NULL;
    hr = pCn->lpVtbl->get_Errors(pCn, &pErrors);
    if (pErrors) {
        LONG nErrCnt;
        pErrors->lpVtbl->get_Count(pErrors, &nErrCnt);
        printf("Error count %d\n", nErrCnt);
        if (nErrCnt) {
            Error *pError = NULL;
            VARIANT vIdx;
            vIdx.vt = VT_INT;
            vIdx.lVal = 0;
            hr = pErrors->lpVtbl->get_Item(pErrors, vIdx, &pError);
            if (!hr) {
                LONG nErr;
                pError->lpVtbl->get_Number(pError, &nErr);
                BSTR bstrErr;
                pError->lpVtbl->get_Description(pError, &bstrErr);
                printf("%Xh %ls\n", nErr, bstrErr);
                SysFreeString(bstrErr);
            }
        }
    }
}

//int main(int argc, char **argv)
int main(void)
{
    HRESULT hr;
    Connection15 *pCon = NULL;
    //Recordset15 *pRst = NULL;
    _Recordset *pRst = NULL;

    hr = CoInitialize(NULL);
    hr = CoCreateInstance(&CLSID_Connection, NULL, CLSCTX_ALL, &IID__Connection, (void **)&pCon);
    if (hr)
        MessageBox(0, TEXT("Error CoCreateInstance()"), 0, 0);
    else
    {
        VARIANT v1, v2;
        VariantInit(&v1);
        VariantInit(&v2);
        BSTR bstrProvider = SysAllocString(L"Microsoft.JET.OLEDB.4.0");
        BSTR bstrConStr = SysAllocString(L"Data Source=WorldCo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");

        hr = pCon->lpVtbl->put_Provider(pCon, bstrProvider);
        hr = pCon->lpVtbl->put_ConnectionString(pCon, bstrConStr);
        hr = pCon->lpVtbl->Open(pCon, NULL, NULL, NULL, adConnectUnspecified);
        //hr = pCon->lpVtbl->Open(pCon, bstrConStr, NULL, NULL, adConnectUnspecified);
        SysFreeString(bstrProvider);
        SysFreeString(bstrConStr);
        LONG nState;
        hr = pCon->lpVtbl->get_State(pCon, &nState);
        if (nState == adStateOpen) {
            SAFEARRAY *pSA = NULL;
            SAFEARRAYBOUND rgsabound;
            rgsabound.lLbound = 0; 
            rgsabound.cElements = 4; 
            pSA = SafeArrayCreate(VT_VARIANT, 1, &rgsabound);

            v1.vt = VT_EMPTY;

            long idx;
            for (int i = 0; i < 3; i++) {
                idx = i;
                SafeArrayPutElement(pSA, &idx, &v1);
            }
            v1.vt = VT_BSTR;
            v1.bstrVal = SysAllocString(L"TABLE");
            idx = 3;
            SafeArrayPutElement(pSA, &idx, &v1);

            VARIANT vtCriteria;
            vtCriteria.vt = VT_ARRAY|VT_VARIANT;
            vtCriteria.parray = pSA;
           
            VARIANT vtMissing;
            //vtMissing.vt = VT_EMPTY;
            vtMissing.vt = VT_ERROR;
            vtMissing.scode = DISP_E_PARAMNOTFOUND;

            //hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtMissing, vtMissing, &pRst);
            hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtCriteria, vtMissing, &pRst);
            if (!hr) {
                Fields* pFlds;
                Field* pFld;
                long nRecs = 0;
                VARIANT_BOOL vb = 0;
                hr = pRst->lpVtbl->get_RecordCount(pRst, &nRecs);
                printf("nRecs = %d\n", nRecs);
                VARIANT vName;
                vName.vt = VT_BSTR;
                vName.bstrVal = SysAllocString(L"TABLE_NAME");
                while (!pRst->lpVtbl->get_EOF(pRst, &vb) && vb == 0) {
                //while (!pRst->lpVtbl->MoveNext(pRst)) {    // don't work
                    hr = pRst->lpVtbl->get_Fields(pRst, &pFlds);
                    if (!hr) {
                        hr = pFlds->lpVtbl->get_Item(pFlds, vName, &pFld);
                        if (!hr) {
                            BSTR bstrTmp;
                            hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
                            if (!hr) {
                                printf("%ls\t", bstrTmp);
                                SysFreeString(bstrTmp);
                            }
                            VARIANT v3;
                            VariantInit(&v3);
                            v3.vt = VT_BSTR;
                            v3.bstrVal = bstrTmp;
                            hr = pFld->lpVtbl->get_Value(pFld, &v3);
                            printf("%ls\t", v3.bstrVal);
                            SysFreeString(bstrTmp);
                            pFld->lpVtbl->Release(pFld);
                        }
                        pFlds->lpVtbl->Release(pFlds);
                        printf("\n");
                    }
                    hr = pRst->lpVtbl->MoveNext(pRst);
                    if (hr) break;
                }
                // done
                pRst->lpVtbl->Release(pRst);
                printf("\nRecordset close\n");
            } else {
                //printf("hr=%Xh %ls\n", hr, GetADOErrorCode(hr));
                printf("hr=%Xh %ls\n", hr, ErrorString(hr));
                ADOError(pCon);
            }
        } else {
            printf("adStateClosed\n");
            //printf("hr=%Xh\n", hr);
            ADOError(pCon);
        }
        pCon->lpVtbl->Release(pCon);
    }
    CoUninitialize();
    return 0;
}
May the source be with you

jj2007

So how does your code...
        BSTR bstrProvider = SysAllocString(L"Microsoft.JET.OLEDB.4.0");
        BSTR bstrConStr = SysAllocString(L"Data Source=WorldCo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");

... differ from mine?
pvXls db 'Provider="Microsoft.ACE.OLEDB.12.0"', 59, 'Data Source=#;Extended Properties="Excel 12.0 Xml;"', 0
pvMdb db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#', 0

I use a more recent provider that allows to read both *.xls and *.xlsx files.

NoCforMe

@Timo: just a question:

Do you ever post assembly-language code here? All I've ever seen from you is C. Hmm; looking at the name of this whole website ...

Just askin'.
Assembly language programming should be fun. That's why I do it.

sinsi

I noticed that you were using ODBC 64-bit, so I had a look - no DSNs at all and one driver (for SQL server).
The two mdb files opened OK :thumbsup:

jj2007

Quote from: sinsi on January 24, 2024, 11:47:38 PMyou were using ODBC 64-bit

How do you determine that? My code is definitely 32-bit...

sinsi

Quote from: jj2007 on January 24, 2024, 11:58:26 PM
Quote from: sinsi on January 24, 2024, 11:47:38 PMyou were using ODBC 64-bit

How do you determine that? My code is definitely 32-bit...
Your two screenshots  :biggrin:

jj2007

Quote from: sinsi on January 25, 2024, 12:07:42 AMYour two screenshots  :biggrin:

Oops :biggrin:


To the left, C:\Windows\System32\odbcad32.exe (note there is 2x "32" in the path - Micros*t...)
To the right, C:\Windows\SysWOW64\odbcad32.exe

Not much difference except for the drivers tab :rolleyes:

jj2007

Attached my spreadsheet editor. It's not yet ready for the ShowCase, so I post it here, hoping to get some feedback on bugs.

Note it has only a right-click context menu. You can either drag a file over the exe, or use the Open spreadsheet function.
Allowed file types are tab, txt and csv, plus xls, xlsx, mdb, dbf, wks for those who have the ADO drivers installed.

Extract all files in the archive to a folder, then drag the tab or xls file over the exe.



P.S.: see also SQL/Microsoft ADO (Advanced Data Objects) VT_DATE quirk