From Paradox to Advantage

rbuser

Sirs

I have a routine which takes a BDE based report and re-formats it to use
Advantage SQL ie removes all the .db's etc.

As a general statement everything works fine except the RAP code which I can
handle on as as needed basis, however I keep getting an error where the
selection criteria gives and error Column not found 'xxx' where xxx is the
field value I am selecting on.

The example below returns and SQL error Column Not Found: LIVE where
LIVE is the field value I am selecting on.

Baring an odd space the code looks exactly like that I get in the .rtm file
if I create a report from scratch.

Any ideas what I am doing wrong would be greatfully appreciated or how to
debug the error would be greatfully appreciated.

Regards to all

Philip L Jackson



object TdaSQL
DatabaseName = 'advantagereport'
DatabaseType = dtAdvantage
DataPipelineName = 'Quote'
Description = 'Quote'
SQLText.Strings = (
'SELECT Quote.REFERENCE, Quote.STATUS, '
' Quote.CLIENT, Quote.SHORT, Quote.QUOTE, '
' Quote.ADD1, Quote.ADD2, Quote.ADD3, '
' Quote.ADD4, Quote.POST_CODE, Quote.TELE'
'FROM Quote Quote'
'WHERE ( Quote.STATUS = '#39'LIVE'#39' )')
object daCriteria1: TdaCriteria
ChildType = 7
Level = 0
Value = 'LIVE'
object TdaField
AutoSearch = True
Alias = 'Status'
FieldAlias = 'Status'
FieldName = 'STATUS'
TableName = 'Quote'
Mandatory = True
TableAlias = 'Quote'
TableSQLAlias = 'Quote'
end
end

nicocizik

Hi Philip,

Are you loading this template from file or from a BLOB field in your
database? When exactly are you encountering this error? (during the
preview, design, template load... etc.) Be sure you have the proper data
connection on your form and that it is named corresponding to the template.
As a test, you might try creating one of your reports completely from
scratch and see if you can get that one to work.

--

rbuser

Hi Nico

1. I am loading a memo from a blob field in a Paradox table, stripping out
all the "paradox stuff" and replacing it with equivalent Advantage commands
and then saving these into a blob field in an Advantage Table. The code at
the very bottom of this posting is an example of a converted report.

2. I have created a number of Advantage based reports and compared these
with Paradox based equivalents so that I know what to change and where.

3. The Column not found 'xxx' error occurs after conversion when running
the report for the first time.

If I go into the query designer open Search Option,
close search options (no changes necessary)
Go to the design tab and select Save

Then the report works flawlessly. If I look at the before and after
they look identical i.e.

'WHERE ( Quote.STATUS = '#39'LIVE'#39' )')


4. Any reports we create in directly in Advantage work very well.

5. If I compare the equivalent lines in a new Advantage report with the
ones I have converted then, with the exception of spacing, they are
identical to that illustrated in my first posting and #3 above.

Whilst this may seem a small thing, as I am converting my users to Advantage
I will have 1000's of reports to upgrade so I need the process as smooth as
possible.

Thanks for your time, I look forward to your reply

Philip L Jackson

nicocizik

Hi Philip,

Although I have never seen this specific error before, when you say that the
report templates look identical as ASCII text, this leads me to believe that
the problem is somewhere in the streaming to binary process. How are you
saving the template back to your blob field? If you save the template as
text to a .rtm file, are you still seeing an error if you try to load it or
is the error only occuring when you load the template from the blob field?
If possible, please send me a copy of one of the template files that will
cause the error along with the code you are using to stream the templates
into your Advantage blob field. support@digital-metaphors.com

--

rbuser

Hi Nico

The code below loads object Rich Edit1, converts the commands from Paradox
to Advantage and then assigns the lines from Rich Edit1 to the Advantage
Table.

I will create a series of folders with source data and code and send these
as requested.

Again thanks for your assistance.

Regards

Philip L Jackson

lMemoryStream := TMemoryStream.Create;
ItemsTable.Edit;

Stream :=
ItemsTable.CreateBlobStream(ItemsTable.FieldByName('Template'),
bmReadWrite);
Stream.Position := 0;
ObjectBinaryToText(Stream, lMemoryStream);
Stream.Free;
lMemoryStream.Position := 0;
Richedit1.Clear;
Richedit1.PlainText := TRUE;
Richedit1.Lines.LoadFromStream(lMemoryStream); // used
RichEdit as it has a FindText method

ItemsTable.Cancel;

btnConvertClick(Self); // this does the work to convert
Paradox to Advantage

tblItem.Edit;
tblItem.FieldByName('Template').AsString := '';
tblItem.FieldByName('Template').Assign(RichEdit1.Lines);
tblItem.Post;

nicocizik

Hi Philip,

I posted a snip of code below that uses a slightly different method to save
the template file to a blob field on a database. I'm not sure this will
fix the issue but it is definitely worth a try.

Note: plItem is the pipeline used in the application to get the correct
field from the rbItems table.

procedure TForm1.SaveTemplate;
var
lBinaryStream: TMemoryStream;
lTextStream: TMemoryStream;
lWriter: TWriter;
begin

lBinaryStream := TMemoryStream.Create;
lTextStream := TMemoryStream.Create;

try

{load the text stream to the memo control}
memo1.Lines.SaveToStream(lTextStream);

lTextStream.Position := 0;

{convert the binary stream to text}
ObjectTextToBinary(lTextStream, lBinaryStream);

lBinaryStream.Seek(0,soFromEnd);
lWriter := TWriter.Create(lBinaryStream, 1024);

try
lWriter.Root := Self;
lWriter.WriteListEnd;
finally
lWriter.Free;
end;

lBinaryStream.Position := 0;

plItem.Edit;

{load the binary stream from the database}
plItem.SetFieldFromStream('Template', lBinaryStream);

plItem.Post;

finally
lBinaryStream.Free;
lTextStream.Free;
end;

--

rbuser

Hi Nico

Regretably no improvement.

Will email source code sample data etc. as requested.

Thanks in advance

Philip L Jackson