Code for Creating ADO XML Persisted recordset

Thad,

I wanted to ask a question about ADO Parameters in Visual Basic 6.
When I work with a recordset created by CreateParameter method I cannot
delete a record in that recordset. Right now the program has to go through
all the records and check each record to see if it matches the criteria.
With parameters it is lot faster but doesn't wont to delete a record.

Mike Tonoyan / MIS Manager
All American Products Co.
1135 Aviation Place
San Fernando, CA 91340
USA
Tel.: 818-361-0059 Ext.: 246
Fax 818-898-2236
E-mail: miket@...
Web: www.allamericanproducts.com


----- Original Message -----
From: "Thad Jacobs" <tjacobs@...>
To: <vantage@yahoogroups.com>
Sent: Thursday, May 23, 2002 1:47 PM
Subject: RE: [Vantage] Code for Creating ADO XML Persisted recordset


I also neglected to mention that I've been doing a bit of R&D with Visual
Studio .NET. Once that becomes my primary development environment (when we
get our new vantage and intranet servers) having XML-Based exports will be a
huge plus.

I plan on setting things up so that I can launch my progress exports from
ASP pages, read the XML files, and load them into Crystal .NET reports.

I would use Access databases, but I wanted to allow multiple users to have
multiple instances of the same report (such as a bom) loaded at the same
time. Facilitating that in a multi-user environment required writing code
to copy blank database templates to the user's machine, and writing
additional code to accomodate for multiple linked csv files at the same
time.

Keeping the data encapsulated in One XML File, instead of an Access MDB and
a linked CSV file makes the system alot easier for me to manage.

However, unless you are willing to do a lot more coding than you already
are, there's not much of a need to make the jump to XML quite yet.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...




-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:44 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thanks for the info, Thad. On a somewhat related note: I did get the ADO
connection in Crystal to recognize my .csv exports properly. However, I
found ADO to be quite a bit slower than connecting the .csv files to reports
through Access. It's a bit harder to set up in Access, but once set up, it
reads the records MUCH faster (on large .csv files it's noticable anyway).
Also, with Access you can manually select the data type if it doesn't
interpret correctly by itself. For instance, when using an Outer-Join in
the export, if a record doesn't exist Progress puts a "?" in the field.
This is fine, but if that happens in numeric fields then Access thinks the
field is a text field. Manually setting it to Integer or Double will allow
Crystal to correctly find it as a number.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 3:28 PM
Subject: RE: [Vantage] Code for Creating ADO XML Persisted recordset


For me, XML is an alternative to CSV exports.

I needed an alternative, because CSV doesn't hold any schema information
(as
in the size of the fields, their datatypes, etc...)

Of course, the Jet/Access drivers can import a csv file and can guess(with
fairly good accuracy) the datatype of the csv column, based on the
characters it contains. However, on one of my vendor transaction reports,
datatypes were being interpreted inconsistently by the Jet drivers.

For programmers using ADO (Advanced Data Objects), this XML format is nice
because you don't have to use an ODBC or OLEDB driver to access the data,
thus improving reliability and performance.

However, Crystal reports doesn't natively support XML files until version
8.5.

XML is of minimal value to most vantage users, but for a few of us
programmers who need to write progress exports to use in external
applications (such as crystal reports), XML is definitely a viable option.

An excerpt from:
http://www.xmlfiles.com/xml/

"XML can be used to exchange data:
In the real world, computer systems and databases contain data in
incompatible formats. One of the most time consuming challenges for
developers has been to exchange data between such systems....Converting
the
data to XML can greatly reduce this complexity and create data that can be
read by different types of applications."


All that said, CSV exports still take less code to write, and are still my
method of choice in some instances.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thad,
Lots of questions I could ask, but starting with: What's an XML file
and
why would I want to use it?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'Scandale, Matt' ; 'api@...' ; 'peg@...' ;
'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 1:36 PM
Subject: [Vantage] Code for Creating ADO XML Persisted recordset


Here's some code for creating an XML file from progress. The format
used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a
viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML
file.

The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted
recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/

Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Here's some code for creating an XML file from progress. The format used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML file.
The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/
Thad,
Lots of questions I could ask, but starting with: What's an XML file and why would I want to use it?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'Scandale, Matt' ; 'api@...' ; 'peg@...' ; 'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 1:36 PM
Subject: [Vantage] Code for Creating ADO XML Persisted recordset


Here's some code for creating an XML file from progress. The format used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML file.
The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/

Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]
For me, XML is an alternative to CSV exports.

