DataViewToHTML with direct SQL


Jackson, Adam
 

Hi,

 

I am trying to get DataViewToHTML working on a batch task with direct SQL and it is not working. My set up is as follows:

 

In Task Prefix

Where v Data List is populated with

Trim(v Data List)&IF(v Data List<>'',',','')&VarName('FK'VAR+LoopCounter()-1)

 

And v Column List is populated with

Trim(v Column List)&IF(v Column List<>'',',','')&VarName('FK'VAR+LoopCounter()-1)

Followed by

RepStr(GR,'Virtual.','')

 

v Data List ends up looking like Virtual.<Column1>,Virtual.<Column2>,Virtual.<Column3> etc

v Column List ends up looking like <Column1>,<Column2>,<Column3> etc

 

In Task Suffix

Evaluate Expression DataViewToHTML(0,Trim(v Data List),Trim(v Column List),'k:\html_test.html','',0)

with logical virtual populated in the result of this evaluate expression.

 

When I run the program I do not get the HTML file and the logical virtual is false.

 

When I run the program as a straight batch task with screen on the data is returned to screen ok.

 

Is what I’m trying to do possible and if so what am I doing wrong?

 

xPa 2.5

 

TIA

 

Adam

 

 

 

 

 

 

 

Adam
Jackson
Manager, Business Systems
T  +447949892482
M  +447949892482
E  adam.jackson@...
McLaren Formula 1 Team
McLaren Technology Centre
Chertsey Road, Woking
Surrey, GU21 4YH, UK
mclaren.com
Facebook
Instagram
LinkedIn
Twitter
YouTube

The contents of this e-mail are confidential and for the exclusive use of the intended recipient. If you are not the intended recipient you should not read, copy, retransmit or disclose its contents. If you have received this email in error please delete it from your system immediately and notify us either by email or telephone. The views expressed in this communication may not necessarily be the views held by McLaren Racing Limited.
McLaren Racing Limited, McLaren Technology Centre, Chertsey Road, Woking, Surrey, GU21 4YH, UK. Company Number: 01517478


Frederik Soete
 

Hi, Adam,

If I am allowed to guess, I think you do not need the 'Virtual.' part at all.

HTH,

Frederik Soete

Op ma 24 mei 2021 10:03 schreef Jackson, Adam <adam.jackson@...>:

Hi,

 

I am trying to get DataViewToHTML working on a batch task with direct SQL and it is not working. My set up is as follows:

 

In Task Prefix

Where v Data List is populated with

Trim(v Data List)&IF(v Data List<>'',',','')&VarName('FK'VAR+LoopCounter()-1)

 

And v Column List is populated with

Trim(v Column List)&IF(v Column List<>'',',','')&VarName('FK'VAR+LoopCounter()-1)

Followed by

RepStr(GR,'Virtual.','')

 

v Data List ends up looking like Virtual.<Column1>,Virtual.<Column2>,Virtual.<Column3> etc

v Column List ends up looking like <Column1>,<Column2>,<Column3> etc

 

In Task Suffix

Evaluate Expression DataViewToHTML(0,Trim(v Data List),Trim(v Column List),'k:\html_test.html','',0)

with logical virtual populated in the result of this evaluate expression.

 

When I run the program I do not get the HTML file and the logical virtual is false.

 

When I run the program as a straight batch task with screen on the data is returned to screen ok.

 

Is what I’m trying to do possible and if so what am I doing wrong?

 

xPa 2.5

 

TIA

 

Adam

 

 

 

 

 

 

 

Adam
Jackson
Manager, Business Systems
T  +447949892482
M  +447949892482
E  adam.jackson@...
McLaren Formula 1 Team
McLaren Technology Centre
Chertsey Road, Woking
Surrey, GU21 4YH, UK
mclaren.com
Facebook
Instagram
LinkedIn
Twitter
YouTube

The contents of this e-mail are confidential and for the exclusive use of the intended recipient. If you are not the intended recipient you should not read, copy, retransmit or disclose its contents. If you have received this email in error please delete it from your system immediately and notify us either by email or telephone. The views expressed in this communication may not necessarily be the views held by McLaren Racing Limited.
McLaren Racing Limited, McLaren Technology Centre, Chertsey Road, Woking, Surrey, GU21 4YH, UK. Company Number: 01517478


