Datetime variable issue

[system]

Hi,

We recently migrated from BODS 3.2 to 4.1 and started observing the below issue in the 4.1 version.

Before starting the actual Dataflow, one script retrieves the sysdate value from oracle to use it as a cutoff date for differential load.

We had been using the below code from BODS 3.1 onwards and was always working. $GV_Current_Time is a global variable of type datetime

[i]$GV_Current_Time = sql(‘ORACLE_DS’,‘SELECT sysdate FROM dual’);

print('The current date is '||$GV_Current_Time);[/i]

This has stopped working in BODS 4.1. No value is assigned to the global variable $GV_Current_Time and the job is failing. When I validated the script I got the below warning
“Warning: Expression <$GV_Current_Time> of type will be converted to type . (BODI-1110432)”

So I tried the below solutions which removes the warning. But still there is no value being populated in $GV_Current_Time.

1. Commented the print command
   $GV_Current_Time = sql(‘ORACLE_DS’,‘SELECT sysdate FROM dual’);
   #print('The current date is '||$GV_Current_Time);
2. Used to_date function
   $GV_Current_Time = to_date(sql(‘ORACLE_DS’,‘SELECT sysdate FROM dual’),‘yyyymmdd hh24:mi:ss’);
   #print('The current date is '||$GV_Current_Time);
3. Used a local variable for printing the value
   $GV_Current_Time = sql(‘ORACLE_DS’,‘SELECT sysdate FROM dual’);
   $LV_Current_Time = $GV_Current_Time;
   print('The current date is '||$LV_Current_Time);

Has anybody faced this type of issue with Datetime variables?

Thanks in advance.

-Karthick

---

ack83 (BOB member since 2013-06-07)

[system]

Common problem:

Things you should never do - a collection and discussion

Approach: I want to use a date inside the sql() function Solution: sql(‘DataStore’, 'DELETE FROM table WHERE date_col = [$start_date] '); Problem with that: is actually multi-fold. First is the parameter substitution. In all strings DataServices does see, it will check for either [] or {} combinations. Each text inside these brackets will be put into the expression parser and replaced by its value. So the string ‘1+1=[1+1]’ will result in the string ‘1+1=2’. And with {} extra quotes will be p…

---

Werner Daehn (BOB member since 2004-12-17)

[system]

Well, besides your loose programming (see this thread unable to Pars SQL () statement in DI, post number 11 for an explanation) you should have had one of two things happen:

1. A value populated into the global variable. Not necessarily the value you want, but it should have had something.
2. An error that the string value generated by the SQL() function could not be converted into the variable.

If you truly got NOTHING in the variable then that’s not right.

If you use this instead, what do you get?

print('The current date is '|| to_char(nvl($LV_Current_Time, sysdate()), 'MM/DD/YYYY HH24:MI:SS')); 

---

eganjp (BOB member since 2007-09-12)

[system]

Thanks to both of you for the responses. Unfortunately this is inherited code which we are living with and we look at it only when we get such issues.

@eganjp

Well, besides your loose programming (see this thread unable to Pars SQL () statement in DI, post number 11 for an explanation) you should have had one of two things happen:

1. A value populated into the global variable. Not necessarily the value you want, but it should have had something.
2. An error that the string value generated by the SQL() function could not be converted into the variable.

If you truly got NOTHING in the variable then that’s not right.

If you use this instead, what do you get?

print('The current date is '|| to_char(nvl($LV_Current_Time, sysdate()), 'MM/DD/YYYY HH24:MI:SS')); 

The print statement printed the sysdate value.

I tried the below code and it printed the value correctly.
print('The current date is '||sql(‘ORACLE_DS’,‘SELECT sysdate FROM dual’));

The issue seems to be in assigning this sysdate value to the datetime variable.

EDIT: This issue occurs only in BODS Windows server. If BODS Server is installed in Linux, then the date is fetched without any issue.

---

ack83 (BOB member since 2013-06-07)

[system]

@ack83

I tried the below code and it printed the value correctly.
print('The current date is '||sql(‘ORACLE_DS’,‘SELECT sysdate FROM dual’));

In this case the value coming back from the sql() funciton is always a string. It is converted for you. What was the actual value printed?

@ack83

The issue seems to be in assigning this sysdate value to the datetime variable.

Here’s where you could have a difference in server and client date formats.

@ack83

EDIT: This issue occurs only in BODS Windows server. If BODS Server is installed in Linux, then the date is fetched without any issue.

Exactly. The two servers probably have different default date formats. The Linux one matches what the database expects. The Windows server doesn’t match.

---

eganjp (BOB member since 2007-09-12)