Problem when assigning variable content used by a query

[yard250]

Hello,

I've a probleme when assigning a variable content to a query designed trough Database Interface Designer, used by a simple map as input, the whole set running on a IBM RS system.

My query, defined in the Database Interface Designer, is:

select * from table where field = '#TEST#'

This is the input of a simple map.
I build my map with as target an IBM RS 6000.
I deploy my map on the server and launch it with:

mercator mymap.aix -ID1 -VARTEST=valueok

'valueok' is a value returning records.

But calling mymap with this syntax returns nothing !
When I hard-code in the query the value 'valueok', I've a result.

What's wrong with this syntax ?

Thanks a lot,

Kind regards

 

[eyetry]

what version?

in windows 652 wrote something like this

..."' -ID1 '-VAR SUB_ID=''" + SUB_ID Argument:Variable ...

check your quoates and spacing. if you can create a trace/audit file you can review what you are passing to see if it looks accurate.

 

[yard250]

This is version 6.7.1.

I'm working under Unix (IBM AIX). This is a specific system problem...I try your syntax, without success.

Your example seems to be a part of code coming from a RUN command (in a Mercator map).
I try to use and assign a variable content at the OS level, using the 'prompt' command.

Do you have any idea ?

Thanks a lot

 

[janhes]

I don't think when running the map this way it will know what valueok is.
Ususlly you ran a map to call the second map using the variable and passing the value via the RUN function to the second map.

 

[BocaBurger]

I don't see -VARTEST defined anywhere so whatever value you assign would go nowhere. Add a -T to the adapter command in the map and see what your .dbl says.

BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[yard250]

To janhes:
Yes, it is specified in the documentation, Database Interface Designer Reference Guide, p 126 (the documentation references an NT example: mercnt MyMap.mmc -ID1 '-VAR parm1=2000-03-11', variable content used by a stored procedure defined in a query, designed in the Database Interface Designer).

To Bocaburger:
-VAR is a specific keyword, used to say to mercator that the following string (TEST) is the variable name, followed by the assignment sign (=), followed by the assigned value.

Indeed, when using the -T option, the query is displayed and the variable is not replaced by his content.

Thanks a lot

 

[janhes]

Indeed page 126 has the documentation but it is not like your example.

mercnt MyMap.mmc -ID1 '-VAR parm1=2000-03-11'

You appear to be trying to send a variable, valueok. This method only allows a literal; you need the run map to be able to pass a variable.
Also your -VARTEST=valueok should be '-VAR TEST=valueok'

 

[yard250]

Thanks Janhes,

Sorry for my explanations, but valueok is a literal, containing a true value, which returns data set when being replaced in the SQL statement.
It seems the variable, defined in the SQL statement, is not replaced by the value assigned in the command line.

Do you have any idea ?

Thanks !

 

[janhes]

Yes you need a space between -VAR and TEST

 

[janhes]

And put it in single quotes as per example in the documentation.

 

[janhes]

Also a literal can't contain a value - it is the value.

 

[yard250]

Here is the result: space is not appreciated by Mercator in this context (space = second command line, as specified in the documentation).

I must add that FINDLIST exists as value: this is a literal.

Any idea ?

Thanks a lot


mercator mymap.aix -ID1 '-VAR LIST=FINDLIST'

Mercator(TM) IBM(TM) RS/6000 AIX(TM) Command Server - version 6.7(306)
Copyright (C) 2000 Mercator Software

Map file: mymap.aix -ID1 -VAR
In # 1: 0 Out # 1: 1 Time: 0:03 Auditing Data
In # 1: 0 Out # 1: 1 Time: 0:03
In # 1: 0 Out # 1: 1 Time: 0:03
Map completed (0): Map completed successfully (3.399 seconds)



Mercator(TM) IBM(TM) RS/6000 AIX(TM) Command Server - version 6.7(306)
Copyright (C) 2000 Mercator Software

Map file: LIST=FINDLIST.mmc
Error detected in running map, exiting.

 

[janhes]

Try using the -T to produce the trace and post the query displayed in the trace file. That may help.

 

[yard250]

Thanks Janhes,

The query is like the one on the top of this post:

select * from table where field = '#LIST#'

This is exactly what is displayed in the trace file.

Any idea ?

 

[janhes]

I think the query should be
select * from table where field = #LIST#

 

[janhes]

Forget the above, I was looking at an example that passed a number. However, have you got the variable defined in the Database Interface Designer with a dummy value?

 

[janhes]

Mercator(TM) IBM(TM) RS/6000 AIX(TM) Command Server - version 6.7(306)
Copyright (C) 2000 Mercator Software

Map file: LIST=FINDLIST.mmc
Error detected in running map, exiting.


This response is odd. It looks like it's trying to run a windows map for '-VAR LIST=FINDLIST'

 

[yard250]

Yes I do, I assign a dummy value to the variable in the Database Interface Designer, in order to generate the associated type tree.

About your last post: this problem doesn't occur as there is no space between '-VAR' and the var name itself (I think I'v read that in the documentation, for Unix map launch).

 

[BocaBurger]

If there is no space between -var and what is next, then it is being ignored since -varanything is not a valid command line option for the DB adapter. The run result looks like you might have a RUN or EXIT rule in the map. Once the engine reports: "Map completed (0): Map completed successfully (3.399 seconds)" There should be nothing else to run.
"Map file: mymap.aix -ID1 -VAR" is not valid, since you haven't told the engine what -var is. From the command line, what you put in -VAR needs to be the _DATA_ to be passed to the DB adapter to be used when communicating with the DB.

BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[janhes]

I just thought I should check. Input card 1 is the card to run the query?