Toad World® Forums

Automation script and variable bound to export SQL


#1

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.


#2

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