DBF to XML Error

[rickmds]

Hi
I was reading and playing with this OLE DB script and got an error on line 65 "RS  = DB.Execute(cSQL)" and am at a bit of a loss as to how to trouble shoot this script.  I have attached a DBF file so that you can replicate.  Please note that the DBF is level 5 and that I modified the script accordingly and also inserted some displays as well.
Thanks in advance.
Rick



; Winbatch OLE DB Script
;
; Prompts user for DBF File name. Opens the file with the Jet
; 4.0 Provider, creates separate .XML files for both the Table
; Structure and the Data.
;
; DBF.ini will be created the first time the script is run (in
; the same subdir as the script) to illustrate the connection and
; the files created
;
; [Main]
; Path=C:\wbdemo\
; Table=Goods
; XML=C:\wbdemo\Goods.XML
; Schema=C:\wbdemo\GoodsSchema.XML
; Conn=Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=C:\wbdemo\;
;      Extended Properties=dBASE III;; 4. Create Column objects, append to new Table
;
; Stan Littlefield 06/29/2000
; ///////////////////////////////////////////////////////

cDbf  = AskFilename( "Select DBF To Link", DirGet(), "dBASE 5.0|*.DBF","*.DBF",1)
cPath = FilePath(cDbf)
;cDbf  = FileNameShort(cDbf)
cDBF = StrCat(cPath,cDbf)
display(5,'cDBF',cDBF)
nDbf  = StrIndexNc(cDbf,".",1,@FWDSCAN)
cDbf  = StrSub(cDbf,1,nDbf-1)

cXML  = strcat(cDbf,".XML")
cSch  = strcat(cDbf,"Schema.XML")

cDbf  = StrSub(cDbf,strlen(cPath)+1,-1)
cConn = strcat("Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=",cPath,"; Extended Properties=dBASE 5.0;")
cSQL  = strcat("SELECT * FROM ",cDbf)
display(3,'Location',cPath)

IniWritePvt("Main","Path",cPath,".\dbf.ini")
IniWritePvt("Main","Table",cDbf,".\dbf.ini")
IniWritePvt("Main","XML",cXML,".\dbf.ini")
IniWritePvt("Main","Schema",cSch,".\dbf.ini")
IniWritePvt("Main","Conn",cConn,".\dbf.ini")


IF FileExist(cXML) == @TRUE
  FileDelete(cXML)
Endif

IF FileExist(cSch) == @TRUE
  FileDelete(cSch)
Endif


cMode = IniReadPvt("Main","Mode","1",".\dbf.ini")
display(5,'cMode',cMode)


BoxOpen("Transferring %cDbf% To XML","Opening Data Source")


DB  = ObjectOpen("ADODB.Connection")

DB.Open(cConn)

RS  = DB.Execute(cSQL)

;Now Describe
BoxText("Translating RecordSet To XML")

RS.Save(cXML,1)
ObjectClose(RS)

adSchemaTables=20
RS  = DB.OpenSchema(adSchemaTables)
RS.Save(cSch,1)

ObjectClose(RS)
DB.close()
ObjectClose(DB)

BoxShut()
exit

[td]

Assuming you have the Jet OLDEB provider installed on your system and have write access to the targeted directory, Stan's old script should work.  If you have  a newer version of WinBatch,  clicking the 'More Error Info' button on the error message dialog, if enabled, should tell you exactly what the ADO COM Automation object is unhappy about.  Otherwise, step through the script in the WinBatch Studio debugger and watch the variable values as they are created.  Make sure they all contain what you think they should.

[rickmds]

Thank you Tony, I will try this and provide an update.

[stanl]

Check you ODBC drivers, I think dbase 5.0 is not part of MDAC and has to be downloaded separately. And, I recall another old script I wrote and the FoxPro drivers worked with db5 files. Might try searching the Tech DB for FoxPro.

[rickmds]

Tony & Stan
I changed the dBase file to IV and re-ran in the debugger to get try to get mote information about the error.  More detail was not provided through the WinBatch de-bugger in the attached image. I also attached thee IV level dbf
Rick

[stanl]