Jackson, Adam
 

HI,

 

Just tried that by doing the RepStr I did on Column List virtual and still same.

 

Adam

 

Adam
Jackson
Manager, Business Systems
T  +447949892482
M  +447949892482
E  adam.jackson@...
McLaren Formula 1 Team
 

From: main@magicu-l.groups.io <main@magicu-l.groups.io> On Behalf Of Frederik Soete via groups.io
Sent: 24 May 2021 09:13
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] DataViewToHTML with direct SQL

 

CAUTION: This email originated from outside of McLaren. Do not click links or open attachments unless you recognise the sender and know the content is safe. Report any suspicious emails to security@...

 

Hi, Adam,

 

If I am allowed to guess, I think you do not need the 'Virtual.' part at all.

 

HTH,

 

Frederik Soete

Op ma 24 mei 2021 10:03 schreef Jackson, Adam <adam.jackson@...>:

Hi,

 

I am trying to get DataViewToHTML working on a batch task with direct SQL and it is not working. My set up is as follows:

 

In Task Prefix

Where v Data List is populated with

Trim(v Data List)&IF(v Data List<>'',',','')&VarName('FK'VAR+LoopCounter()-1)

 

And v Column List is populated with

Trim(v Column List)&IF(v Column List<>'',',','')&VarName('FK'VAR+LoopCounter()-1)

Followed by

RepStr(GR,'Virtual.','')

 

v Data List ends up looking like Virtual.<Column1>,Virtual.<Column2>,Virtual.<Column3> etc

v Column List ends up looking like <Column1>,<Column2>,<Column3> etc

 

In Task Suffix

Evaluate Expression DataViewToHTML(0,Trim(v Data List),Trim(v Column List),'k:\html_test.html','',0)

with logical virtual populated in the result of this evaluate expression.

 

When I run the program I do not get the HTML file and the logical virtual is false.

 

When I run the program as a straight batch task with screen on the data is returned to screen ok.

 

Is what I’m trying to do possible and if so what am I doing wrong?

 

xPa 2.5

 

TIA

 

Adam

 

 

 

 

 

 

 

Adam

Jackson

Manager, Business Systems

T  +447949892482

M  +447949892482

E  adam.jackson@...

McLaren Formula 1 Team

McLaren Technology Centre

Chertsey Road, Woking

Surrey, GU21 4YH, UK

mclaren.com

Facebook

Instagram

LinkedIn

Twitter

YouTube

The contents of this e-mail are confidential and for the exclusive use of the intended recipient. If you are not the intended recipient you should not read, copy, retransmit or disclose its contents. If you have received this email in error please delete it from your system immediately and notify us either by email or telephone. The views expressed in this communication may not necessarily be the views held by McLaren Racing Limited.
McLaren Racing Limited, McLaren Technology Centre, Chertsey Road, Woking, Surrey, GU21 4YH, UK. Company Number: 01517478


Steven Blank
 

Adam,

This may be one of those secret handshake things for which Magic's documentation is infamous, but I don't think you can use the DataViewTo••• functions in a Batch task, at least not in a Direct SQL Batch task, at least not that I've ever been able to make it work. For what it's worth, using the Pervasive SQL gateway and evaluating a DataViewTo••• function anywhere in a Direct SQL Batch task's Record level elicits the ODBC error, "Invalid cursor state" – there's a hint buried in there.

The only way I found to use DataViewTo••• functions with Direct SQL is in an Online task. This just means that you have to rig the Online task to work like a batch task, that is, in-an-out, one-and-done.

Steven G. Blank
SGBlank Consulting


Jackson, Adam
 

HI Steve,
Thanks, got it working though there seems to be some character limit on the data and column name arguments. When I restrict the columns so length of these are 257 chars it works. If I add one more column on to make length 273 it stops working ☹ Luckily, it also does not need the "Virtual." Bit at the beginning of the data variables or I would even get less columns in. I guess I'll have to use really short column names to get the file created, read it back in as a blob and replace short column names with the ones I actually want. As sometimes the case with Magic functionality - close but no cigar.

