Tweet
for my sixth example program in 'beginning win32 sdk style programming example series' here in
the source code forum...
http://www.powerbasic.com/support/pb...ad.php?t=25174
i used the odbc sqldrivers() function to amass database driver information to load a text box
that covers the entire extent of a display form/dialog. the example there was somewhat elementary,
except for the odbc sqldrivers() call, which is rather involved. it occurred to me that the flow
of the series itself and my attempts at providing clear explanations would be best served if i
posted the sqldrivers() explanations in a seperate post not part of the 'beginning ...' series.
that way for learners who are just interested in the mechanics of getting multiple dialog projects
running, or for those who are just interested in seeing how to do simple scrollable information
display, the complexities of the sqldrivers() function could be relegated to that of a 'black box',
out of which information faithfully if not somewhat mysteriously flowed. the comment thread for the
sdk series is at...
http://www.powerbasic.com/support/pb...ad.php?t=14429
...and comments on this thread could also go there. so in this seperate post in a different
thread i'll attempt to give a blow by blow and detailed explanation for the sqldrivers() function
for those who are interested. below is the full code for an example that should run for both the
console compiler and pb windows (uses a display macro). note that i abstracted from the odbc
includes at:
http://www.powerbasic.com/files/pub/...e/pbodbc30.zip
the eight equates and four functions necessary to make this example work. sqldrivers() is in
odbc32.dll, so this setup is necessary for the program to run. in this first and full
program they are listed right after the win32api.inc include. in the explanatory programs after-
wards i didn't want to include them in every program so as to save space, so i am asking you to
copy them to an include file named odbcincs.inc. you'll note that include in the programs after
this one.
if you ran the example you will have noted that for each driver there is a verbose description,
such as, 'sql server' or 'microsoft access driver (*.mdb)', followed by a blank line and half a
dozen or so attribute/value pairs. on my system the first driver for which information is provided
is the sql server driver like so...
sql server
usagecount=5
sqllevel=1
fileusage=0
driverodbcver=02.50
connectfunctions=yyy
apilevel=2
cptimeout=60
and this format of data is repeated for all drivers on the system on which the program is
running. now the thing is, this format of the data was created by the program, as it makes sense
to view it in that manner. sqldrivers() does not present it in that format. the format
sqldrivers() presents the data in is one where a string buffer provided by the program for the
driver description is filled with that description, and another buffer provided for the
attribute/value pairs is filled with that information - all in one asciiz character string buffer
with unchanging address and each attribute/value pair seperated by a null byte. in addition, the
odbc functions always return in a seperate buffer the numbers of bytes returned in adjacent
character string buffers. our work in this example will be to use all the information provided by
sqldrivers() so as to format it into the readily readable format that you see when you run the
program.
so lets begin. the first thing you always have to do when using odbc functions is set up an
odbc environment - sometimes called a 'workspace' in other contexts, and set the odbc version. a
handle to an odbc environment is first obtained by calling the following function:
sqlallochandle(%sql_handle_env, %sql_null_handle, henvr)
odbc functions never return database data through return values, but rather through function
parameters. note that before this call in pbmain() henvr was dimensioned as a local dword variable
but was not initialized before its use in this function, other than its default initialization to
zero by the compiler. the reason for this is that after the function call, if the function call
succeeds, henvr will contain a valid odbc handle to an environment. in terms of actual return
values, odbc functions return success/failure codes, as can be observed in pbmain() above where you
can see i tested for %sql_success. %sql_success is defined in the equates above as zero, and can be
thought of a zero errors.
armed with a valid henvr we can set the odbc version through...
call sqlsetenvattr(henvr, %sql_attr_odbc_version, byval %sql_ov_odbc3, %sql_is_integer)
after that we're ready to get down to business. at this point i'd recommend you obtain
microsoft's actual documentation for sqldrivers() as we are ready to call it. there are over a
thousand pages of documentation on the odbc api but the documentation on sqldrivers() is only a few
pages or so and is located at...
http://msdn2.microsoft.com/en-gb/library/ms712400.aspx
referring either to that microsoft documentation, or to the powerbasic declare above in
odbcincs.inc, you can see the first parameter is the handle to the environment we obtained above.
the second parameter is an equate equaling either 1 or 2 depending on which way you want to move
through the database driver information. the %sql_fetch_next is most useful and we'll use it here.
after that are six more complicated looking parameters but in actuality the six refer to only two
pieces of real data, and they are the character string driver description name (szdriverdes) such
as 'sql server', and the driver attribute/value pairs (szdriverattributes) as seen and described
above.
to set up the function call the programmer has to provide memory owned by the program into
which the function can place the driver and attribute data. programmers often refer to such memory
as buffers. note in pbmain() the variable declarations...
local szdriverdes as asciiz*64, szdriverattr as asciiz*256
those variables are the buffers into which sqldrivers() will repeatedly place driver
description and attribute/value information until the list of database information is exhausted.
i can't overemphasize enough that these are output parameters meaning that information is not fed
to the function through them but rather extracted from it by the programmer and his/her app. if
you examine the microsoft documentation you will see in the extensive write up on the function that
they clearly mark each parameter as either an input parameter, an output parameter, or an
input/output parameter (yes, you can have those too! but in sqldrivers() they are either/or).
another point that just occurred to me is that if you are coming from a programming background
limited to the basic family of languages, the whole idea of a string data type such as asciiz and
the idea of creating a 'buffer owned by the application' might seem peculiar to you. what is going
on here is actually fairly profound, and consists of the fact that in the entire world of
computerdom, there are two entirely different and opposing ways of representing strings (i fear
this is going to be a digression!).
let me give an example. suppose in a powerbasic program you wrote you had a procedure where
you needed an integer long and a string like so:
local inumber as long
local strcompanyname as string
and then you assigned data to the variables like this...
inumber = 17
strcompanyname = "acme warehousing company, inc"
the steps the compiler must take to compile the statement with the integer are considerably
different than the steps it must take with the string. in the case of the long the compiler must
reserve a four byte piece of memory to store the '17', then move the binary representation of '17'
to that location. in the case of the string the compiler must generate code to first measure the
string, then do an api call to perform a memory allocation for the length of the string, after that
create a string descriptor that holds information on the string such as its length, and finally
actually move the bytes of the string's characters to the buffer it allocated to hold the string.
and this all occurs transparently to you the programmer.
when you enter the world of the windows api or the odbc api you are entering a world where
string handling doesn't work that way. in fact, if you even search for the word 'string' in the
documentation for any of the api functions that involve what we think of as string data you will be
hard pressed to find it (in sqldrivers() i found the word string exactly one time). you will
constantly see references instead to 'buffers' and 'pointer to a buffer'. the reason for this is
also fairly profound and far reaching. in the assembler and c code in which windows was written, in
the assembler and c code in which many operating systems were written, there is no string data type.
when c++ eventually came on the scene after both unix and windows were already written a c++ string
data type was created using c++ classes along the lines of basic implementations. in later
additions to the windows api you will find other kinds of 'strings' similiar to basic strings, but
not in the original libraries dating from the early days of windows.
digression over and back to sqldrivers(). below is a simplified version of the program in
which there is only one call to the sqldrivers() function, as i eliminated its placement within the
while loop construct. what we'll do is simply call the function one time after setting up a few
necessary parameters, and then we'll examine in detail what data was placed into the output
parameters by the single function call. so copy the following code to a new editor window, but
make sure both includes are accessable to the program from wherever on your computer it is running.
remember to please place those odbc includes into the same directory as the source here. i'm
speaking of the eight equates and four declares from the program at the top of this post.
i have already explained what the first two parameters to sqldrivers() are. the third
parameter, szdriverdes, is that asciiz fixed length string i started to describe when i launched
myself into the digression about buffers. you should be able to figure out now what that third
parameter is, and how it was used. sqldrivers() put its first driver description into it. on your
machine it may not be sql server, but it will be a text description of some sort of driver. the
fourth parameter is 64 and i just pulled that number out of the air, as my derivation of this
function followed the steps i am outlining for you here, and i found that none of the text strings
were very long. that was an input parameter, by the way. the fifth parameter is an output
parameter and when sqldrivers() was called it returned to us the length of the text string output in
the szdriverdes buffer. as you can see (and count), 'sql server' contains 10 characters and
sqldrivers() informs us that it is returning 10 characters to us. so far so good.
now, however, we get into trouble. the pattern and meaning of the next three parameters is
exactly the same as for szdriverdes, and indeed we do get a string with an attribute/value pair, but
we only seem to get the first one, and not the others. and the numbers definitely don't seem to
match as they did for szdriverdes. the full set of information for the sql server driver from the
first program was like so:
usagecount=5
sqllevel=1
fileusage=0
driverodbcver=02.50
connectfunctions=yyy
apilevel=2
cptimeout=60
it seems we only got the first line. the 'usagecount=5' certainly equals 12 characters, but
according to the ilen2 output parameter of sqldrivers(), 101 characters of information were returned
to us. if you read the 'comments' in microsoft's sqldrivers() documentation you'll find the
following:
each [attribute/value] pair is terminated with a null byte, and the entire list
is terminated with a null byte (that is, two null bytes mark the end of the list).
for example, a file-based driver using c syntax might return the following list of
attributes:
("\0" represents a null character):
fileusage=1\0fileextns=*.dbf\0\0
those null bytes after the end of each attribute/value pair is fooling powerbasic into thinking
it hit the end of the string after hitting a null byte after 'usagecount=5' (actually, that isn't
exactly right. since a null byte marks the end of a string, that first null byte was in fact the end
of the string). it isn't seeing those additional 89 bytes after that null. we own those bytes. they
are saftly in 'our' buffer. but how do we get to them?
about the fastest and easiest way to fix it is to set a pointer equal to the address of the first
byte of the string, then loop through the string one byte at a time for 101 bytes (the count returned
to us in ilen2, remember) testing all the way for null bytes. when a null byte is found, just stick
some other less troublesome character in its place. in terms of just what particular character to
stick there, just remember that all versions of basic love commas as seperators. there's just nothing
basic loves more than seeing commas seperating bits of data. now c, that language has a fondness for
white space, i.e., tabs, spaces, anything like that. but for basic, commas do it every time. so here
is the next version of the program. try this...
i believe we're making progress. now we've reached the point where we have all the
information for one database driver. we have its name and the attribute/value pairs that
describe it. all we need to do now is format the data into a readily readable list. the easiest
way to do that is to use powerbasic's great parsecount and parse combination. if you've never used
these you are in for a treat as they are wonderful and easy to use. parsecount returns the number
of delimited fields in a string. if the delimiter is the comma all you have to do is call the
function like so:
inumberfields = parsecount(szdriverattr)
for the example above inumberfields would be equal to seven. the parse statement can then be
used to load a dynamically dimensioned array with the individual attribute/value pairs. so the
sequence of operations is to dimension an array with parsecount-1 elements (parse starts filling the
array at element zero, so we're back to reconciling zero based and one based counts), then execute
the parse statement passing it first the string to be parsed and second the correctly dimensioned
string array into which it will place the parsed strings. so now try this next iteration of the
program...
at this point i don't believe i need to provide any further examples as we've come full circle.
the next version of the program is the first program at the top of this post where sqldrivers() is
called repeatedly inside a while loop with the exit condition being when there is no more data to
retrieve marked by sqldrivers() returning %sql_no_data.
this program shows a particularly involved api function, and if you were able to follow along
and understand it, you will easily be able to master the api functions required to create a graphical
interfaces in windows. probably the hardest issues for programmers whose programming background is
limited to basic are the issues relating to character string buffers, pointers to character string
buffers, and null terminated asciiz strings. some insight into these issues can be obtained by
carefully reading the powerbasic documentation on all the string data types and pointers.
[this message has been edited by fred harris (edited january 08, 2007).]
the source code forum...
http://www.powerbasic.com/support/pb...ad.php?t=25174
i used the odbc sqldrivers() function to amass database driver information to load a text box
that covers the entire extent of a display form/dialog. the example there was somewhat elementary,
except for the odbc sqldrivers() call, which is rather involved. it occurred to me that the flow
of the series itself and my attempts at providing clear explanations would be best served if i
posted the sqldrivers() explanations in a seperate post not part of the 'beginning ...' series.
that way for learners who are just interested in the mechanics of getting multiple dialog projects
running, or for those who are just interested in seeing how to do simple scrollable information
display, the complexities of the sqldrivers() function could be relegated to that of a 'black box',
out of which information faithfully if not somewhat mysteriously flowed. the comment thread for the
sdk series is at...
http://www.powerbasic.com/support/pb...ad.php?t=14429
...and comments on this thread could also go there. so in this seperate post in a different
thread i'll attempt to give a blow by blow and detailed explanation for the sqldrivers() function
for those who are interested. below is the full code for an example that should run for both the
console compiler and pb windows (uses a display macro). note that i abstracted from the odbc
includes at:
http://www.powerbasic.com/files/pub/...e/pbodbc30.zip
the eight equates and four functions necessary to make this example work. sqldrivers() is in
odbc32.dll, so this setup is necessary for the program to run. in this first and full
program they are listed right after the win32api.inc include. in the explanatory programs after-
wards i didn't want to include them in every program so as to save space, so i am asking you to
copy them to an include file named odbcincs.inc. you'll note that include in the programs after
this one.
Code:
''sqldrivers.bas 'tested with cc4.01,4.02,4.03,8.01,8.02,8.03
#compile exe
#register none
#dim all
#include "win32api.inc" 'main windows include file.
'
'save to odbcincs.inc. for programs after this one. these equates and declares are from
'sqltypes.inc, sql32.inc and sqlext32.inc.
%sql_null_handle = 0&
%sql_handle_env = 1
%sql_attr_odbc_version = 200
%sql_ov_odbc3 = 3???
%sql_is_integer = (-6)
%sql_fetch_next = 1
%sql_no_data = 100
%sql_error = -1
'
declare function sqlallochandle lib "odbc32.dll" alias "sqlallochandle" _
( _
byval handletype as integer,_
byval inputhandle as dword,_
byref outputhandle as dword _
) as integer
'
declare function sqlsetenvattr lib "odbc32.dll" alias "sqlsetenvattr" _
( _
byval environmenthandle as dword,_
byval attribute as long,_
byref value as any,_
byval stringlength as long _
) as integer
'
declare function sqldrivers lib "odbc32.dll" alias "sqldrivers" _
( _
byval henv as dword,_
byval fdirection as word,_
byref szdriverdesc as asciiz,_
byval cbdriverdescmax as integer,_
byref pcbdriverdesc as integer,_
byref szdriverattributes as asciiz,_
byval cbdrvrattrmax as integer,_
byref pcbdrvrattr as integer _
) as integer
'
declare function sqlfreehandle lib "odbc32.dll" alias "sqlfreehandle" _
( _
byval handletype as integer,_
byval thehandle as dword _
) as integer
'end of odbcincs.inc.
'
macro display(msg,fp) 'if program is running in console compiler, output goes to stdout of
#if %def(%pb_cc32) 'console window.
stdout msg
#else
print #fp, msg 'otherwise, to a file which will be shellexecut()'ed at program termination.
#endif
end macro
'
function pbmain() as long
local ilen1 as integer, ilen2 as integer, fn as integer
local szdriverdes as asciiz*64, szdriverattr as asciiz*256
local sztextfile as asciiz*256, szcurdir as asciiz*256
local ptrbyte as byte ptr
local strarr() as string
local henvr as dword
register i as long
'
#if %def(%pb_cc32)
console set screen 400, 80
#else
sztextfile=curdir$ & "\" & "output.txt"
fn=freefile
open sztextfile for output as #fn
#endif
if sqlallochandle(%sql_handle_env,%sql_null_handle,henvr)<>%sql_error then
call sqlsetenvattr(henvr,%sql_attr_odbc_version,byval %sql_ov_odbc3,%sql_is_integer)
while sqldrivers(henvr,%sql_fetch_next,szdriverdes,64,ilen1,szdriverattr,256,ilen2)<>%sql_no_data
display(szdriverdes,fn) : display(chr$(13),fn)
ptrbyte=varptr(szdriverattr) 'get address of start of buffer containing attribute/value pairs.
decr ilen2 'convert from one based count to zero based count, then move
for i=0 to ilen2 'through buffer converting any null bytes encountered to commas.
if @ptrbyte[i]=0 then 'null bytes are 0 and commas are chr$(44), or just 44 here. this
@ptrbyte[i]=44 'technique uses a base pointer and pointer arithmetic to do its job.
end if 'a double null byte sequence exists at end of characters in buffer,
next i 'one of which was included in count in ilen2. this would have been
@ptrbyte[ilen2]=0 'converted to a trailing comma by the code above, so lets remove it.
redim strarr(parsecount(szdriverattr)-1)
parse szdriverattr,strarr()
for i=0 to ubound(strarr,1)
display(strarr(i),fn)
next i
display(chr$(13,10) & chr$(13),fn)
loop
call sqlfreehandle(%sql_handle_env,henvr)
else
display("odbc function call failure. couldn't allocate environment handle!",fn)
end if
#if %def(%pb_cc32)
waitkey$
#else
close #fn
szcurdir=curdir$
call shellexecute(0,"open",sztextfile,byval 0,szcurdir,%sw_showdefault)
#endif
'
pbmain=0
end function
such as, 'sql server' or 'microsoft access driver (*.mdb)', followed by a blank line and half a
dozen or so attribute/value pairs. on my system the first driver for which information is provided
is the sql server driver like so...
sql server
usagecount=5
sqllevel=1
fileusage=0
driverodbcver=02.50
connectfunctions=yyy
apilevel=2
cptimeout=60
and this format of data is repeated for all drivers on the system on which the program is
running. now the thing is, this format of the data was created by the program, as it makes sense
to view it in that manner. sqldrivers() does not present it in that format. the format
sqldrivers() presents the data in is one where a string buffer provided by the program for the
driver description is filled with that description, and another buffer provided for the
attribute/value pairs is filled with that information - all in one asciiz character string buffer
with unchanging address and each attribute/value pair seperated by a null byte. in addition, the
odbc functions always return in a seperate buffer the numbers of bytes returned in adjacent
character string buffers. our work in this example will be to use all the information provided by
sqldrivers() so as to format it into the readily readable format that you see when you run the
program.
so lets begin. the first thing you always have to do when using odbc functions is set up an
odbc environment - sometimes called a 'workspace' in other contexts, and set the odbc version. a
handle to an odbc environment is first obtained by calling the following function:
sqlallochandle(%sql_handle_env, %sql_null_handle, henvr)
odbc functions never return database data through return values, but rather through function
parameters. note that before this call in pbmain() henvr was dimensioned as a local dword variable
but was not initialized before its use in this function, other than its default initialization to
zero by the compiler. the reason for this is that after the function call, if the function call
succeeds, henvr will contain a valid odbc handle to an environment. in terms of actual return
values, odbc functions return success/failure codes, as can be observed in pbmain() above where you
can see i tested for %sql_success. %sql_success is defined in the equates above as zero, and can be
thought of a zero errors.
armed with a valid henvr we can set the odbc version through...
call sqlsetenvattr(henvr, %sql_attr_odbc_version, byval %sql_ov_odbc3, %sql_is_integer)
after that we're ready to get down to business. at this point i'd recommend you obtain
microsoft's actual documentation for sqldrivers() as we are ready to call it. there are over a
thousand pages of documentation on the odbc api but the documentation on sqldrivers() is only a few
pages or so and is located at...
http://msdn2.microsoft.com/en-gb/library/ms712400.aspx
referring either to that microsoft documentation, or to the powerbasic declare above in
odbcincs.inc, you can see the first parameter is the handle to the environment we obtained above.
the second parameter is an equate equaling either 1 or 2 depending on which way you want to move
through the database driver information. the %sql_fetch_next is most useful and we'll use it here.
after that are six more complicated looking parameters but in actuality the six refer to only two
pieces of real data, and they are the character string driver description name (szdriverdes) such
as 'sql server', and the driver attribute/value pairs (szdriverattributes) as seen and described
above.
to set up the function call the programmer has to provide memory owned by the program into
which the function can place the driver and attribute data. programmers often refer to such memory
as buffers. note in pbmain() the variable declarations...
local szdriverdes as asciiz*64, szdriverattr as asciiz*256
those variables are the buffers into which sqldrivers() will repeatedly place driver
description and attribute/value information until the list of database information is exhausted.
i can't overemphasize enough that these are output parameters meaning that information is not fed
to the function through them but rather extracted from it by the programmer and his/her app. if
you examine the microsoft documentation you will see in the extensive write up on the function that
they clearly mark each parameter as either an input parameter, an output parameter, or an
input/output parameter (yes, you can have those too! but in sqldrivers() they are either/or).
another point that just occurred to me is that if you are coming from a programming background
limited to the basic family of languages, the whole idea of a string data type such as asciiz and
the idea of creating a 'buffer owned by the application' might seem peculiar to you. what is going
on here is actually fairly profound, and consists of the fact that in the entire world of
computerdom, there are two entirely different and opposing ways of representing strings (i fear
this is going to be a digression!).
let me give an example. suppose in a powerbasic program you wrote you had a procedure where
you needed an integer long and a string like so:
local inumber as long
local strcompanyname as string
and then you assigned data to the variables like this...
inumber = 17
strcompanyname = "acme warehousing company, inc"
the steps the compiler must take to compile the statement with the integer are considerably
different than the steps it must take with the string. in the case of the long the compiler must
reserve a four byte piece of memory to store the '17', then move the binary representation of '17'
to that location. in the case of the string the compiler must generate code to first measure the
string, then do an api call to perform a memory allocation for the length of the string, after that
create a string descriptor that holds information on the string such as its length, and finally
actually move the bytes of the string's characters to the buffer it allocated to hold the string.
and this all occurs transparently to you the programmer.
when you enter the world of the windows api or the odbc api you are entering a world where
string handling doesn't work that way. in fact, if you even search for the word 'string' in the
documentation for any of the api functions that involve what we think of as string data you will be
hard pressed to find it (in sqldrivers() i found the word string exactly one time). you will
constantly see references instead to 'buffers' and 'pointer to a buffer'. the reason for this is
also fairly profound and far reaching. in the assembler and c code in which windows was written, in
the assembler and c code in which many operating systems were written, there is no string data type.
when c++ eventually came on the scene after both unix and windows were already written a c++ string
data type was created using c++ classes along the lines of basic implementations. in later
additions to the windows api you will find other kinds of 'strings' similiar to basic strings, but
not in the original libraries dating from the early days of windows.
digression over and back to sqldrivers(). below is a simplified version of the program in
which there is only one call to the sqldrivers() function, as i eliminated its placement within the
while loop construct. what we'll do is simply call the function one time after setting up a few
necessary parameters, and then we'll examine in detail what data was placed into the output
parameters by the single function call. so copy the following code to a new editor window, but
make sure both includes are accessable to the program from wherever on your computer it is running.
remember to please place those odbc includes into the same directory as the source here. i'm
speaking of the eight equates and four declares from the program at the top of this post.
Code:
'sqldrivers.bas
#compile exe
#register none
#dim all
#include "win32api.inc" 'main windows include file.
#include "odbcincs.inc" 'highly limited abstract from sqltypes.inc, sql32.inc, and sqlext32.inc
'
macro display(msg,fp) 'if program is running in console compiler, output goes to stdout of
#if %def(%pb_cc32) 'console window.
stdout msg
#else
print #fp, msg 'otherwise, to a file which will be shellexecut()'ed at program termination.
#endif
end macro
'
function pbmain() as long
local ilen1 as integer, ilen2 as integer, fn as integer
local szdriverdes as asciiz*64, szdriverattr as asciiz*256
local sztextfile as asciiz*256, szcurdir as asciiz*256
local ptrbyte as byte ptr
local strarr() as string
local henvr as dword
register i as long
'
#if %def(%pb_cc32)
console set screen 400, 80
#else
sztextfile=curdir$ & "\" & "output.txt"
fn=freefile
open sztextfile for output as #fn
#endif
if sqlallochandle(%sql_handle_env,%sql_null_handle,henvr)<>%sql_error then
call sqlsetenvattr(henvr,%sql_attr_odbc_version,byval %sql_ov_odbc3,%sql_is_integer)
call sqldrivers(henvr,%sql_fetch_next,szdriverdes,64,ilen1,szdriverattr,256,ilen2)
display("szdriverdes = " & szdriverdes,fn)
display("len(szdriverdes) = " & trim$(str$(len(szdriverdes))),fn)
display("ilen1 = " & trim$(str$(ilen1)),fn)
display(chr$(13),fn)
display("szdriverattr = " & szdriverattr,fn)
display("len(szdriverattr) = " & trim$(str$(len(szdriverattr))),fn)
display("ilen2 = " & trim$(str$(ilen2)),fn)
call sqlfreehandle(%sql_handle_env,henvr)
else
display("odbc function call failure. couldn't allocate environment handle!",fn)
end if
#if %def(%pb_cc32)
waitkey$
#else
close #fn
szcurdir=curdir$
call shellexecute(0,"open",sztextfile,byval 0,szcurdir,%sw_showdefault)
#endif
'
pbmain=0
end function
Code:
below is output from above program: ===================================== szdriverdes = sql server len(szdriverdes) = 10 ilen1 = 10 ' szdriverattr = usagecount=5 len(szdriverattr) = 12 ilen2 = 101
parameter, szdriverdes, is that asciiz fixed length string i started to describe when i launched
myself into the digression about buffers. you should be able to figure out now what that third
parameter is, and how it was used. sqldrivers() put its first driver description into it. on your
machine it may not be sql server, but it will be a text description of some sort of driver. the
fourth parameter is 64 and i just pulled that number out of the air, as my derivation of this
function followed the steps i am outlining for you here, and i found that none of the text strings
were very long. that was an input parameter, by the way. the fifth parameter is an output
parameter and when sqldrivers() was called it returned to us the length of the text string output in
the szdriverdes buffer. as you can see (and count), 'sql server' contains 10 characters and
sqldrivers() informs us that it is returning 10 characters to us. so far so good.
now, however, we get into trouble. the pattern and meaning of the next three parameters is
exactly the same as for szdriverdes, and indeed we do get a string with an attribute/value pair, but
we only seem to get the first one, and not the others. and the numbers definitely don't seem to
match as they did for szdriverdes. the full set of information for the sql server driver from the
first program was like so:
usagecount=5
sqllevel=1
fileusage=0
driverodbcver=02.50
connectfunctions=yyy
apilevel=2
cptimeout=60
it seems we only got the first line. the 'usagecount=5' certainly equals 12 characters, but
according to the ilen2 output parameter of sqldrivers(), 101 characters of information were returned
to us. if you read the 'comments' in microsoft's sqldrivers() documentation you'll find the
following:
each [attribute/value] pair is terminated with a null byte, and the entire list
is terminated with a null byte (that is, two null bytes mark the end of the list).
for example, a file-based driver using c syntax might return the following list of
attributes:
("\0" represents a null character):
fileusage=1\0fileextns=*.dbf\0\0
those null bytes after the end of each attribute/value pair is fooling powerbasic into thinking
it hit the end of the string after hitting a null byte after 'usagecount=5' (actually, that isn't
exactly right. since a null byte marks the end of a string, that first null byte was in fact the end
of the string). it isn't seeing those additional 89 bytes after that null. we own those bytes. they
are saftly in 'our' buffer. but how do we get to them?
about the fastest and easiest way to fix it is to set a pointer equal to the address of the first
byte of the string, then loop through the string one byte at a time for 101 bytes (the count returned
to us in ilen2, remember) testing all the way for null bytes. when a null byte is found, just stick
some other less troublesome character in its place. in terms of just what particular character to
stick there, just remember that all versions of basic love commas as seperators. there's just nothing
basic loves more than seeing commas seperating bits of data. now c, that language has a fondness for
white space, i.e., tabs, spaces, anything like that. but for basic, commas do it every time. so here
is the next version of the program. try this...
Code:
#compile exe
#register none
#dim all
#include "win32api.inc" 'main windows include file.
#include "odbcincs.inc" 'highly limited abstract from sqltypes.inc, sql32.inc, and sqlext32.inc
'
macro display(msg,fp) 'if program is running in console compiler, output goes to stdout of
#if %def(%pb_cc32) 'console window.
stdout msg
#else
print #fp, msg 'otherwise, to a file which will be shellexecut()'ed at program termination.
#endif
end macro
'
function pbmain() as long
local ilen1 as integer, ilen2 as integer, fn as integer
local szdriverdes as asciiz*64, szdriverattr as asciiz*256
local sztextfile as asciiz*256, szcurdir as asciiz*256
local ptrbyte as byte ptr
local strarr() as string
local henvr as dword
register i as long
'
#if %def(%pb_cc32)
console set screen 400, 150
#else
sztextfile=curdir$ & "\" & "output.txt"
fn=freefile
open sztextfile for output as #fn
#endif
if sqlallochandle(%sql_handle_env,%sql_null_handle,henvr)<>%sql_error then
call sqlsetenvattr(henvr,%sql_attr_odbc_version,byval %sql_ov_odbc3,%sql_is_integer)
call sqldrivers(henvr,%sql_fetch_next,szdriverdes,64,ilen1,szdriverattr,256,ilen2)
ptrbyte=varptr(szdriverattr) 'the varptr function returns the address of a variable.
decr ilen2 'so ptrbyte holds the address of the 256 byte long asciiz
for i=0 to ilen2 'string buffer. the ilen2 parameter holds the one based
if @ptrbyte[i]=0 then 'count of characters returned. i decremented it because
@ptrbyte[i]=44 'in using a base pointer you are working with zero based
end if 'offsets from a starting point. pointer subscript notation
next i 'is used to move through buffer substituting ','s for '0's.
@ptrbyte[ilen2]=0 'lets also shorten the string and remove trailing comma.
display("szdriverattr = " & szdriverattr,fn)
display("len(szdriverattr) = " & trim$(str$(len(szdriverattr))),fn)
display("ilen2 = " & trim$(str$(ilen2)),fn)
call sqlfreehandle(%sql_handle_env,henvr)
else
display("odbc function call failure. couldn't allocate environment handle!",fn)
end if
#if %def(%pb_cc32)
waitkey$
#else
close #fn
szcurdir=curdir$
call shellexecute(0,"open",sztextfile,byval 0,szcurdir,%sw_showdefault)
#endif
'
pbmain=0
end function
Code:
below is output from above program: ===================================== szdriverattr = usagecount=5,sqllevel=1,fileusage=0,driverodbcver=02.50,connectfunctions=yyy,apilevel=2,cptimeout=60 len(szdriverattr) = 100 ilen2 = 100
information for one database driver. we have its name and the attribute/value pairs that
describe it. all we need to do now is format the data into a readily readable list. the easiest
way to do that is to use powerbasic's great parsecount and parse combination. if you've never used
these you are in for a treat as they are wonderful and easy to use. parsecount returns the number
of delimited fields in a string. if the delimiter is the comma all you have to do is call the
function like so:
inumberfields = parsecount(szdriverattr)
for the example above inumberfields would be equal to seven. the parse statement can then be
used to load a dynamically dimensioned array with the individual attribute/value pairs. so the
sequence of operations is to dimension an array with parsecount-1 elements (parse starts filling the
array at element zero, so we're back to reconciling zero based and one based counts), then execute
the parse statement passing it first the string to be parsed and second the correctly dimensioned
string array into which it will place the parsed strings. so now try this next iteration of the
program...
Code:
#compile exe
#register none
#dim all
#include "win32api.inc" 'main windows include file.
#include "odbcincs.inc" 'highly limited abstract from sqltypes.inc, sql32.inc, and sqlext32.inc
'
macro display(msg,fp) 'if program is running in console compiler, output goes to stdout of
#if %def(%pb_cc32) 'console window.
stdout msg
#else
print #fp, msg 'otherwise, to a file which will be shellexecut()'ed at program termination.
#endif
end macro
'
function pbmain() as long
local ilen1 as integer, ilen2 as integer, fn as integer
local szdriverdes as asciiz*64, szdriverattr as asciiz*256
local sztextfile as asciiz*256, szcurdir as asciiz*256
local ptrbyte as byte ptr
local strarr() as string
local henvr as dword
register i as long
'
#if %def(%pb_cc32)
console set screen 400, 150
#else
sztextfile=curdir$ & "\" & "output.txt"
fn=freefile
open sztextfile for output as #fn
#endif
if sqlallochandle(%sql_handle_env,%sql_null_handle,henvr)<>%sql_error then
call sqlsetenvattr(henvr,%sql_attr_odbc_version,byval %sql_ov_odbc3,%sql_is_integer)
call sqldrivers(henvr,%sql_fetch_next,szdriverdes,64,ilen1,szdriverattr,256,ilen2)
display(szdriverdes,fn)
display(chr$(13),fn)
ptrbyte=varptr(szdriverattr) 'the varptr function returns the address of a variable.
decr ilen2 'so ptrbyte holds the address of the 256 byte long asciiz
for i=0 to ilen2 'string buffer. the ilen2 parameter holds the one based
if @ptrbyte[i]=0 then 'count of characters returned. i decremented it because
@ptrbyte[i]=44 'in using a base pointer you are working with zero based
end if 'offsets from a starting point. pointer subscript notation
next i 'is used to move through buffer substituting ','s for 0s.
@ptrbyte[ilen2]=0 'finally, insert null byte at trailing comma.
redim strarr(parsecount(szdriverattr)-1) 'for above string we need elements 0 through 6.
parse szdriverattr,strarr() 'parse will extract each attribute/value pair
for i=0 to ubound(strarr,1) 'and load strarr() starting at element zero
display(strarr(i),fn) 'and incrementing until all elements extracted.
next i 'the ubound(strarr) is parsecount-1.
display(chr$(13,10) & chr$(13),fn)
call sqlfreehandle(%sql_handle_env,henvr)
else
display("odbc function call failure. couldn't allocate environment handle!",fn)
end if
#if %def(%pb_cc32)
waitkey$
#else
close #fn
szcurdir=curdir$
call shellexecute(0,"open",sztextfile,byval 0,szcurdir,%sw_showdefault)
#endif
'
pbmain=0
end function
the next version of the program is the first program at the top of this post where sqldrivers() is
called repeatedly inside a while loop with the exit condition being when there is no more data to
retrieve marked by sqldrivers() returning %sql_no_data.
this program shows a particularly involved api function, and if you were able to follow along
and understand it, you will easily be able to master the api functions required to create a graphical
interfaces in windows. probably the hardest issues for programmers whose programming background is
limited to basic are the issues relating to character string buffers, pointers to character string
buffers, and null terminated asciiz strings. some insight into these issues can be obtained by
carefully reading the powerbasic documentation on all the string data types and pointers.
[this message has been edited by fred harris (edited january 08, 2007).]