I needed an alternative, because CSV doesn't hold any schema information (as
in the size of the fields, their datatypes, etc...)

Of course, the Jet/Access drivers can import a csv file and can guess(with
fairly good accuracy) the datatype of the csv column, based on the
characters it contains. However, on one of my vendor transaction reports,
datatypes were being interpreted inconsistently by the Jet drivers.

For programmers using ADO (Advanced Data Objects), this XML format is nice
because you don't have to use an ODBC or OLEDB driver to access the data,
thus improving reliability and performance.

However, Crystal reports doesn't natively support XML files until version
8.5.

XML is of minimal value to most vantage users, but for a few of us
programmers who need to write progress exports to use in external
applications (such as crystal reports), XML is definitely a viable option.

An excerpt from:
http://www.xmlfiles.com/xml/

"XML can be used to exchange data:
In the real world, computer systems and databases contain data in
incompatible formats. One of the most time consuming challenges for
developers has been to exchange data between such systems....Converting the
data to XML can greatly reduce this complexity and create data that can be
read by different types of applications."


All that said, CSV exports still take less code to write, and are still my
method of choice in some instances.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thad,
Lots of questions I could ask, but starting with: What's an XML file and
why would I want to use it?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'Scandale, Matt' ; 'api@...' ; 'peg@...' ;
'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 1:36 PM
Subject: [Vantage] Code for Creating ADO XML Persisted recordset


Here's some code for creating an XML file from progress. The format used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML file.

The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted
recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/

Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Thanks for the info, Thad. On a somewhat related note: I did get the ADO connection in Crystal to recognize my .csv exports properly. However, I found ADO to be quite a bit slower than connecting the .csv files to reports through Access. It's a bit harder to set up in Access, but once set up, it reads the records MUCH faster (on large .csv files it's noticable anyway). Also, with Access you can manually select the data type if it doesn't interpret correctly by itself. For instance, when using an Outer-Join in the export, if a record doesn't exist Progress puts a "?" in the field. This is fine, but if that happens in numeric fields then Access thinks the field is a text field. Manually setting it to Integer or Double will allow Crystal to correctly find it as a number.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 3:28 PM
Subject: RE: [Vantage] Code for Creating ADO XML Persisted recordset


For me, XML is an alternative to CSV exports.

I needed an alternative, because CSV doesn't hold any schema information (as
in the size of the fields, their datatypes, etc...)

Of course, the Jet/Access drivers can import a csv file and can guess(with
fairly good accuracy) the datatype of the csv column, based on the
characters it contains. However, on one of my vendor transaction reports,
datatypes were being interpreted inconsistently by the Jet drivers.

For programmers using ADO (Advanced Data Objects), this XML format is nice
because you don't have to use an ODBC or OLEDB driver to access the data,
thus improving reliability and performance.

However, Crystal reports doesn't natively support XML files until version
8.5.

XML is of minimal value to most vantage users, but for a few of us
programmers who need to write progress exports to use in external
applications (such as crystal reports), XML is definitely a viable option.

An excerpt from:
http://www.xmlfiles.com/xml/

"XML can be used to exchange data:
In the real world, computer systems and databases contain data in
incompatible formats. One of the most time consuming challenges for
developers has been to exchange data between such systems....Converting the
data to XML can greatly reduce this complexity and create data that can be
read by different types of applications."


All that said, CSV exports still take less code to write, and are still my
method of choice in some instances.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thad,
Lots of questions I could ask, but starting with: What's an XML file and
why would I want to use it?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'Scandale, Matt' ; 'api@...' ; 'peg@...' ;
'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 1:36 PM
Subject: [Vantage] Code for Creating ADO XML Persisted recordset


Here's some code for creating an XML file from progress. The format used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML file.

The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted
recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/

Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]
I also neglected to mention that I've been doing a bit of R&D with Visual
Studio .NET. Once that becomes my primary development environment (when we
get our new vantage and intranet servers) having XML-Based exports will be a
huge plus.