Thanks again for the help, regards,

Adam



Adam,

This may be one of those secret handshake things for which Magic's documentation is infamous, but I don't think you can use the DataViewTo••• functions in a Batch task, at least not in a Direct SQL Batch task, at least not that I've ever been able to make it work. For what it's worth, using the Pervasive SQL gateway and evaluating a DataViewTo••• function anywhere in a Direct SQL Batch task's Record level elicits the ODBC error, "Invalid cursor state" – there's a hint buried in there.

The only way I found to use DataViewTo••• functions with Direct SQL is in an Online task. This just means that you have to rig the Online task to work like a batch task, that is, in-an-out, one-and-done.

Steven G. Blank
SGBlank Consulting





Adam
Jackson
Manager, Business Systems
T  +447949892482
M  +447949892482
E  adam.jackson@...
McLaren Formula 1 Team
McLaren Technology Centre
Chertsey Road, Woking
Surrey, GU21 4YH, UK
mclaren.com
Facebook
Instagram
LinkedIn
Twitter
YouTube

The contents of this e-mail are confidential and for the exclusive use of the intended recipient. If you are not the intended recipient you should not read, copy, retransmit or disclose its contents. If you have received this email in error please delete it from your system immediately and notify us either by email or telephone. The views expressed in this communication may not necessarily be the views held by McLaren Racing Limited.
McLaren Racing Limited, McLaren Technology Centre, Chertsey Road, Woking, Surrey, GU21 4YH, UK. Company Number: 01517478


Jackson, Adam
 

Correction: not character limit – direct SQL APG had made a long text column a Blob which the DataTo command did not like. Made it a long Unicode and all good

 

Adam

Jackson

Manager, Business Systems

T  +447949892482

M  +447949892482

E  adam.jackson@...

McLaren Formula 1 Team

 

 

From: main@magicu-l.groups.io <main@magicu-l.groups.io> On Behalf Of Jackson, Adam via groups.io
Sent: 25 May 2021 09:24
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] DataViewToHTML with direct SQL

 

CAUTION: This email originated from outside of McLaren. Do not click links or open attachments unless you recognise the sender and know the content is safe. Report any suspicious emails to security@...

 

HI Steve,
Thanks, got it working though there seems to be some character limit on the data and column name arguments. When I restrict the columns so length of these are 257 chars it works. If I add one more column on to make length 273 it stops working Luckily, it also does not need the "Virtual." Bit at the beginning of the data variables or I would even get less columns in. I guess I'll have to use really short column names to get the file created, read it back in as a blob and replace short column names with the ones I actually want. As sometimes the case with Magic functionality - close but no cigar.

Thanks again for the help, regards,

Adam



Adam,

This may be one of those secret handshake things for which Magic's documentation is infamous, but I don't think you can use the DataViewTo••• functions in a Batch task, at least not in a Direct SQL Batch task, at least not that I've ever been able to make it work. For what it's worth, using the Pervasive SQL gateway and evaluating a DataViewTo••• function anywhere in a Direct SQL Batch task's Record level elicits the ODBC error, "Invalid cursor state" – there's a hint buried in there.

The only way I found to use DataViewTo••• functions with Direct SQL is in an Online task. This just means that you have to rig the Online task to work like a batch task, that is, in-an-out, one-and-done.

Steven G. Blank
SGBlank Consulting




Adam

Jackson

Manager, Business Systems

T  +447949892482

M  +447949892482

E  adam.jackson@...

McLaren Formula 1 Team

McLaren Technology Centre

Chertsey Road, Woking

Surrey, GU21 4YH, UK

mclaren.com

Facebook

Instagram

LinkedIn

Twitter

YouTube

The contents of this e-mail are confidential and for the exclusive use of the intended recipient. If you are not the intended recipient you should not read, copy, retransmit or disclose its contents. If you have received this email in error please delete it from your system immediately and notify us either by email or telephone. The views expressed in this communication may not necessarily be the views held by McLaren Racing Limited.
McLaren Racing Limited, McLaren Technology Centre, Chertsey Road, Woking, Surrey, GU21 4YH, UK. Company Number: 01517478