I haven't used dbf files in over 10 years and do not have FoxPro drivers installed. You might want to set the debugger on and send the debug file for a look see. Could be a space in the path affecting the cConn variable for the connection.

[rickmds]

Stan
Thanks for responding. Unfortunately the "More Error Information" is not available and this is all that I have for debugging.  I did check spacing and paths and all appears to be OK. 
Rick

[stanl]

Stan
Thanks for responding. Unfortunately the "More Error Information" is not available and this is all that I have for debugging.  I did check spacing and paths and all appears to be OK. 
Rick

No, there is a set debug to echo to a file. That is what I was referring to.

[td]

Unfortunately, you are using an older version of WinBatch so you don't have the benefits of better COM error reporting which would likely show up as the  "More Error Information" button being enabled.  Note that I tried Stan's old script on a Windows 10 system with the Jet 4.0 installed and it still works as advertised. 

You can check your system for the Access dBase driver by starting the ODBC applet that is in either the  Control Panel or Administrative Tools menu/window.

The easiest way to debug script is to step through it using WinBatch Studio.

[td]

In passing and while it is not causing your error the line

Code (winbatch) Select Expand

cDBF = StrCat(cPath,cDbf)
should be removed from your script as it creates a bad path+file name.

[rickmds]

Tony
Thanks for sticking with me on this. I upgraded to 16B, verified the Jet installation; I am on 64 bit version and made the suggested code change.  Here is the detail now on the error.  It appears that the error does not like line 35... "cSQL  = strcat("SELECT * FROM ",cDbf)" and cDBF is null... caused by line 33 "cDbf  = StrSub(cDbf,strlen(cPath)+1,-1)".
Thoughts?
Rick

[td]

Obviously, you are getting your error because your where cause is empty, i.e., no file name.   It is empty because you are not correctly parsing the dBase file's base name from the full path.  You can use the 'FileBaseName' function to obtain the dBase files name for the where clause instead of using convoluted string parsing.  However,  you may have other problems with your path that need to be checked.  For example, is your full path to the db greater than 259 characters?

Again the easiest way to debug this is to step through your code one line at a time (F10) and watch the variables values change in the Watch Window.   You can look at the full value of each variable by clicking on its name in the Watch Windows.  The defect should become apparent.

As an alternative you can add DebugTrace to the top of your script to get a trace file.  See the DebugTrace documentation in the Consolidated WIL Help file for details.

[td]

This simplified version (sans any input checks) works on my Windows 10 machine:

Code (winbatch) Select Expand

cDbf  = AskFilename( "Select DBF To Link", DirGet(), "dBASE 5.0|*.DBF","*.DBF",1)
cPath = FilePath(cDbf)
cXML  = cPath: FileRoot(cDbf):".XML"
cSch  = cPath:FileRoot(cDbf):"Schema.XML"
cDbf  = FileBaseName(cDbf)
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=":cPath:"; Extended Properties=dBASE 5.0;"
cSQL  = "SELECT * FROM ":cDbf

IniWritePvt("Main","Path",cPath,".\dbf.ini")
IniWritePvt("Main","Table",cDbf,".\dbf.ini")
IniWritePvt("Main","XML",cXML,".\dbf.ini")
IniWritePvt("Main","Schema",cSch,".\dbf.ini")
IniWritePvt("Main","Conn",cConn,".\dbf.ini")

IF FileExist(cXML) == @TRUE
  FileDelete(cXML)
Endif
IF FileExist(cSch) == @TRUE
  FileDelete(cSch)
Endif

cMode = IniReadPvt("Main","Mode","1",".\dbf.ini")

DB  = ObjectOpen("ADODB.Connection")
DB.Open(cConn)
RS  = DB.Execute(cSQL)

RS.Save(cXML,1)

RS = 0

adSchemaTables=20
RS  = DB.OpenSchema(adSchemaTables)
RS.Save(cSch,1)

RS = 0
DB.close()
DB = 0

exit

[rickmds]

Tony
Thank you very much for providing this; works great on my side as well!
Rick

[td]

The danger of cut-and-paste scripting is that it is too easy to skip the part about understanding what the borrowed  script is actually doing.