I plan on setting things up so that I can launch my progress exports from
ASP pages, read the XML files, and load them into Crystal .NET reports.

I would use Access databases, but I wanted to allow multiple users to have
multiple instances of the same report (such as a bom) loaded at the same
time. Facilitating that in a multi-user environment required writing code
to copy blank database templates to the user's machine, and writing
additional code to accomodate for multiple linked csv files at the same
time.

Keeping the data encapsulated in One XML File, instead of an Access MDB and
a linked CSV file makes the system alot easier for me to manage.

However, unless you are willing to do a lot more coding than you already
are, there's not much of a need to make the jump to XML quite yet.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...




-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:44 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thanks for the info, Thad. On a somewhat related note: I did get the ADO
connection in Crystal to recognize my .csv exports properly. However, I
found ADO to be quite a bit slower than connecting the .csv files to reports
through Access. It's a bit harder to set up in Access, but once set up, it
reads the records MUCH faster (on large .csv files it's noticable anyway).
Also, with Access you can manually select the data type if it doesn't
interpret correctly by itself. For instance, when using an Outer-Join in
the export, if a record doesn't exist Progress puts a "?" in the field.
This is fine, but if that happens in numeric fields then Access thinks the
field is a text field. Manually setting it to Integer or Double will allow
Crystal to correctly find it as a number.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 3:28 PM
Subject: RE: [Vantage] Code for Creating ADO XML Persisted recordset


For me, XML is an alternative to CSV exports.

I needed an alternative, because CSV doesn't hold any schema information
(as
in the size of the fields, their datatypes, etc...)

Of course, the Jet/Access drivers can import a csv file and can guess(with
fairly good accuracy) the datatype of the csv column, based on the
characters it contains. However, on one of my vendor transaction reports,
datatypes were being interpreted inconsistently by the Jet drivers.

For programmers using ADO (Advanced Data Objects), this XML format is nice
because you don't have to use an ODBC or OLEDB driver to access the data,
thus improving reliability and performance.

However, Crystal reports doesn't natively support XML files until version
8.5.

XML is of minimal value to most vantage users, but for a few of us
programmers who need to write progress exports to use in external
applications (such as crystal reports), XML is definitely a viable option.

An excerpt from:
http://www.xmlfiles.com/xml/

"XML can be used to exchange data:
In the real world, computer systems and databases contain data in
incompatible formats. One of the most time consuming challenges for
developers has been to exchange data between such systems....Converting
the
data to XML can greatly reduce this complexity and create data that can be
read by different types of applications."


All that said, CSV exports still take less code to write, and are still my
method of choice in some instances.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thad,
Lots of questions I could ask, but starting with: What's an XML file
and
why would I want to use it?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'Scandale, Matt' ; 'api@...' ; 'peg@...' ;
'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 1:36 PM
Subject: [Vantage] Code for Creating ADO XML Persisted recordset


Here's some code for creating an XML file from progress. The format
used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a
viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML
file.

The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted
recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/

Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Whoa. You are light-years ahead of me on this stuff. You're saying all the words that I would love to learn about... but alas. Gotta keep focused on the fires at hand. But I'll know who to ask when my pallette clears off.

Thanks Thad.

Troy

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 4:47 PM
Subject: RE: [Vantage] Code for Creating ADO XML Persisted recordset


I also neglected to mention that I've been doing a bit of R&D with Visual
Studio .NET. Once that becomes my primary development environment (when we
get our new vantage and intranet servers) having XML-Based exports will be a
huge plus.

I plan on setting things up so that I can launch my progress exports from
ASP pages, read the XML files, and load them into Crystal .NET reports.

I would use Access databases, but I wanted to allow multiple users to have
multiple instances of the same report (such as a bom) loaded at the same
time. Facilitating that in a multi-user environment required writing code
to copy blank database templates to the user's machine, and writing
additional code to accomodate for multiple linked csv files at the same
time.

Keeping the data encapsulated in One XML File, instead of an Access MDB and
a linked CSV file makes the system alot easier for me to manage.

However, unless you are willing to do a lot more coding than you already
are, there's not much of a need to make the jump to XML quite yet.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...




-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:44 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thanks for the info, Thad. On a somewhat related note: I did get the ADO
connection in Crystal to recognize my .csv exports properly. However, I
found ADO to be quite a bit slower than connecting the .csv files to reports
through Access. It's a bit harder to set up in Access, but once set up, it
reads the records MUCH faster (on large .csv files it's noticable anyway).
Also, with Access you can manually select the data type if it doesn't
interpret correctly by itself. For instance, when using an Outer-Join in
the export, if a record doesn't exist Progress puts a "?" in the field.
This is fine, but if that happens in numeric fields then Access thinks the
field is a text field. Manually setting it to Integer or Double will allow
Crystal to correctly find it as a number.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 3:28 PM
Subject: RE: [Vantage] Code for Creating ADO XML Persisted recordset


For me, XML is an alternative to CSV exports.

I needed an alternative, because CSV doesn't hold any schema information
(as
in the size of the fields, their datatypes, etc...)

Of course, the Jet/Access drivers can import a csv file and can guess(with
fairly good accuracy) the datatype of the csv column, based on the
characters it contains. However, on one of my vendor transaction reports,
datatypes were being interpreted inconsistently by the Jet drivers.

For programmers using ADO (Advanced Data Objects), this XML format is nice
because you don't have to use an ODBC or OLEDB driver to access the data,
thus improving reliability and performance.

However, Crystal reports doesn't natively support XML files until version
8.5.

XML is of minimal value to most vantage users, but for a few of us
programmers who need to write progress exports to use in external
applications (such as crystal reports), XML is definitely a viable option.

An excerpt from:
http://www.xmlfiles.com/xml/

"XML can be used to exchange data:
In the real world, computer systems and databases contain data in
incompatible formats. One of the most time consuming challenges for
developers has been to exchange data between such systems....Converting
the
data to XML can greatly reduce this complexity and create data that can be
read by different types of applications."


All that said, CSV exports still take less code to write, and are still my
method of choice in some instances.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, May 23, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Code for Creating ADO XML Persisted recordset


Thad,
Lots of questions I could ask, but starting with: What's an XML file
and
why would I want to use it?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'Scandale, Matt' ; 'api@...' ; 'peg@...' ;
'vantage@yahoogroups.com'
Sent: Thursday, May 23, 2002 1:36 PM
Subject: [Vantage] Code for Creating ADO XML Persisted recordset


Here's some code for creating an XML file from progress. The format
used
is ADO Persisted recordset (urn:schemas-microsoft-com:rowset).

The resulting file can be loaded into an ADO recordset by using the
recordset's OPEN method.

PEG API Members:
Though this post does not directly relate to API, it does provide a
viable
alternative to COM for creating a temporary database file.

Any questions or comments are welcome.

Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...


/************BEGIN CUSTOMERSXML.P*********/
{xml.i}

output stream xml to c:\test.xml.

run BeginSchema.
run AddFieldToSchema("CustID", "CHAR").
run AddFieldToSchema("CustName", "CHAR").
run AddFieldToSchema("Phone", "CHAR").
run AddFieldToSchema("Fax", "CHAR").
run AddFieldToSchema("Address1", "CHAR").
run AddFieldToSchema("Address2", "CHAR").
run AddFieldToSchema("Address3", "CHAR").
run AddFieldToSchema("City", "CHAR").
run AddFieldToSchema("State", "CHAR").
run AddFieldToSchema("ZIP", "CHAR").
run AddFieldToSchema("Country", "CHAR").
run AddFieldToSchema("CustNum", "NUMBER").
run EndSchema.

run BeginRowSet.

for each customer no-lock where company = "001" by name:
run Addrow.
run UpdateStringField("CustID", custid).
run UpdateStringField("CustName", name).
run UpdateStringField("Phone", phone).
run UpdateStringField("Fax", fax).
run UpdateStringField("Address1", address1).
run UpdateStringField("Address2", address2 ).
run UpdateStringField("Address3", address3).
run UpdateStringField("City", city).
run UpdateStringField("State", state).
run UpdateStringField("ZIP", zip).
run UpdateStringField("Country", country ).
run UpdateNumberField("CustNum", CustNum).
run EndRow.
end.

run endrowset.

output stream xml close.

/* To test the integrity of the ADO Persisted
XML recordset at design time:*/

def var rs as com-handle no-undo.
create "adodb.recordset" rs.

rs:open("c:\test.xml",,,,).
rs:close.
release object rs.

/**********END CUSTOMERSXML.P*********/




/************BEGIN XML.I*********/


/*The purpose of this library is to automate the creation of an XML
file.

The current XML format I'm using is ADO persisted recordset,
but the functions could be rewritten to accommodate
other XML formats, without having to change the calling code.
*/

def var fieldcount as integer no-undo.
def stream xml.

procedure BeginSchema:

put stream xml unformatted
"<xml xmlns:s=""uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882""
xmlns:dt=""uuid:C2F41010-65B3-11d1-A29F-00AA00C14882""
xmlns:rs=""urn:schemas-microsoft-com:rowset""
xmlns:z=""#RowsetSchema"">
<s:Schema id=""RowsetSchema"">
<s:ElementType name=""row"" content=""eltOnly""
rs:updatable=""true"">".
end.

procedure AddFieldToSchema:
define input parameter FieldName as char no-undo.
define input parameter FieldType as char no-undo.

def var dt as char no-undo.
/*these data type strings where simply copied from ADO persisted
recordsets
from a SQL database. More datatype options
should be added as necessary*/

if FieldType = "CHAR" then
dt = "<s:datatype dt:type=""string"" dt:maxLength=""255""
rs:precision=""0"" rs:fixedlength=""true"" />".
else if FieldType = "NUMBER" then
dt = "<s:datatype dt:type=""float"" dt:maxLength=""8""
rs:precision=""15"" rs:fixedlength=""true""/>".
else if FieldType = "CURRENCY" then
dt = "<s:datatype dt:type=""number"" rs:dbtype=""currency""
dt:maxLength=""8"" rs:precision=""19"" rs:fixedlength=""true""/>".
else if FieldType = "DATE" then
dt = "<s:datatype dt:type=""dateTime"" rs:dbtype=""timestamp""
dt:maxLength=""16"" rs:scale=""0"" rs:precision=""16""
rs:fixedlength=""true""/>".

fieldcount = fieldcount + 1.

put stream xml unformatted
"<s:AttributeType name="""
FieldName

""" rs:number="""
string(FieldCount)

""" rs:nullable=""true"" rs:write=""true"">"
dt
"</s:AttributeType>".

end.

procedure EndSchema:
put stream xml unformatted
"<s:extends type=""rs:rowbase"" />
</s:ElementType>
</s:Schema>".
end.

procedure BeginRowSet:

put stream xml unformatted
"<rs:data>
<rs:insert>".
end.

Procedure AddRow:
put stream xml unformatted "<z:row".
end.

Procedure UpdateStringField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as char no-undo.

/*Here's some code to replace some special XML characters with their
corresponding codes.
If you are certain the field does not contain the offending
characters, you could potentially delete the
replace statements for a modest performance increase*/

fieldvalue = replace(fieldvalue, "&", "&").
fieldvalue = replace(fieldvalue, """", """).
fieldvalue = replace(fieldvalue, "'", "'").
fieldvalue = replace(fieldvalue, "<", "<").
fieldvalue = replace(fieldvalue, ">", ">").
fieldvalue = replace(fieldvalue, "æ", "+/-").
fieldvalue = replace(fieldvalue, "°", "deg").

put stream xml unformatted
" " fieldname "=""" fieldvalue """".

end.

Procedure UpdateDateField:
define input parameter FieldName as char no-undo.
define input parameter DateValue as date.


put stream xml unformatted " " fieldname "=""".
put stream xml year (DateValue) FORMAT "9999".
put stream xml "-".
put stream xml month (DateValue) FORMAT "99".
put stream xml "-".
put stream xml day (DateValue) FORMAT "99".
put stream xml "T00:00:00""".

end.

Procedure UpdateNumberField:
define input parameter FieldName as char no-undo.
define input parameter FieldValue as decimal no-undo.

put stream xml unformatted
" " fieldname "=""" string(fieldvalue) """".

end.

Procedure EndRow:
put stream xml unformatted "/>".
end.

procedure EndRowSet:

put stream xml unformatted
"</rs:insert>
</rs:data>
</xml>".
end.
/*END XML.I*/

Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]