Solved

PPRO*C and TIMESTAMP, DATE variable

Posted on 2002-04-29
24
2,730 Views
Last Modified: 2012-06-27
Is it possible to define a TIMESTAMP or DATE variables in a PRO*C program? How can I get or enter date, timestamp values without converting them from and to
char[?]
0
Comment
Question by:vangel
  • 10
  • 7
  • 4
  • +2
24 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You use the DATE datatype to store dates and times in 7-byte, fixed-length
fields. As the text bellow shows, the century, year, month, day, hour (in 24-hour
format), minute, and second are stored in that order from left to right.
The century and year bytes are in excess-100 notation. The hour, minute, and
second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less
than 100. The epoch is January 1, 4712 B.C.E. For this date, the century byte is
53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and
second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1).
Normally, there is little reason to use this datatype.
Table 3-3: DATE Format
Byte 1 234567
Meaning Century Year Month Day Hour Minute Second
Example
17-OCT-1994
at 1:23:12 PM
119 194 10 17 14 24 13
0
 
LVL 2

Accepted Solution

by:
dbaora earned 450 total points
Comment Utility
Hi,
you can declare a char variable for date data types and fetch the date into them:

char * getDate() {

char * date;

EXEC SQL BEGIN DECLARE SECTION;
  char my_date[25];
  char db_name[10];
EXEC SQL END   DECLARE SECTION;

EXEC SQL AT :db_name
  SELECT SYSDATE
  into   :my_date
  from DUAL;

if (sqlca.sqlcode != 0)
  printf("Error getting date.\n");
else {
  date = new char[strlen(my_date)+1];
  strcpy(date,my_date);
}
return date;
}


0
 
LVL 2

Expert Comment

by:dbaora
Comment Utility
I forgot to write that you can also use to_date() and to_char() functions on the date data type.

i.e:
select to_char(sysdate,'MM-DD-YY')
into :my_date
from dual;

insert into some_table(the_date)
values to_date(:my_date,'MM-DD-YY');

regards,
dbaora.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
The Oracle internal type DATE has equivalent C++ types:
char[n]    n-byte character array
VARCHAR[n] n-byte variable-length character array
0
 

Author Comment

by:vangel
Comment Utility
Are there any oracle functions (besides the C ones like strftime) that I can use and manipulate the :my_date. I.E extract the Year or set The Year in the :my_date variable?
(I am using oracle 9i). Oracle 9i documentation mentions TIMESTAMP etc as external datatypes. How can I use these external datatypes in a PRO*C program (programming example)?

