Pelles C forum

C language => Tips & tricks => Topic started by: TimoVJL on November 25, 2014, 10:52:17 AM

Title: Example using ado
Post by: TimoVJL on November 25, 2014, 10:52:17 AM

This is just an example using ado

Code: [Select]

#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")

TCHAR *GetADOErrorCode(DWORD nErr);

int main(int argc, char **argv)
{
HRESULT hr;
Recordset15 *pRst = NULL;

hr = CoInitialize(NULL);
hr = CoCreateInstance(&CLSID_Recordset, NULL, CLSCTX_ALL, &IID_Recordset15, (void **)&pRst);
if (hr)
MessageBox(0, TEXT("Error CoCreateInstance()"), 0, 0);
else
{
VARIANT v1, v2;
VariantInit(&v1);
v1.vt = VT_BSTR;
v1.bstrVal = SysAllocString(L"SELECT * FROM test");
VariantInit(&v2);
v2.vt = VT_BSTR;
v2.bstrVal = SysAllocString(L"Driver={Microsoft Access Driver (*.mdb)};Dbq=test.mdb;");
// STDMETHOD(Open)(THIS,VARIANT,VARIANT,enum CursorTypeEnum,enum LockTypeEnum,LONG);
hr = pRst->lpVtbl->Open(pRst, v1, v2, adOpenKeyset, adLockUnspecified, adCmdText);
SysFreeString(v1.bstrVal);
SysFreeString(v2.bstrVal);
//hr = pRst->lpVtbl->Open(pRst, v1, v2, 1, -1, -1);
if (!hr) {
Fields* pFlds;
Field* pFld;
long nRecs = 0;
hr = pRst->lpVtbl->get_RecordCount(pRst, &nRecs);
_tprintf(TEXT("nRecs = %d\n"), nRecs);
hr = pRst->lpVtbl->get_Fields(pRst, &pFlds);
if (!hr) {
long nFlds;
VARIANT vIdx;
hr = pFlds->lpVtbl->get_Count(pFlds, &nFlds);
_tprintf(TEXT("nFlds = %d\n"), nFlds);
VariantInit(&vIdx);
vIdx.vt = VT_I4;
for (int i = 0; i < nFlds; i++) {
vIdx.lVal = i;
hr = pFlds->lpVtbl->get_Item(pFlds, vIdx, &pFld);
if (!hr) {
BSTR bstrTmp;
VARIANT v3;
VariantInit(&v3);
v3.vt = VT_BSTR;
v3.bstrVal = bstrTmp;
hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
if (!hr) {
_tprintf(bstrTmp);
SysFreeString(bstrTmp);
}
hr = pFld->lpVtbl->get_Value(pFld, &v3);
if (!hr) {
if (v3.vt == VT_BSTR) {
_tprintf(TEXT(" %ls\n"), bstrTmp);
SysFreeString(bstrTmp);
} else {
VariantChangeType(&v3, &v3, VARIANT_LOCALBOOL, VT_BSTR);
_tprintf(TEXT(" %ls\n"), bstrTmp);
SysFreeString(bstrTmp);
}
}
pFld->lpVtbl->Release(pFld);
}
}
pFlds->lpVtbl->Release(pFlds);
}
} else
_tprintf(TEXT("%Xh %ls\n"), hr, GetADOErrorCode(hr));
pRst->lpVtbl->Release(pRst);
}
CoUninitialize();

//MessageBox(0, TEXT("Done"), 0, 0);
return 0;
}Let me know, if there is something wrong.

Title: Re: Example using ado
Post by: czerny on November 25, 2014, 01:35:34 PM

If the database doesn't exist, I get: 80004005h Unspecified Error
Shouldn't be used in this case:  800A0F0Bh Database does not exist?

Title: Re: Example using ado
Post by: TimoVJL on January 09, 2024, 03:48:25 PM

A simple ADO test, not useful
Those who have MS Office or AccessDatabaseEngine installed can test it with test.mdb

Title: Re: Example using ado
Post by: jj2007 on January 09, 2024, 05:26:49 PM

Looks good, Timo, and works fine, but how do you get the names of tables after OpenSchema? I tried this:

Code: [Select]

hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtCriteria, vtMissing, &pRst);
if (!hr) {
printf("Recordset tables=%i\n", pRst);
printf("Count=%i\n", pRst->GetRows);
pRst->lpVtbl->Release(pRst); // release the _Recordset
printf("Recordset close\n");
Unknown field 'GetRows' of '_Recordset (aka struct _Recordset)'
 :(

Title: Re: Example using ado
Post by: TimoVJL on January 09, 2024, 06:33:28 PM

something like this:

Code: [Select]

hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtCriteria, vtMissing, &pRst);
if (!hr) {
Fields* pFlds;
Field* pFld;
long nRecs = 0;
hr = pRst->lpVtbl->get_RecordCount(pRst, &nRecs);
printf("nRecs = %d\n", nRecs);
hr = pRst->lpVtbl->get_Fields(pRst, &pFlds);
if (!hr) {
long nFlds = 0;
hr = pFlds->lpVtbl->get_Count(pFlds, &nFlds);
printf("nFlds = %d\n", nFlds);
VARIANT vIdx;
VariantInit(&vIdx);
vIdx.vt = VT_I4;
for (int i = 0; i < nFlds; i++) {
vIdx.lVal = i;
hr = pFlds->lpVtbl->get_Item(pFlds, vIdx, &pFld);
if (!hr) {
BSTR bstrTmp;
VARIANT v3;
VariantInit(&v3);
v3.vt = VT_BSTR;
v3.bstrVal = bstrTmp;
hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
if (!hr) {
printf("%ls\n", bstrTmp);
SysFreeString(bstrTmp);
}
}
pFld->lpVtbl->Release(pFld);
}
pFlds->lpVtbl->Release(pFlds);
}
// done
pRst->lpVtbl->Release(pRst);
printf("Recordset close\n");
}

Title: Re: Example using ado
Post by: Vortex on January 09, 2024, 07:38:21 PM

Hi Timo,

Thanks, both of your applications are processing correctly the database file on Windows 7 Sp1 64-bit.

Title: Re: Example using ado
Post by: jj2007 on January 09, 2024, 09:10:07 PM

Hi Timo,
This works fine so far, but it returns as names
 TABLE_CATALOG
 TABLE_SCHEMA
 TABLE_NAME
 TABLE_TYPE
etc. - the labels. What I am really interested in is the value of TABLE_NAME, i.e. the sheet's actual name, e.g. main.

What am I doing wrong here? It compiles with TheValue, but I get error 800a0bcd (EOF or BOF) and a nullstring :(

Code: [Select]

hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
if (!hr) {
VARIANT TheValue;
VariantInit(&TheValue);
TheValue.vt = VT_BSTR;
printf("Item %ls\n", bstrTmp);
hr = pFld->lpVtbl->get_Value(pFld, &TheValue);
printf("val %x %ls\n", hr, TheValue.bstrVal);
SysFreeString(bstrTmp);
}

Btw googling for "get_Fields" "get_name" "get_value" "get_RecordCount" puts this post on rank #2 - congrats :)

Title: Re: Example using ado
Post by: John Z on January 09, 2024, 09:25:22 PM

Hi TimoVJL,

Quote from: TimoVJL on January 09, 2024, 03:48:25 PM

A simple ADO test, not useful
Those who have MS Office or AccessDatabaseEngine installed can test it with test.mdb

How to test? I tried as posted and tried using the full path to test.mdb.
All I get is:

Code: [Select]

C:\Users\John\Downloads\Pelles\msado>msado_test2_64.exe
adStateClosed
Error count 0
I can open test.mdb with Access and see the table and entries.

Well, worth mentioning this is WIN11

John Z

Title: Re: Example using ado
Post by: jj2007 on January 09, 2024, 09:39:52 PM

> using the full path

Hi John,

Try escaping the backslashes: D:\\PellesC\\test.mdb should work, at least it does on my Win10 machine.

@Timo: please see reply #6

Title: Re: Example using ado
Post by: TimoVJL on January 09, 2024, 09:51:14 PM

Hi John Z,
The msado_test2_64.exe won't work, as 64-bit don't have "Microsoft.JET.OLEDB.4.0" working connection string.
For x64 it's something like "Microsoft.ACE.OLEDB.12.0"
If you have 32-bit MS Office, it doesn't help.

Title: Re: Example using ado
Post by: John Z on January 09, 2024, 10:34:56 PM

Thanks TimoVJL, got it, looks good with the non-64 bit version
running Windows 11 Home, 23H2, OS Build 22631.2861
results:

Code: [Select]

C:\Users\John\Downloads\Pelles\msado>msado_test2.exe
nRecs = -1
nFlds = 9
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED
Recordset close
Thank jj2007 - I did double \ because it would not compile otherwise.  Cheers

John Z

Title: Re: Example using ado
Post by: TimoVJL on January 10, 2024, 09:48:51 AM

With this version:

Code: [Select]

nRecs = -1
TABLE_NAME      test

Recordset close

Title: Re: Example using ado
Post by: jj2007 on January 10, 2024, 01:41:46 PM

Code: [Select]

nRecs = -1
TABLE_NAME      JJWasHere

Recordset close
Compliments, Timo - you are a genius :)

I had played around with the OpenSchema needs a SAFEARRAY problem, without success. Thanks a lot for looking into this.

Code: [Select]

BSTR bstrConStr = SysAllocString(L"Data Source=test.xls;Extended Properties='Excel 8.0;HDR=NO'");
Yields the name of an Excel sheet. Excellent :)

Same for dbf:

Code: [Select]

BSTR bstrConStr = SysAllocString(L"Data Source=.;Extended Properties='dBASE IV'");
The first line causes a linker error for me ("Unresolved external symbol '_GetADOErrorCode'"):

Code: [Select]

//printf("hr=%Xh %ls\n", hr, GetADOErrorCode(hr));
//ADOError(pCon);

Title: Re: Example using ado
Post by: TimoVJL on January 10, 2024, 03:49:19 PM

That ADOErrors.c is in msado_test2_WS1.zip amd msado_test2_WS1.zip, if you need it.

Title: Re: Example using ado
Post by: jj2007 on January 12, 2024, 01:41:57 PM

Here is a list of the connection strings that work for me on Windows 10:

Code: [Select]

; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=test2000.mdb', 0
; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=testjj5.xls;Extended Properties="Excel 8.0"', 0
; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=testjj5.xls;Extended Properties="Excel 8.0;HDR=NO"', 0
; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=test2000.mdb', 0 ; ok
; MyCon$ db 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.;Extended Properties=dBASE IV;', 0 ; ok

Note that the table name for Excel files is kind of boring: it always returns Database
For dBase, in contrast, you can only specify the folder, not a specific file, and it picks files from that folder randomly. Maybe it can be solved with SELECT * from specificfile.xls, I'll have to test that.

Thanks a lot, Timo, for all the work you put into this :)

Title: Re: Example using ado
Post by: jj2007 on January 13, 2024, 11:53:46 PM

I've been playing a bit, and finally managed to get OpenSchema running in order to get the sheet names:

Windows 7 VM (no Excel, no Access, no Office):

Code: [Select]

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj3$
opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
---------------- open failed ----------- $AdoErr$() Unspecified Error
opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
---------------- open failed ----------- $AdoErr$() Unspecified Error
opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
opening test2000.mdb
The table name is JJWasHere
opening E:\AdoTest\main2002.mdb
The table name is main
Win10 (Office 2003 installed):

Code: [Select]

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj3$
opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj4$
opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
The table name is JJ5Test$
The table name is TestB5$
The table name is TestC5$
opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
opening test2000.mdb
The table name is JJWasHere
opening E:\AdoTest\main2002.mdb
The table name is main
Full string is e.g.
Provider="Microsoft.JET.OLEDB.4.0";Data Source=testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"

As mentioned earlier, DBase IV works on a per folder basis, but you can read specific files by restricting the data to the sheet name, which happens to be the file name.

Title: Re: Example using ado
Post by: TimoVJL on January 14, 2024, 10:29:37 AM

Windows 7 SP1 Breaks ADO Compatibility (https://answers.microsoft.com/en-us/windows/forum/all/windows-7-sp1-breaks-ado-compatibility/12fa557c-d7f5-479f-9cc2-a147e3ad8ba5)
An ADO-based application that is compiled in Windows 7 SP1 or in Windows Server 2008 R2 SP1 does not run in earlier versions of Windows (https://support.microsoft.com/en-us/topic/an-ado-based-application-that-is-compiled-in-windows-7-sp1-or-in-windows-server-2008-r2-sp1-does-not-run-in-earlier-versions-of-windows-6cb6e820-1f5f-b1f3-48e9-de5841bf60ed)
"Type Mismatch" error message when you run a VBA macro in a 64-bit version of an Office 2010 application (https://support.microsoft.com/fi-fi/topic/-type-mismatch-error-message-when-you-run-a-vba-macro-in-a-64-bit-version-of-an-office-2010-application-ba8aae20-0762-4108-93de-1787f6e09076)

Title: Re: Example using ado
Post by: jj2007 on January 14, 2024, 12:44:08 PM

Yes, it's messy. My "solution" will be that it works only partially on Win7.
For Win10, I am making progress: the query works for Excel and Access files (but not for Dbase files).

Windows 10:

Code: [Select]

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is TestJJ3$
Query Ole$      $AdoErr$()      All is fine

opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj4$
Query Ole$      $AdoErr$()      All is fine

opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
The table name is JJ5Test$
The table name is TestB5$
The table name is TestC5$
Query Ole$      $AdoErr$()      All is fine

opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
Query Ole$      $AdoErr$()      Unspecified Error

opening test2000.mdb
The table name is JJWasHere
Query Ole$      $AdoErr$()      All is fine

opening E:\AdoTest\main2002.mdb
The table name is main
Query Ole$      $AdoErr$()      All is fine
Windows 7:

Code: [Select]

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj3$
Query: [SELECT * from [Database]]
Query Ole$ $AdoErr$() All is fine

opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
---------------- open failed ----------- $AdoErr$() Unspecified Error

opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
---------------- open failed ----------- $AdoErr$() Unspecified Error

opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
Query: [SELECT * from World_Countries]
Query Ole$ $AdoErr$() All is fine

opening test2000.mdb
The table name is JJWasHere
Query: [SELECT * from [JJWasHere]]
Query Ole$ $AdoErr$() All is fine

opening E:\AdoTest\main2002.mdb
The table name is main
Query: [SELECT * from [main]]
Query Ole$ $AdoErr$() All is fine

opening E:\AdoTest\test2015.mdb
The table name is main
Query: [SELECT * from [main]]
Query Ole$ $AdoErr$() All is fine
P.S.: All is fine for DBase III+IV but not for DBase II