Re: Automation script and variable bound to export SQL

Guest · March 12, 2010, 3:11am

DocJones · March 12, 2010, 3:11am

Hello Debbie,

Date/Time range:

Actually i was trying to provide an
easy way to set the requested month of the export queries by a centralized
variable. Every view has a “between
and <first day of current month-X+1>” in its where clause and
by setting X i can easily export different months. Or at least thats what
i have tried

I played around a bit and came up with
the following solution:

“… between qu.trunc(current timestamp-cast(:MONTH
as INTEGER) month,‘MM’) and qu.trunc(current timestamp-cast(:MONTH as INTEGER)
month+1 month,‘MM’) with ur;”

with qu.trunc(…) is a supporting UDF
truncating a TIMESTAMP to the 2nd Parameter (‘MM’ is month).

Now i just need a “Input variable
value here” dialog box and i am fine

VARCHAR

I tried to quote all occurencies of
:DEPOT, but the automation script does not work properly (as you might
see from the attached log file). I added all called scripts and the TAS
file as well to help with understanding. You may have to trim the pathnames
in it a bit.

Suggestions/Features requested:

Its a bit clumsy to create a SQL script
for each export ans save a seperate SQL file. Would be easier if you could
choose if you want to add the SQL to be executed directly to the Export
activity (without creating a SQL file).
Relative pathnames in ALL filename
dialoges (Execute Script, Select to File, Attach emails, etc…)
Print function with detailed information
of each activity attached (just like the current tooltip-bubbles onMouseOver
event).
add a “Execute STP” activity
(currently i have to create a SQL script with “call .()”
in it and Execute it
for polling reason (and while loops
add a “Sleep”/“Pause” activity) (eg, i want to wait
for a certain record in a table to occur)

Further Questions

It seems i cant place parallel activities
within a connection. This makes huge scripts a bit clumsy and very inefficient
in terms of connection re-using.

Just my 2ct

Hope you can help me with the VARCHAR
issue

regards & keep up the great work

/Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -

Saaläckerstrasse 8

63801 Kleinostheim

Tel: +49(6027)40903-13

Fax: +49(6027)40903-9013

MOB: +49(170)9672528

Internet: http://www.ids-logistik.de

E-Mail: marc_rink@ids-logistik.de

Sitz der Gesellschaft: Kleinostheim

Amtsgericht Aschaffenburg - HRB 10253

Geschäftsführer: Dr. Michael Bargl

Vorsitzender des Beirates: Mathias Krage

“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”

–Kristian Wilson, Nintendo 1989.

Von:
Debbie Peabody dpeabody@quest.com

An:
"toad_db2@yahoogroups.com"
toad_db2@yahoogroups.com

Datum:
11.03.2010 22:52

Betreff:
RE: [toad_db2] Re: Automation script
and variable bound to export SQL [1 Attachment]

Gesendet von:
toad_db2@yahoogroups.com

[Attachment(s)
from Debbie Peabody included below]
On the date range issue. It looks like you
want the last 30 days. I would send your SQL to the Query Builder and use
the Date Range tab and let it build the filter for the last 30 days. This
is a built in bind feature. See attached. You would not need to use
a bind var at all for this.

On the varchar with preceding zeros. The
evaluation code seems to be stripping it off. I don’t know DB2 very well.
What I would try is to surround the value in quotes in my SQL statement.
The code will look for the quotes and leave the value alone. But this would
need to be done in the SQL and not after the value is retrieved.

Debbie

From: toad_db2@yahoogroups.com [mailto:toad_db2@yahoogroups.com]
On Behalf Of Marc Rink**
Sent:** Thursday, March 11, 2010 7:26 AM**
To:** toad_db2@yahoogroups.com**
Subject:** [toad_db2] Re: Automation script and variable bound to export
SQL

Message from: DocJones

Heyas,

another thing: i have retrieved values in the form (VARCHAR(10)):
‘0100’, ‘0500’, … and assigned them to a Variable. But the Variable is
being set to the integer representation of the required value: 100, 500,…

Those numbers are branch office numbers and must be with leading zeroes.
How to achive this?

regards

/Z

Historical Messages

Author: Marc Rink

Date: Thu Mar 11 07:26:06 PST 2010

Heyas,

another thing: i have retrieved values in the form (VARCHAR(10)):
‘0100’, ‘0500’, … and assigned them to a Variable. But the Variable is
being set to the integer representation of the required value: 100, 500,…

Those numbers are branch office numbers and must be with leading zeroes.
How to achive this?

regards

/Z

__

Author: Marc Rink

Date: Thu Mar 11 01:03:55 PST 2010

Heyas,

i have an automation script that exports several VIEWS with a given MONTH
Parameter. MONTH is a Variable in the script evaluating to the date last
month (DateAdd(“m”,-1,Now)) used in the “where” clause
for a “between…”.

It seems i cant bind the MONTH variable to the Select to File activitiy.

Here is the log of the automation script:

Category Timestamp Message

Automation Logging 11.03.2010 09:58:02
Build started

Automation Logging 11.03.2010 09:58:03
Warning: No Exception Handler. To send an Email when
there is an error, change the value of Email on Error to true and enter
email values.

Automation Logging 11.03.2010 09:58:03
Build Completed

Automation Logging 11.03.2010 09:58:03
Variable “MONTH” set to “0”

Automation Logging 11.03.2010 09:58:03
Variable value = 0

Automation Logging 11.03.2010 09:58:03
Variable “MONTH” set to “DateAdd(“m”,-1,
Now)”

Automation Logging 11.03.2010 09:58:03
Variable value = 11.02.2010 09:58:03

Automation Logging 11.03.2010 09:58:03
Connecting to LOGI_LS (ELIX)

Automation Logging 11.03.2010 09:58:03
Connection successful.

Automation Logging 11.03.2010 09:58:03
Begin Export

Automation Logging 11.03.2010 09:58:04
SQL to execute: select * from elix.sdgaccess
where upper(vername1) like ‘FEDERAL%’ and upper(vername1) like ‘%MOGUL%’
and (verland!=‘DE’ or empland!=‘DE’) and borddat between qu.trunc(:MONTH-1
month,‘MM’) and qu.trunc(:MONTH,‘MM’)with ur;

Automation Logging 11.03.2010 09:58:04
Beginning export.

Statusbar 11.03.2010 09:58:05
Export Finished [11], errors detected, click
for summary.

Automation Logging 11.03.2010 09:58:05
ERROR [42610] [IBM][DB2/6000] SQL0418N A statement
contains a use of an untyped parameter marker or a null value that is not
valid. SQLSTATE=42610

ERROR [22005] [IBM] CLI0112E Error in assignment. SQLSTATE=22005

Automation Logging 11.03.2010 09:58:05
bei Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext
executionContext)