(I've increased the points to 300)
0
 

Author Comment

by:vangel
Comment Utility
Are there any oracle functions (besides the C ones like strftime) that I can use and manipulate the :my_date. I.E extract the Year or set The Year in the :my_date variable?
(I am using oracle 9i). Oracle 9i documentation mentions TIMESTAMP etc as external datatypes. How can I use these external datatypes in a PRO*C program (programming example)?

(I've increased the points to 300)
0
 

Author Comment

by:vangel
Comment Utility
Are there any oracle functions (besides the C ones like strftime) that I can use and manipulate the :my_date. I.E extract the Year or set The Year in the :my_date variable?
(I am using oracle 9i). Oracle 9i documentation mentions TIMESTAMP etc as external datatypes. How can I use these external datatypes in a PRO*C program (programming example)?

(I've increased the points to 300)
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Look in

Oracle C++ Call Interface
Programmer’s Guide
Release 9.0.1

Find it at Oracle site. If you can not find it drop an email - I will attach it to you if possible.

OCCI TIMESTAMP

Timestamp Class
This class conforms to the SQL92 TIMESTAMP and TIMESTAMPTZ types.
OCCI expects the SQL data type corresponding to the Timestamp class to be of
type TIMESTAMP WITH TIME ZONE.
Timestamp(const Environment *env,
int year = 1,
unsigned int month = 1,
unsigned int day = 1,
unsigned int hour = 0,
unsigned int min = 0,
unsigned int sec = 0,
unsigned int fs = 0,
int tzhour = 0,
Returns a null Timestamp object. A null timestamp can be initialized by
assignment or calling the fromText method. Methods that can be called on null
timestamp objects are setNull, isNull and operator=().
Timestamp();
Assigns the values found in a.
Timestamp(const Timestamp &src);
The following code example demonstrates that the default constructor creates a null
value, and how you can assign a non null value to a timestamp and perform
operations on it:
Environment *env = Environment::createEnvironment();
//create a null timestamp
Timestamp ts;
if(ts.isnull())
cout << "\n ts is Null";
//assign a non null value to ts
Timestamp notNullTs(env, 2000, 8, 17, 12, 0, 0, 0, 5, 30);
ts = notNullTs;

//now all operations are valid on ts...
int yr;
unsigned int mth, day;
ts.getDate(yr, mth, day);
The following code example demonstrates how to use the fromText method to
initialize a null timestamp:
Environment *env = Environment::createEnvironment();
Timestamp ts1;
ts1.fromText("01:16:17.12 04/03/1825", "hh:mi:ssxff dd/mm/yyyy", "", env);
The following code example demonstrates how to get the timestamp column from a
result set, check whether the timestamp is null, get the timestamp value in string
format, and determine the difference between 2 timestamps:
Timestamp reft(env, 2001, 1, 1);
ResultSet *rs=stmt->executeQuery("select order_date from orders
where customer_id=1");
rs->next();
//retrieve the timestamp column from result set
Timestamp ts=rs->getTimestamp(1);
//check timestamp for null
if(!ts.isNull())
{
//get the timestamp value in string format
string tsstr=ts.toText("dd/mm/yyyy hh:mi:ss [tzh:tzm]",0);
if(reft<ts //compare timestamps
{
IntervalDS ds=reft.subDS(ts); //get difference between timestamps
}
}



Summary of Timestamp Methods
Method Summary
fromText() on page 8-222 Sets the time stamp from the values provided by the
string.
getDate() on page 8-222 Gets the date from the Timestamp object.
getTime() on page 8-223 Gets the time from the TimeStamp object.
getTimeZoneOffset() on
page 8-223
Returns the time zone hour and minute offset value.
intervalAdd() on page 8-224 Returns a Timestamp object with value (this +
interval).
intervalSub() on page 8-224 Returns a Timestamp object with value (this -interval).
isNull() on page 8-225 Check if Timestamp is null.
operator=() on page 8-225 Simple assignment.
operator==() on page 8-225 Check if a and b are equal.
operator!=() on page 8-226 Check if a and b are not equal.
operator>() on page 8-226 Check if a is greater than b.
operator>=() on page 8-227 Check if a is greater than or equal to b.
operator<() on page 8-227 Check if a is less than b.
operator<=() on page 8-228 Check if a is less than or equal to b.
setDate() on page 8-228 Sets the year, month, day components contained for
this timestamp.
setNull() on page 8-229 Sets the value of Timestamp to null
setTime() on page 8-229 Sets the day, hour, minute, second and fractional
second components for this timestamp.
setTimeZoneOffset() on
page 8-229
Sets the hour and minute offset for timezone.
subDS() on page 8-230 Returns a IntervalDS representing this - val.
subYM() on page 8-230 Returns a IntervalYM representing this - val.
0
 

Author Comment

by:vangel
Comment Utility
That is what I am looking for (if any), but not OCI or OCCI calls. I do not want to come to the point of allocating handles, just for date manipulation. Plain PRO*C
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
See this example from Metalink:

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -

#include <stdio.h>
 
typedef unsigned char datetype[7];
 
EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR userid[20];
        VARCHAR password[20];
        EXEC SQL TYPE datetype IS DATE REFERENCE;
        datetype verification_date_time;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
 
main()
{
        short i, century, year, month, day, hour, minute, second;
        userid.len = sprintf(userid.arr, "scott");
        password.len = sprintf(password.arr, "tiger");

        EXEC SQL CONNECT :userid IDENTIFIED BY :password;
        EXEC SQL SELECT SYSDATE INTO verification_date_time FROM DUAL;

        century = (short) verification_date_time[0];
        year    = (short) verification_date_time[1];
        month   = (short) verification_date_time[2];
        day     = (short) verification_date_time[3];
        hour    = (short) verification_date_time[4];
        minute  = (short) verification_date_time[5];
        second  = (short) verification_date_time[6];
        printf("century = %d\n", century);
        printf("year = %d\n", year);
        printf("month = %d\n", month);
        printf("day = %d\n", day);
        printf("hour = %d\n", hour);
        printf("minute = %d\n", minute);
        printf("second = %d\n", second);
}

 - - - - - - - - - - - - - - - -  Code ends here  - - - - - - - - - - - - - - - -


Sample Output
-------------
century = 120
year = 101
month = 1
day = 23
hour = 10
minute = 47
second = 43  




sora
0
 

Author Comment

by:vangel
Comment Utility
That is what I am looking for, is there any datatype equivalent to TIMESTAMP that will also return and set the milliseconds??
0
 

Author Comment

by:vangel
Comment Utility
Could also please tell me where is the above documented???
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:sora
Comment Utility
vangel

Check Metalink Document id: 125677.1

Go to http://metalink.oracle.com/

You need a metalink account, but your Oracle account manager should be able to help you with that.

It is very useful to have this account as you can check for yourself a lot of known issues, bugs etc.


ps: you may REJECT the answer proposed by dbaora if you are not happy with it if you want this to go back to the open area for comments from other experts.


sora
0
 

Author Comment

by:vangel
Comment Utility
How about the TIMESTAMP one????
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
To use miliseconds you have to use Java class which gives miliseconds also. After that you can store them to the timestamp string.
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
These are the new external datatypes you can use in 9i:

TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE

it should be similar to what I showed you for DATE

EXEC SQL TYPE datetype IS TIMESTAMP REFERENCE;
         datetype verification_date_time;


you can give this a try


sora
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
You cannot get milliseconds from Oracle, as schwertner rightly pointed out. But you do not need Java necessarily, to get the milliseconds - since you are already using C.

So to summarise - you can use external datatypes for DATE and TIMESTAMP as shown above, so that you can avoid the conversions. But you cannot get millisecond granularity directly from Oracle.


sora
0
 

Author Comment

by:vangel
Comment Utility
I did try the

"EXEC SQL TYPE datetype IS TIMESTAMP REFERENCE"

but when I compile I get the following error:

"Semantic error at line 51, column 27, file vademop.pc:
EXEC SQL TYPE datetype is TIMESTAMP;
..........................1
PCC-S-02350, cannot equivalence this SQL type"
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
When you do the pro*c pre-compiling, can you copy the banner and post it? Just want to double confirm that your client is also 9i


sora
0
 

Author Comment

by:vangel
Comment Utility
>proc vademop.pc
Pro*C/C++: Release 9.0.1.0.0 - Production on Tue May 7 08:47:09 2002

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

System default option values taken from: /home/oracle/oracle9i/precomp/admin/pcscfg.cfg

Semantic error at line 51, column 27, file vademop.pc:
EXEC SQL TYPE datetype is TIMESTAMP;
..........................1
PCC-S-02350, cannot equivalence this SQL type
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
I don't have 9i installed and so I cannot try this out for you.

Read this also:

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus the hour, minute, and second values. It has no time zone. The TIMESTAMP datatype has the form:

TIMESTAMP(fractional_seconds_precision)


where fractional_seconds_precision (which is optional) specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.


and use accordingly.

EXEC SQL TYPE datetype is TIMESTAMP(6)

or

EXEC SQL TYPE datetype is TIMESTAMP()


I don't know the exact format, so try out above and let me know


sora
0
 

Author Comment

by:vangel
Comment Utility
No luck, I did try this before and  I still get the same error
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
vangel-

Does using DATE meet your requirement to select directly into the host variable without doing to_char etc?

Note, that even if you manage to use TIMESTAMP it is similar to DATE but has some extra timezone features only. So you still cannot get the milliseconds even if you use the TIMESTAMP

So, if you want to take this further, post your views


sora
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
Please update and finalize this question.  If you need Moderator assistance to split points or otherwise help with special handling, let us know via comment here or by posting a Zero point question with this question link in the Community Support topic area.
Thank you,
Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now