Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Parameter switch!!!


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Dec 12, 2016 12:14 AM Last Post By: Eric ten Westen...
Eric ten Westen...

Posts: 8
Registered: 4/4/10
Parameter switch!!!  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 11, 2016 4:05 AM
Hi

I have this query :

 select
   ORW.ID__ORGANIZATION,
   GETDAYOFTHEWEEK((SELECT B FROM TO_INT(:LANGUAGE_ID)), ORW.ID__DAYOFTHEWEEK) AS DAYOFTHEWEEK
 from ORGANIZATIONREWARD ORW
 where ORW.ID__ORGANIZATION = :ID__ORGANIZATION


As you can see 2 parameters LANGUAGE_ID and ID__ORGANIZATION

GETDAYOFTHEWEEK is an UDF returning a Day description in a certain language for id ID__DAYOFTHEWEEK.
The table has 2 records for ID__ORGANIZATION value 1 NO RECORDS for ID__ORGANIZATION 2!!!

When i run in IBConsole i fill in:

LANGUAGE_ID = 1
ID__ORGANIZATION = 1

Expecting 2 records in Dutch
Result ok!

Now rollback and try again:

LANGUAGE_ID = 2
ID__ORGANIZATION = 1

Expecting 2 records in English
Result: NO Records!!!!

Now rollback and try again:

LANGUAGE_ID = 1
ID__ORGANIZATION = 2

Expection NO records:
Result: 2 Records in English
So the parameter LANGUAGE_ID did get the value 2 for English
and the parameter ID__ORGANIZATION did get the value 1 otherwise i did not get records at all!!

Any help appreciated!

Eric

Using Interbase XE7 Update 6

function TO_INT

ALTER PROCEDURE "TO_INT" 
(
  "A" INTEGER
)
RETURNS
(
  "B" INTEGER
)
AS
BEGIN
   B = A;
   SUSPEND;
END
 ^


Edited by: Eric ten Westenend on Dec 11, 2016 4:11 AM
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Parameter switch!!! [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 11, 2016 9:15 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Eric ten Westenend wrote:
Hi

I have this query :

 select
   ORW.ID__ORGANIZATION,
   GETDAYOFTHEWEEK((SELECT B FROM TO_INT(:LANGUAGE_ID)), ORW.ID__DAYOFTHEWEEK) AS DAYOFTHEWEEK
 from ORGANIZATIONREWARD ORW
 where ORW.ID__ORGANIZATION = :ID__ORGANIZATION


As you can see 2 parameters LANGUAGE_ID and ID__ORGANIZATION

InterBase parses parameters funny. Most databases, and more importantly TParam,
parses left to right top to bottom. InterBase though starts from the FROM
clause parses down, then goes back and parses the Select part and does this
recursively (IOW if there are sub queres follows the same pattern within the sub
query when it gets to it).

Also InterBase does NOT support named parameters. Your query is first preparsed
and the :xxx slots are replaced with a ? the the ? are mapped to the TParam by
index number.

So in your case TParams has mapped :LANGUAGE_ID to parameter slot 0 and
:ID__ORGANIZATION to 1. InterBase though as mapped :LANGUAGE_ID to parameter
slot 1 and :ID__ORGANIZATION to 0. Normally you need never touch nor should you
touch TParam's ordering because it will mess up because it pushes parameters
across based on there positioning in the TParams array.

In this case you need to reorder the TParams so that :ID__ORGANIZATION is first
(:LANGUAGE_ID would therefor be second). With TQuery this can be done at design
time, with TIBDataset/TIBSQL you will need to do this at runtime after a prepare.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Eric ten Westen...

Posts: 10
Registered: 6/1/15
Re: Parameter switch!!! [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 12, 2016 12:14 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thanks Jeff for this fast reaction!

Jeff Overcash (TeamB) wrote:
Eric ten Westenend wrote:
Hi

I have this query :

 select
   ORW.ID__ORGANIZATION,
   GETDAYOFTHEWEEK((SELECT B FROM TO_INT(:LANGUAGE_ID)), ORW.ID__DAYOFTHEWEEK) AS DAYOFTHEWEEK
 from ORGANIZATIONREWARD ORW
 where ORW.ID__ORGANIZATION = :ID__ORGANIZATION


As you can see 2 parameters LANGUAGE_ID and ID__ORGANIZATION

InterBase parses parameters funny. Most databases, and more importantly TParam,
parses left to right top to bottom. InterBase though starts from the FROM
clause parses down, then goes back and parses the Select part and does this
recursively (IOW if there are sub queres follows the same pattern within the sub
query when it gets to it).

Also InterBase does NOT support named parameters. Your query is first preparsed
and the :xxx slots are replaced with a ? the the ? are mapped to the TParam by
index number.

So in your case TParams has mapped :LANGUAGE_ID to parameter slot 0 and
:ID__ORGANIZATION to 1. InterBase though as mapped :LANGUAGE_ID to parameter
slot 1 and :ID__ORGANIZATION to 0. Normally you need never touch nor should you
touch TParam's ordering because it will mess up because it pushes parameters
across based on there positioning in the TParams array.

In this case you need to reorder the TParams so that :ID__ORGANIZATION is first
(:LANGUAGE_ID would therefor be second). With TQuery this can be done at design
time, with TIBDataset/TIBSQL you will need to do this at runtime after a prepare.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02