Automation Logging 11.03.2010 09:58:05
Failed

and here is the Exception raised from the export activitiy:

Thread (11) Export Started [11.03.2010 09:58:04]

Thread (11) Export Wizard: Building object list

Thread (11) Exporting Data

Thread (11) Object SQL Query started.

Thread (47) InternalReadBackground - start

Thread (47) InternalReadBackground - exception ERROR [42610] [IBM][DB2/6000]
SQL0418N A statement contains a use of an untyped parameter marker
or a null value that is not valid. SQLSTATE=42610

ERROR [22005] [IBM] CLI0112E Error in assignment. SQLSTATE=22005

Thread (47) InternalReadBackground - stack bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg,
Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)

bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method)

bei IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)

bei IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior
behavior)

bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

bei Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()

Thread (17) DoneReadTreadWriter

Thread (17) DoneReadThreadWriter finally - adapter.RowsRead 0

Thread (11) Object SQL Query finished with error: ERROR [42610] [IBM][DB2/6000]
SQL0418N A statement contains a use of an untyped parameter marker
or a null value that is not valid. SQLSTATE=42610

ERROR [22005] [IBM] CLI0112E Error in assignment. SQLSTATE=22005

Thread (11) bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg,
Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)

bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method)

bei IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)

bei IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior
behavior)

bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

bei Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()

Thread (11) Export Finished [11.03.2010 09:58:05]

Do i have to explicitly cast the MONTH Variable to a TIMESTAMP? If so,
how is this achivied best?

regards

/Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -

Saaläckerstrasse 8

63801 Kleinostheim

Tel: +49(6027)40903-13

Fax: +49(6027)40903-9013

MOB: +49(170)9672528

Internet: http://www.ids-logistik.de

E-Mail: marc_rink@ids-logistik.de

Sitz der Gesellschaft: Kleinostheim

Amtsgericht Aschaffenburg - HRB 10253

Geschäftsführer: Dr. Michael Bargl

Vorsitzender des Beirates: Mathias Krage

“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”

–Kristian Wilson, Nintendo 1989.

__

.,._

Attachment(s) from Debbie
Peabody

1 of 1 Photo(s)

Last30Days.PNG

Reply
to sender | Reply
to group | Reply
via web post | Start
a New Topic

Messages
in this topic (2)

Recent Activity:

Visit
Your Group

MARKETPLACE
Hobbies
& Activities Zone: Find others who share your passions! Explore new
interests.

Switch to: Text-Only,
Daily
Digest • Unsubscribe
• Terms
of Use

.

,.,_
Script_1.log (5.83 KB)

DocJones · March 12, 2010, 3:11am

Hello Debbie,

Date/Time range:

Actually i was trying to provide an
easy way to set the requested month of the export queries by a centralized
variable. Every view has a “between
and <first day of current month-X+1>” in its where clause and
by setting X i can easily export different months. Or at least thats what
i have tried

I played around a bit and came up with
the following solution:

“… between qu.trunc(current timestamp-cast(:MONTH
as INTEGER) month,‘MM’) and qu.trunc(current timestamp-cast(:MONTH as INTEGER)
month+1 month,‘MM’) with ur;”

with qu.trunc(…) is a supporting UDF
truncating a TIMESTAMP to the 2nd Parameter (‘MM’ is month).

Now i just need a “Input variable
value here” dialog box and i am fine

VARCHAR

I tried to quote all occurencies of
:DEPOT, but the automation script does not work properly (as you might
see from the attached log file). I added all called scripts and the TAS
file as well to help with understanding. You may have to trim the pathnames
in it a bit.

Suggestions/Features requested:

Its a bit clumsy to create a SQL script
for each export ans save a seperate SQL file. Would be easier if you could
choose if you want to add the SQL to be executed directly to the Export
activity (without creating a SQL file).
Relative pathnames in ALL filename
dialoges (Execute Script, Select to File, Attach emails, etc…)
Print function with detailed information
of each activity attached (just like the current tooltip-bubbles onMouseOver
event).
add a “Execute STP” activity
(currently i have to create a SQL script with “call .()”
in it and Execute it
for polling reason (and while loops
add a “Sleep”/“Pause” activity) (eg, i want to wait
for a certain record in a table to occur)

Further Questions

It seems i cant place parallel activities
within a connection. This makes huge scripts a bit clumsy and very inefficient
in terms of connection re-using.

Just my 2ct

Hope you can help me with the VARCHAR
issue

regards & keep up the great work

/Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -