Hey ho. I'm working on a little project with ODBC lately (movie database
HTML exporter - yeah, I should use PHP for this, but I felt like playing
with ODBC, don't have the time to learn PHP, and anyway it's for a friend
who doesn't (yet) run apache with PHP).

Now, my problem... I find ODBC somewhat messy, and not all that well
documented. The information in the MDAC SDK seems outdated, and written
more for "database developers" than "normal" programmers, and certainly
does not live up to the standard of the PlatformSDK.

My data source is a local access database. I'd like the user to be able
to specify the path in the commandline, ie something like "dbtool d:\docs\test.mdb".
I have, however, not figured out how to make ODBC open a database 'directly'
like that. I don't feel like setting up one of those DSN(?) thingies.
My current approach is a call to SQLDriverConnect(), which pops up a dialog
where the user has to go through a bunch of tedious steps to open the database.

I'm developing this in C/C++, so I don't necessarily have to use ODBC (hiroshimator
suggested ADO), as long as the API is able to work with M$ Access databases and
can execute SQL statements - so no, using other database formats (eg tsunami) is
not an option (for now - I might change later... but for now it's nice being able
to use access instead of coding my own database frontend).

If there's sometihng 'cleaner' than ODBC with better documentation that can
get the job done, lemme hear :). And no, programming in VB, Delphi or BCB isn't
an option, and I'd like to keep MFC out of the game, too.

Thanks in advance for any suggestions :)
Posted on 2002-06-18 10:41:35 by f0dder
Hi!

First of all you should get Iczelions ODBC tutorial at his homepage.
Look at the example program in the last Tutorial... it shows how to access a Access ( :) ) Database via ODBC.

I think this should clear things up for you!
Posted on 2002-06-18 10:52:12 by bazik
Thanks bazik, that worked. Dunno how I missed icz' ODBC tutorials :).

Still, if anybody has recommendations on other APIs, I'd like to hear -
I find ODBC pwetty damn ugly.
Posted on 2002-06-18 11:11:06 by f0dder
Sorry, I can't help, but I tried to do something using ODBC... and I encoutered the same problems as you... the documentation is messy, and unclear at many aspects...

Iczelion's tutorials as good, but only an introduction...

Personally, It was for fun, and I gived up this project for now... mainly because of the lack of clear documentation... (and lack of time to do time consuming tries researches)

Just to tell you you are not alone...

Btw, if you find a good information source about that, please let me know...

FYI, I played a bit with php, to try how long it would take to do my site with it, and it was very fast to learn... and the information is good to not say everywhere...
Posted on 2002-06-18 11:29:21 by JCP
Posted on 2002-06-18 11:40:34 by bazik
bazik, that documentation is available for download in the MDAC sdk
somewhere at microsoft's site. But (at least) the ODBC part of the
documentation is rather sucky :/
Posted on 2002-06-18 12:10:26 by f0dder
Well, if you want ODBC easy, you must use VB :tongue:
Posted on 2002-06-18 12:13:10 by bazik
No, use MS Access to make your query's and copy/paste them into your program :) (select SQL view)
Posted on 2002-06-18 12:20:16 by Qweerdy
Constructing SQL queries is not the problem - the uglyness and bad
documentation of the ODBC api is :)
Posted on 2002-06-18 12:24:43 by f0dder

No, use MS Access to make your query's and copy/paste them into your program :) (select SQL view)


Yeah, you get 100% Microsoft SQL code then, wich is 100% incompatibel with every SQL standard! :rolleyes:
Posted on 2002-06-18 12:24:49 by bazik
f0dder,
i endorse what Hiro said, use ADO. I use it extensively for both
Access and Oracle databases, and it really does make things easy.

In ADO, create a Connection object, then call its Open
method with the following string:
[size=12]

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="[color=red]<local database path>[/color]";Persist Security Info=False"[/size]


Once you have established the connection, you can just use the
Execute method of the Connection object to execute your
queries, in pseudocode it looks like this:


Set <recordset name> = <connection object>.Execute(<your sql string>)

or just:

<connection object>.Execute <your sql string>


As for the sql, i have never had a problem with it, Access
understands all the standard stuff (except the joins are a little
different). Just remember though, if you insert short dates, they
should be in American format (mm/dd/yy).

If you do use ADO, use v2.5, there is little or no benefit in using
v2.6 or v2.7, and in fact v2.6 needs a service pack applied to make
it work properly. Also, if you need to create tables/indexes, etc.,
either swat up on the sql required, or use DAO instead.
Posted on 2002-06-18 20:32:13 by sluggy
sluggy,

using ADO in VB is fine, but is it still fine coding in native C(++)? Without MFC/ATL? With all the VARIANT parms used by these interfaces?

japheth
Posted on 2002-06-19 11:30:09 by japheth
Hi Fodder really try PHP & MYSQL

I made this for my site

http://www.pinklightning.com/connex.php

I use it because it's cheap and highly effective

But if you really want to go ASM, try out my ASM CGI stuff, you may find some of the code, here and there could be optimized, you'll have to figure out the ODBC part, MayBe go for it, if you do I'll put it up on my host which is good till March 2003, you can run
ASM CGI's on it

Andy981
Posted on 2002-06-19 22:20:12 by andy981
japheth,
without a doubt, using ADO in straight C or asm is a lot more difficult/time consuming than using it in VB, but can still be worth the effort. I have never done 'raw' COM in either of those languages, so i cannot say for sure how much work it would be, but having the ability to do something like this would be great:


lea eax, <buffer holding your sql string>
push eax
mov eax, <interface ptr>
call dword ptr [eax + <offset to 'execute' function entry>]


There are not that many variants involved, and all you have to remember is that essentially they are just structs.....
Posted on 2002-06-19 23:58:10 by sluggy
Here is a boat load of ADO connection strings


http://www.able-consulting.com/ADO_Conn.htm
Posted on 2002-06-20 15:56:59 by andy981
f0dder

This came in the mail today. I can't say enough about the possibilities of PHP & MySQL.


MySQL and ODBC
In MySQL: http://www.devshed.com/Server_Side/MySQL

Learn it from start to finish. Installing MyODBC, creating a
new data source through the ODBC Data Source Administrator,
linking a MySQL database into a new MS Access database, and
finally updating the MySQL database through an MS Access
GUI.

farrier
Posted on 2002-06-20 16:29:10 by farrier



Yeah, you get 100% Microsoft SQL code then, wich is 100% incompatibel with every SQL standard! :rolleyes:


almost all if not all RDBMS 'enhance' their SQL implementation. Most don't even succeed to implement the '92 standards completely, let alone the '99 one.

There is nothing wrong with writing for a specific database.
Posted on 2002-06-20 18:13:01 by Hiroshimator
sluggy,

heres an excerpt in ASM filling a listbox with ADO:



OnInitDialog proc uses esi hWnd:HWND, lParam:LPARAM

local hWndLB:HWND
local pRS:LPRECORDSET20
local pFs:ptr Fields
local pF:ptr Field
local vSource:VARIANT
local vActiveConnection:VARIANT
local vTemp:VARIANT
local vbTemp:VARIANT_BOOL
local szRow[256]:byte
local szFld1[80]:byte
local szFld2[80]:byte


lea eax,vTemp
invoke VariantInit, eax

invoke GetDlgItem, hWnd, IDC_LIST1
mov hWndLB,eax

;--------------------------------- create recordset
invoke CoCreateInstance, addr CLSID_Recordset, 0, CLSCTX_INPROC_SERVER, addr IID_Recordset20, addr pRS
.if (eax != S_OK)
invoke MessageBox, hWnd, CStr("CoCreateInstance failed"), 0, MB_OK
ret
.endif

;--------------------------------- open recordset
lea eax,vSource
invoke VariantInit, eax
mov vSource.vt,VT_BSTR
mov vSource.bstrVal,offset wszSQLStatement

lea eax,vActiveConnection
invoke VariantInit, eax
mov vActiveConnection.vt,VT_BSTR
mov vActiveConnection.bstrVal,offset wszFileDSN

invoke vf(pRS, IRecordset20, Open), vSource, vActiveConnection, \
adOpenForwardOnly, adLockReadOnly, -1
.if (eax != S_OK)
invoke MessageBox, hWnd, CStr("IRecordset::Open failed"), 0, MB_OK
invoke vf(pRS, IRecordset20, Release)
ret
.endif

xor esi,esi
.while (1)
;--------------------------------- check for EOF
invoke vf(pRS, IRecordset20, get_EOF),addr vbTemp
movsx eax,vbTemp
.break .if (eax)
mov szFld1,0
mov szFld2,0
;--------------------------------- get fields collection
invoke vf(pRS, IRecordset20, get_Fields), addr pFs
.if (eax == S_OK)

;--------------------------------- get first field
mov vTemp.vt,VT_I4
mov vTemp.lVal,0
invoke vf(pFs, Fields, get_Item), vTemp, addr pF
.if (eax == S_OK)
invoke vf(pF, Field, get_Value), addr vTemp
.if (vTemp.vt == VT_BSTR)
invoke WideCharToMultiByte, CP_ACP, 0, \
vTemp.bstrVal, -1, addr szFld1, sizeof szFld1, NULL, NULL
invoke SysFreeString, vTemp.bstrVal
.endif
invoke vf(pF, Field, Release)
.endif

;--------------------------------- get second field
mov vTemp.vt,VT_I4
mov vTemp.lVal,1
invoke vf(pFs, Fields, get_Item), vTemp, addr pF
.if (eax == S_OK)
invoke vf(pF, Field, get_Value), addr vTemp
.if (vTemp.vt == VT_BSTR)
invoke WideCharToMultiByte, CP_ACP, 0, \
vTemp.bstrVal, -1, addr szFld2, sizeof szFld2, NULL, NULL
invoke SysFreeString, vTemp.bstrVal
.endif
invoke vf(pF, Field, Release)
.endif

invoke vf(pFs, Fields, Release)
.endif

inc esi
invoke wsprintf, addr szRow, CStr("%u., %s, %s"), esi, addr szFld1, addr szFld2
ListBox_AddString hWndLB, addr szRow

;--------------------------------- move cursor to next row
invoke vf(pRS,IRecordset20,MoveNext)
.break .if (eax != S_OK)
.endw
invoke vf(pRS, IRecordset20, Release)

ret
OnInitDialog endp


Same in VB:



Private Sub Form_Load()
Set rs = New Recordset
rs.Open "select xxx,yyy from tablex", "File Name=c:\xxx\db1.dsn"
While Not rs.EOF
List1.AddItem rs(0) & "," & rs(1)
rs.MoveNext
Wend

End Sub


The ASM version is really ugly. C++ will look a bit better though.
Posted on 2002-06-20 19:15:39 by japheth