First, if someone could forward this to an ODBC news group I would
be very thankfull.  I have very limited USNET access.

Okay, I am trying to write a function to export
records to an ODBC database from VB.  
Here is what I have (basically)

first I create the SQL statment, something like this:
"insert into table1 (col1, col2) values (?, ?)"

I then call SQLPrepare and SQLSetParam with a
cbValue of SQL_DATA_AT_EXEC and then SQLExecute.

My problem is on the SQLPutData.  All the datatypes
work except for char.  Here is the stripped code.

Dim tmp_char As String
Dim tmpField As Variant
Dim rc As Integer
Dim SQLString As String
Dim cbValue As Long
Dim token As Variant

rc = SQLPrepare(ehstmt, SQLString, Len(SQLString))
rc = SQLSetParam(ehstmt, Y, SQL_C_CHAR, SQL_CHAR, colStore(Y, 2), 0,
Null, cbValue)
' colStore(Y,2) is the length of the field.
cbValue = SQL_DATA_AT_EXEC
rc = SQLExecute(ehstmt)
rc = SQLParamData(ehstmt, token)
tmp_char = tmpField
rc = SQLPutData(ehstmt, tmp_char, SQL_NTS)
rc = SQLParamData(ehstmt, token)

The row is added, but there is garbage in the field.

I have tried everything I can think of.  Like using SQL_NTS for
colStore(Y,2) and
via-versa.  I tried using hardcoded values for the length.  I tried
using a Variant
for tmp_char and an array of chars.  I even tried passing the location
of tmp_char.
I have tried SQL_C_DEFAULT for SQL_C_CHAR and SQL_VARCHAR for both.  I
also tried
adding a chr(0) to the end of tmp_char.  Still garbage every time.

Like I said, the other datatypes work, what is wrong with char!

Help!

Steven

--

Texas Department of Health,                      home (512) 453-2317
WIC Automation                              work (512) 458-7111x3476
http://www.*-*-*.com/ ~sdrinovs                 pager (512) 403-8453
My opinions are my own and by no means represent the state of Texas.

On Wed, 07 May 1997 10:09:57 -0500, Steven Drinovsky

It may be that you need to convert the string into a byte array. You
should also use SQLBindParameter (ODBC 2.0) instead of SQLSetParam. In
this case, SQLPutData is not needed unless you try to put a BLOb in
the database.

Something like this may help:

Public Sub StringToBytes(Data As String, byteLen As Long,
return_buffer() As Byte)

    Dim strlen As Long, count As Long

    For count = 0 To Len(Data) - 1
    return_buffer(count) = Asc(Mid(Data, count + 1, 1))
    Next count
    For count = Len(Data) To byteLen
    return_buffer(count) = 0
    Next count

End Sub

... same thing but in the another direction...

Public Function BytesToString(ByRef byte_array() As Byte) As String
Dim Data As String, strlen As String
    Data = StrConv(byte_array(), vbUnicode)
    strlen = InStr(Data, Chr(0)) - 1
    BytesToString = Left(Data, strlen)
End Function

The following example shows, how to use this stuff:
...
    strlen = Len(dboc.package_id)
    Call StringToBytes(dboc.package_id, strlen, tmpstr)
    ok = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, _
        SQL_CHAR, strlen, 0, tmpstr(0), 0, ByVal lenbuf)
...

further questions.

Timo