Hi,
I would need to read data from and also write data to DBF files. I’m using ODBCConnection without problems. I see all tables, fields in tables.
ODBCQuery class SQL method does not work with the DBF files, so I do not know how to query the files, get the result and write back the recaltulated data.
Thanks for help.
Etyien
Subject: query dbf files using LSX
You should set up a DSN using the dBase driver if you have not already. Then call odbcconnection.connectto(“your dsn here”). An example:
Dim sources As Variant
Dim tables As Variant
Dim p As Variant
Dim rpt As Variant
Dim ind As Variant
Dim uidb As notesuidatabase
Dim db As notesdatabase
Dim con As odbcconnection
Dim query As odbcquery
Dim results As odbcresultset
Dim ws As notesuiworkspace
Dim uiv As notesuiview
Dim view As notesview
Dim dc As notesdocumentcollection
Dim documents() As notesdocument
Dim doc As notesdocument
Dim item As notesitem
Dim vals() As String
Dim title As String
Dim prompt As String
Dim tmp As String
Dim d As Integer
Dim flag As Integer
Dim table_is_new As Integer
Set ws = New notesuiworkspace
Set uidb = ws.currentdatabase
Set db = uidb.database
Set uiv = ws.currentview
Set view = uiv.view
Set doc = view.getFirstDocument
Set con = New odbcconnection
’ ***************************************************************************************
’ Check that the required ODBC data source exists.
’ ***************************************************************************************
sources = con.listdatasources
ind = Arraygetindex(sources, CB_SOURCE, 5)
If Isnull(ind) Then
Msgbox "The required ODBC data source is not installed. "+_
"Please run the 'Printing setup...' wizard.",48,"Export"
Exit Sub
End If
’ ***************************************************************************************
’ Check that we can connect to the data source.
’ ***************************************************************************************
Print “Connecting…”
Call con.connectto(CB_SOURCE)
If Not con.isconnected Then
Msgbox "Unable to export data (unable to connect to data source)."+_
"You may not be logged into the network.",48,"Export"
Print ""
Exit Sub
End If
Print “”
Set query = New odbcquery
Set query.connection = con
Set results = New odbcresultset
Set results.query = query
’ ********************************************************************************
’ Determine if the required table is present. Insert it if necessary.
’ ********************************************************************************
table_is_new = False
tables = con.ListTables( CB_SOURCE )
ind = Arraygetindex(tables, CB_TABLE, 5)
If Isnull(ind) Then
Print "setting up external table..."
' ***************************************************************************
' This variable allows us to skip the step of checking for existing
' data (below). We set it to True in cases where the table did not
' exist and there can be no existing data.
' ***************************************************************************
table_is_new = True
query.sql = "CREATE TABLE " + CB_TABLE + " ("+_
"ClientCode CHAR(6), "+_
"ClientName CHAR(50), "+_
"ItemSeq CHAR(6), "+_
"Project CHAR(75), "+_
"Lob CHAR(4), "+_
"SubLob CHAR(4), "+_
"RecordType CHAR(2), "+_
"RTDesc CHAR(50), "+_
"Location CHAR(20), "+_
"Volume CHAR(3), "+_
"Barcode CHAR(16), "+_
"Year CHAR(2), "+_
"Printed CHAR(1))"
flag = results.execute
Print ""
If Not flag Then
Msgbox "Unable to export data (failed to create the external table).",48,"Export"
Call con.disconnect
Exit Sub
End If
End If
’ ********************************************************************************
’ Check if there is data in the dbf table already, and whether the user
’ wants to overwrite it etc.
’ ********************************************************************************
If Not (table_is_new) Then
Print "Checking for existing data... please wait"
query.sql = "SELECT * from "+CB_TABLE
Call results.execute
If results.isresultsetavailable Then
Redim vals(1) As String
vals(0)=REPLACE_DATA ' Constant. See button Declarations event.
vals(1)=KEEP_DATA ' Constant. See button Declarations event.
title = "Print Queue"
prompt = "There is already data in ColorBar. What do you want to do?"
p = ws.Prompt( PROMPT_OKCANCELLIST, title, prompt, vals(0), vals )
If p="" Then Exit Sub
Select Case p
Case REPLACE_DATA
query.sql = "DELETE from "+CB_TABLE
flag = results.execute
If Not flag Then
Msgbox "Unable to remove the existing data.",48,"Export"
Print ""
Call con.disconnect
Exit Sub
End If
Case KEEP_DATA
' do nothing
End Select
End If
Print ""
End If
’ ********************************************************************************
’ Push the data into the dbf file. Note that we first do a SELECT *
’ on the table - even though there may be no records in it (see above).
’ This is to assure that the .addrow() method, which we call next, has
’ a valid rowset to act on.
’ ********************************************************************************
Print “Preparing to write records…”
query.sql = "SELECT * from "+CB_TABLE
Call results.execute
Print “”
Do Until doc Is Nothing
With results
Call .addrow
Set item = doc.getfirstitem("ClientCode")
If Not item Is Nothing Then
tmp = Left$(item.text,6)
flag = .setValue("ClientCode", tmp)
End If
flag = .updaterow
If Not flag Then
Msgbox "Failed to update a row in the external table."+Chr$(10)+Chr$(10)+_
"details:"+Chr$(10)+_
"resultset: "+Cstr(results.getError)+" "+results.getExtendedErrorMessage(results.getError)+Chr$(10)+_
"query: "+Cstr(query.getError)+" "+query.getExtendedErrorMessage(query.getError)+Chr$(10)+_
"connection: "+Cstr(con.getError)+" "+con.getExtendedErrorMessage(con.getError),+_
48,"Export"
Call con.disconnect
Exit Sub
End If
End With
Set doc = view.getnextdocument(doc)
Loop
If con.isconnected Then Call con.disconnect
Subject: RE: query dbf files using LSX
Hi Thomas,
Thanks for the accurate codes. I am just started using ODBC in Lotus, so I might be wrong, but I basicly wrote the same code (with less error handling). The DSN is in the ODBC.INI, I can connect to the DBF files. Using con.ListFields I get the fields of the tables.
I’m trying to use the following codes:
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
qry.sql=“SELECT * FROM” +TABLENAME
result.Execute
If result.IsResultSetAvailable Then
…
Else
Msgbox “NULL result”
End if
The DBF file is not empty, but I get the msg “NULL result”. So it says to me that the problem is with SQL. Is it possible to use SQL with DBF? I thought so. So should I install additional programs or drivers?
Thanks for help.
Etyien