systimestamp

最新推荐文章于 2024-05-14 00:51:49 发布
翻译 最新推荐文章于 2024-05-14 00:51:49 发布 · 5k 阅读 · 0
文章标签：

#systimestamp #Oracle

本文详细介绍了Oracle数据库中时间戳与时区的相关概念，包括systimestamp、localtimestamp和current_timestamp的区别，以及不同时间戳类型的存储方式和时区处理方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

        不管是笑与否——我没有什么货，这篇文章是在真正的没有章法后的简单的剪切粘贴。

 

20:39:51 SQL> create table t1 (t1 timestamp);

Table created.

20:39:55 SQL> insert into t1 values(systimestamp);

1 row created.

20:39:59 SQL> select t1 - systimestamp  from t1;

T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620

1 row selected.

20:40:08 SQL>


 

          我的笔记本电脑运行Oracle很流畅，仅仅在5小时之内花了4秒消逝。运行在64位的Linux系统的Oracle 11.2.0.3 ——客户端以TZ=EST5EDT运行，但服务器端以UK时运行（目前BST（GMT+1））。

 

        在MOS: 340512.1的时间戳和时区评论可用——关于MOS的常问问题，幸亏Jure Bratina，在这个问题上评论，227334.1——“日期和日历——常问问题”。

 

        更新：

         正像Niall在这个评论里引用的，“时间是很复杂的”。

 

         Oracle提供三个时间戳：systimestamp，localtimestamp和current_timestamp（根据很多一致性原则，仅仅有一个使用下划线）。Oracle也提供三个时间戳类型：timestamp，timestamp with time zone和timestamp with local time zone。Oracle同时也提供两种时区，叫做dbtimezone和sessiontimezone。

 

         如果你需要弄清楚这些联系起来的细节，我认为你需要设置你机器的时区为非UTC（或者是GMT我仍然倾向于这样叫它），然后使用两个分离的机器做为客户端，它们的时区设置成其他时区（）再次避免UTC。

 

         我做了很多实验，但是我的设置还不够严谨——我的机器时区是GMT，但是我打开一个（UNIX机器）会话设置会话时区为EST5EDT然后启动我的数据库，接着运行另一个（UNIX机器）会话，设置不同的时区。在不同的时区我应该重启机器的原因是Oracle正常化的timestamps设置成UTC——意思是说有几种情况，当我不能确定存储的值是是否在UTC里，因为它被正常化或者因为仅仅是真实的机器时间。

 

         隐藏这里有个小小的实验（11.2.0.2，实例以EST5EDT允许，unix会话以UTC允许，通过网络跟服务器取得连接）。

 

select
        current_timestamp,
        localtimestamp,
        systimestamp
from
        dual
;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
17-APR-13 11.37.10.870658 AM +01:00
17-APR-13 11.37.10.870658 AM
17-APR-13 06.37.10.870554 AM -04:00


 

         注意：

         systimestamp反映实例的timestamp——比会话timestamp早五个小时。systimestamp返回带时区的timestamp，不仅仅是一个timestamp。localtimestamp和current_timestamp显示客户端时间，但是localtimestamp不显示时区，current_timestamp要显示（+1:00出现了因为夏令时（英国夏令时间）被激活了因此我的会话比UTC提前了一个小时，然后数据库滞后了4小时）。

        另一个快速测试：

 

create table t1 (
        t0              timestamp,
        tz              timestamp with time zone,
        tl              timestamp with local time zone,
        ts_type         varchar2(20)
)
;

insert into t1 values(
        systimestamp, systimestamp, systimestamp,
        'sys Timestamp'
);

commit;

select * from t1;

T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL                                                                          TS_TYPE
--------------------------------------------------------------------------- --------------------
17-APR-13 06.44.04.353489 AM
17-APR-13 06.44.04.353489 AM -04:00
17-APR-13 11.44.04.353489 AM                                                sys Timestamp

select
        dump(t0,16),
        dump(tz,16),
        dump(tl,16),
        ts_type
from
        t1
;
DUMP(T0,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TZ,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TL,16)
------------------------------------------------------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,71,4,11,7,2d,5,15,11,d0,68
Typ=181 Len=13: 78,71,4,11,b,2d,5,15,11,d0,68,10,3c
Typ=231 Len=11: 78,71,4,11,b,2d,5,15,11,d0,68
sys Timestamp


 

 

         注意：

         T0：timestamp列，有实例的timestamp——但是没有存储任何时区信息；原始转储显示的值是6:44:04（7,2d,5——转换为十六进制然后减去1）。任何人在任何时区都会看到输出显示6:44:04如果他们选择这一列。

 

         TZ——the timestamp with time zone列，有实例的timestamp，但是存储（b, 2d,5 – 11:44:04）有时区信息（10,3c），允许会话知晓全局的时间和地区（或者更一进步，时区）信息。

 

         TL——the timestamp with local time zone列，有实例的timestamp，但没有存储（b, 2d, 5 – 11:44:04）时区信息。因此当你查询时，输出的结果被调整为适合长的时间戳。这是正确的全局时刻，显示相关的本地时间。但是，作为惩罚，将会丢失关于进入（在哪个时区）的信息。

 

         我认为这个关于原始转储的三个不同类型的内容的测验可以帮助你明白你为什么需要在一个列里存储包含时区——如果你不想丢失一些信息，基于时间的算术将会给你一些惊喜，如果你的应用是跨时区的话，

 

         下一个话题：

         索引时间（虽然在Tony Hasler’s blog的评论里的连接或许可以所有你想要的答案），Oracle设计的错误我曾经访问过。

 

         我曾经访问过：

        http://jonathanlewis.wordpress.com/2010/04/05/failed-login/

 

For your entertainment – there’s nothing up my sleeves, this was a simple cut-n-paste after real-time typing with no tricks:

20:39:51 SQL> create table t1 (t1 timestamp);

Table created.

20:39:55 SQL> insert into t1 values(systimestamp);

1 row created.

20:39:59 SQL> select t1 - systimestamp  from t1;

T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620

1 row selected.

20:40:08 SQL>

My laptop runs Oracle so quickly that it took only 4 seconds for 5 hours to elapse !
11.2.0.3 on 64-bit Linux – the client is running with TZ=EST5EDT, while the server is running UK Time (currently BST (GMT+1))

Comments available on MOS: 340512.1 Timestamps & time zones – Frequently Asked Questions
Another MOS note, thanks to Jure Bratina in the comments: 227334.1 – “Dates & Calendars – Frequently Asked Questions” in the question

Update:
As Niall quotes in the comments: “times are difficult”.
Oracle supplies three timestamps: systimestamp, localtimestamp, and current_timestamp. (For reasons of consistency, only one of uses an underscore ;) )
Oracle also supplies three timestamp types: timestamp, timestamp with time zone, and timestamp with local time zone.
Oracle also supplies two timezone calls: dbtimezone, and sessiontimezone

If you need to figure out all the details of how these things hang together, I think you need to set your machine timezone to something that isn’t UTC (or GMT as I still tend to call it), then use two separate machines as clients, with their timezones set to two other timezones (again avoiding UTC).

I’ve done a few experiments but without being so rigorous in my settings – my machine was running on GMT, but I opened a (UNIX) session and set the session time zone to EST5EDT to start the database, while running other (UNIX) session with different TZ settings. The reason I should have restarted the machine in a different timezone is that Oracle “normalises” some timestamps to UTC – which means there are cases when I can’t be certain whether the stored value is in UTC because it has been normalised or because it simply was the actual machine time.

So here’s a little experiment (11.2.0.2, instance started in EST5EDT, unix session running in UTC, connecting across the network to the server).

select
        current_timestamp,
        localtimestamp,
        systimestamp
from
        dual
;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
17-APR-13 11.37.10.870658 AM +01:00
17-APR-13 11.37.10.870658 AM
17-APR-13 06.37.10.870554 AM -04:00

Notes:
systimestamp reflects the instance timestamp – which is 5 hours earlier than the session timestamp.
systimestamp returns a timestamp with time zone, not just a timestamp
localtimestamp and current_timestamp show the client time, but localtimestamp doesn’t show the timezone, current_timestamp does (the +1:00 appears because Daylight Saving Time (British Summer Time) is active so my session is one hour ahead of UTC, while the database is 4 hours behind.)

Another quick test:

create table t1 (
        t0              timestamp,
        tz              timestamp with time zone,
        tl              timestamp with local time zone,
        ts_type         varchar2(20)
)
;

insert into t1 values(
        systimestamp, systimestamp, systimestamp,
        'sys Timestamp'
);

commit;

select * from t1;

T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL                                                                          TS_TYPE
--------------------------------------------------------------------------- --------------------
17-APR-13 06.44.04.353489 AM
17-APR-13 06.44.04.353489 AM -04:00
17-APR-13 11.44.04.353489 AM                                                sys Timestamp

select
        dump(t0,16),
        dump(tz,16),
        dump(tl,16),
        ts_type
from
        t1
;
DUMP(T0,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TZ,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TL,16)
------------------------------------------------------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,71,4,11,7,2d,5,15,11,d0,68
Typ=181 Len=13: 78,71,4,11,b,2d,5,15,11,d0,68,10,3c
Typ=231 Len=11: 78,71,4,11,b,2d,5,15,11,d0,68
sys Timestamp

Notes:
T0 – the timestamp column, has the instance timestamp in it – but doesn’t have any timezone information stored; the raw dump show the value 6:44:04 (7, 2d, 5 – convert from hex and substract one). Anyone on ANY timezone will see their output showing 6:44:04 if they select this column.

TZ – the timestamp with time zone column, has the instance timestamp, but has stored it as (b, 2d,5 – 11:44:04) with time zone information (10,3c) that allows the session to know what “global” moment the information really represents and the location (or, rather, time zone) where is was entered.

TL – the timestamp with local time zone, has the instance timestamp, but has stored it as (b, 2d, 5 – 11:44:04) with NO timezone information. So the output when you query this column is adjusted to suit the local timestamp. It’s the right “global” moment, and it displays as the relevant local time. But, as a penalty, it’s lost the information about where (in which time zone) it was entered.

I think that examination of the content of the raw dumps of the three different types may help you understand why you need to store timestamps in a column type that includes a time zone – if you don’t then you lose some information, and time-based arithmetic will give you some surprises if your application crosses timezones.

Next Issue:
Indexing time (though the link in the comments below to Tony Hasler’s blog probably gives you all the answers you need), and an Oracle design error that I’ve visited before.

I’ve visited before.
http://jonathanlewis.wordpress.com/2010/04/05/failed-login/

 

 

        http://www.oaktable.net/content/systimestamp

 

katoonSina 优快云
@Wentasy 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [优快云博客]
mysql中systimestamp_操作系统的时区设置会影响数据库查询SYSDATE和SYSTIMESTAMP的值
weixin_34901508的博客
02-07 368
SYSDATE和SYSTIMESTAMP的值并不受数据库參数DBTIMEZONE的影响，操作系统时区的环境变量(如TZ)会影响它们的输入，由于SYSDATE和SYSTIMESTAMP实际是调用操作系统底层接口直接返回值。DBTIMEZONE的设置仅仅会影响数据库内两种数据类型的值：一种是TimeStamp with Time Zone，还有一种是TimeStamp with Local Time ...
SYSTIMESTAMP
allway2的博客
08-14 771
SYSTIMESTAMP Syntax Description of the illustration systimestamp.eps Purpose SYSTIMESTAMPreturns the system date, including fractional seconds and time zone, of the system on which the database r...
Oracle Systimestamp 函数
热门推荐
产品经理，程序人生
03-10 2万+
在Oracle/PLSQL中，Systimestamp 函数返回本机数据库上当前系统日期和时间(包括微秒和时区)。 Systimestamp 函数的语法是： systimestamp 应用于： Oracle 9i,Oracle 10g,Oracle 11g 例如： SQL> select systimestamp from dual; SYSTIMESTAMP ----
Oracle函数大全八十二：SYSTIMESTAMP函数
yixiaobing的博客
05-14 1542
函数返回的是数据库服务器的当前时间戳和时区信息，而不是客户端的。函数的具体行为，因此建议查阅您正在使用的Oracle数据库版本的官方文档以获取更详细和准确的信息。函数用于获取当前系统的时间戳，包括日期、时间和时区信息。返回的时间戳包含时区信息，因此可以方便地进行时区转换。可以直接获取当前系统的时间戳，包括日期、时间和时区信息。类型的值，表示当前的完整时间戳以及与之关联的时区。这将返回当前系统时间戳转换为UTC时区后的结果。列的值大于当前时间戳减去一天的所有记录。这将返回一个格式化的字符串，例如。
oracle微秒函数,Oracle Systimestamp 函数
weixin_28805935的博客
04-03 2658
在Oracle/PLSQL中，Systimestamp 函数返回本机数据库上当前系统日期和时间(包括微秒和时区)。Systimestamp 函数的语法是：systimestamp应用于：Oracle 9i,Oracle 10g,Oracle 11g例如：SQL> select systimestamp from dual;SYSTIMESTAMP-----------------------...
DBeaver systimestamp
02-27
### 关于 DBeaver 中 SYSTIMESTAMP 的使用及其潜在问题 在数据库操作中，`SYSTIMESTAMP` 函数返回当前会话的时间戳，包括日期和时间以及时区信息。对于支持此功能的数据库管理系统（DBMS），如 Oracle 数据库，在 ...
/* Result Sets Interface */ #ifndef SQL_CRSR # define SQL_CRSR struct sql_cursor { unsigned int curocn; void *ptr1; void *ptr2; unsigned int magic; }; typedef struct sql_cursor sql_cursor; typedef struct sql_cursor SQL_CURSOR; #endif /* SQL_CRSR */ /* Thread Safety */ typedef void * sql_context; typedef void * SQL_CONTEXT; /* Object support */ struct sqltvn { unsigned char *tvnvsn; unsigned short tvnvsnl; unsigned char *tvnnm; unsigned short tvnnml; unsigned char *tvnsnm; unsigned short tvnsnml; }; typedef struct sqltvn sqltvn; struct sqladts { unsigned int adtvsn; unsigned short adtmode; unsigned short adtnum; sqltvn adttvn[1]; }; typedef struct sqladts sqladts; static struct sqladts sqladt = { 1,1,0, }; /* Binding to PL/SQL Records */ struct sqltdss { unsigned int tdsvsn; unsigned short tdsnum; unsigned char *tdsval[1]; }; typedef struct sqltdss sqltdss; static struct sqltdss sqltds = { 1, 0, }; /* File name & Package Name */ struct sqlcxp { unsigned short fillen; char filnam[16]; }; static struct sqlcxp sqlfpn = { 15, "store_ngskip.pc" }; static unsigned int sqlctx = 2697803; static struct sqlexd { unsigned long sqlvsn; unsigned int arrsiz; unsigned int iters; unsigned int offset; unsigned short selerr; unsigned short sqlety; unsigned int occurs; short *cud; unsigned char *sqlest; char *stmt; sqladts *sqladtp; sqltdss *sqltdsp; unsigned char **sqphsv; unsigned long *sqphsl; int *sqphss; short **sqpind; int *sqpins; unsigned long *sqparm; unsigned long **sqparc; unsigned short *sqpadto; unsigned short *sqptdso; unsigned int sqlcmax; unsigned int sqlcmin; unsigned int sqlcincr; unsigned int sqlctimeout; unsigned int sqlcnowait; int sqfoff; unsigned int sqcmod; unsigned int sqfmod; unsigned int sqlpfmem; unsigned char *sqhstv[12]; unsigned long sqhstl[12]; int sqhsts[12]; short *sqindv[12]; int sqinds[12]; unsigned long sqharm[12]; unsigned long *sqharc[12]; unsigned short sqadto[12]; unsigned short sqtdso[12]; } sqlstm = {13,12}; /* SQLLIB Prototypes */ extern sqlcxt (/*_ void **, unsigned int *, struct sqlexd *, struct sqlcxp * _*/); extern sqlcx2t(/*_ void **, unsigned int *, struct sqlexd *, struct sqlcxp * _*/); extern sqlbuft(/*_ void **, char * _*/); extern sqlgs2t(/*_ void **, char * _*/); extern sqlorat(/*_ void **, unsigned int *, void * _*/); /* Forms Interface */ static int IAPSUCC = 0; static int IAPFAIL = 1403; static int IAPFTL = 535; extern void sqliem(/*_ unsigned char *, signed int * _*/); typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR; typedef struct { unsigned short len; unsigned char arr[1]; } varchar; /* CUD (Compilation Unit Data) Array */ static short sqlcud0[] = {13,4130,1,0,0, 5,0,0,0,0,0,27,60,0,0,4,4,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,10,0,0, 36,0,0,2,0,0,32,64,0,0,0,0,0,1,0, 51,0,0,3,0,0,32,80,0,0,0,0,0,1,0, 66,0,0,4,0,0,31,115,0,0,0,0,0,1,0, 81,0,0,5,71,0,4,120,0,0,1,0,0,1,0,2,97,0,0, 100,0,0,6,174,0,3,159,0,0,7,7,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,97,0,0,1,97,0, 0,1,97,0,0,1,0,0,0, 143,0,0,7,143,0,3,189,0,0,4,4,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,0,0,0, 174,0,0,8,0,0,29,229,0,0,0,0,0,1,0, 189,0,0,9,0,0,31,232,0,0,0,0,0,1,0, 204,0,0,10,0,0,31,258,0,0,0,0,0,1,0, 219,0,0,11,229,0,3,322,0,0,12,12,0,1,0,1,97,0,0,1,3,0,0,1,97,0,0,1,97,0,0,1,97, 0,0,1,97,0,0,1,97,0,0,1,3,0,0,1,97,0,0,1,97,0,0,1,97,0,0,1,0,0,0, }; /****************************************************************************** 受発注連携クライアント DB処理 mod:2018/5/21 SQLエラー時にabortするよう修正 mod:2019/10/7 発注データ内容不正時の処理追加 ******************************************************************************/ #include <sys/types.h> #include <unistd.h> #include <stdio.h> #include <stdlib.h> #include <string.h> /*** add 2019/10/7 start ***/ #include <errno.h> /*** add 2019/10/7 end ***/ #include <sqlda.h> #include <sqlcpr.h> #include "logging.h" #include "bj_onl_trn_client.h" #include "store.h" /*** add 2019/10/7 start ***/ #include "config.h" /*** add 2019/10/7 end ***/ /* procコマンドでエラーが表示されるため _DEBUG_のときのみインクルードする */ #ifdef _DEBUG_ #include "trace.h" #else #define _TRACE_(level, body) #endif /****************************************************************************** ホスト変数 ******************************************************************************/ char dbname[DB_NAME_LEN+1]; char username[DB_USERNAME_LEN+1]; char password[DB_PASSWORD_LEN+1]; char process_name[LEN_PG_ID+1]; struct odr_trn_head trn_head; struct odr_trn_mei trn_mei; /****************************************************************************** sqlca ******************************************************************************/ #include <sqlca.h> /****************************************************************************** 初期化 返り値 0 : OK -1 : エラー ******************************************************************************/ int store_initialize() { _TRACE_(10, ("username [%s] password [%s] dbname [%s]" , username, password, dbname)); /* データベースへの接続 */ /* EXEC SQL CONNECT :username IDENTIFIED BY :password USING :dbname; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )10; sqlstm.offset = (unsigned int )5; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstv[0] = (unsigned char *)username; sqlstm.sqhstl[0] = (unsigned long )21; sqlstm.sqhsts[0] = ( int )21; sqlstm.sqindv[0] = ( short *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned long )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqhstv[1] = (unsigned char *)password; sqlstm.sqhstl[1] = (unsigned long )41; sqlstm.sqhsts[1] = ( int )41; sqlstm.sqindv[1] = ( short *)0; sqlstm.sqinds[1] = ( int )0; sqlstm.sqharm[1] = (unsigned long )0; sqlstm.sqadto[1] = (unsigned short )0; sqlstm.sqtdso[1] = (unsigned short )0; sqlstm.sqhstv[2] = (unsigned char *)dbname; sqlstm.sqhstl[2] = (unsigned long )21; sqlstm.sqhsts[2] = ( int )21; sqlstm.sqindv[2] = ( short *)0; sqlstm.sqinds[2] = ( int )0; sqlstm.sqharm[2] = (unsigned long )0; sqlstm.sqadto[2] = (unsigned short )0; sqlstm.sqtdso[2] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlstm.sqparm = sqlstm.sqharm; sqlstm.sqparc = sqlstm.sqharc; sqlstm.sqpadto = sqlstm.sqadto; sqlstm.sqptdso = sqlstm.sqtdso; sqlstm.sqlcmax = (unsigned int )100; sqlstm.sqlcmin = (unsigned int )2; sqlstm.sqlcincr = (unsigned int )1; sqlstm.sqlctimeout = (unsigned int )0; sqlstm.sqlcnowait = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } _TRACE_(0, ("sqlcode : %d", sqlca.sqlcode)); if(sqlca.sqlcode < 0) { log_printf(LOG_ERROR, "Database connect error %s%", sqlca.sqlerrm.sqlerrmc); /* EXEC SQL ROLLBACK RELEASE; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )36; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } return -1; } return 0; } /****************************************************************************** 終了 返り値 0 : OK ******************************************************************************/ int store_terminate() { _TRACE_(10, ("")); /* データベースからの切断 */ /* EXEC SQL ROLLBACK WORK RELEASE; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )51; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } return 0; } /****************************************************************************** トランザクションの開始 返り値 0 : OK ******************************************************************************/ int store_begin(char *trn_file_id) { _TRACE_(10, ("")); log_printf(LOG_INFO, "START TRN_FILE_ID=%s", trn_file_id); /* 発注キュー及び発注トランヘッダ用ホスト変数の初期化 */ memset(&trn_head, 0, sizeof(trn_head)); /* トランファイルIDをホスト変数へ格納 */ strncpy(trn_head.trn_file_id, trn_file_id, LEN_TRN_FILE_ID); return 0; } /*** add 2019/10/7 start ***/ /****************************************************************************** 発注データINSERTエラー時の処理 ******************************************************************************/ int store_ins_error(char *buffer, size_t size) { char timestamp_buf[18]; FILE *temp_file = NULL; char temp_filename[PATH_MAX]; _TRACE_(10, ("")); _TRACE_(10, ("trn_file_id [%s]", trn_head.trn_file_id)); /* EXEC SQL ROLLBACK WORK; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )66; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } memset(timestamp_buf, 0, sizeof(timestamp_buf)); memset(temp_filename, 0, sizeof(temp_filename)); /* EXEC SQL SELECT TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3') INTO :timestamp_buf FROM DUAL; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.stmt = "select TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3') into :\ b0 from DUAL "; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )81; sqlstm.selerr = (unsigned short)1; sqlstm.sqlpfmem = (unsigned int )0; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstv[0] = (unsigned char *)timestamp_buf; sqlstm.sqhstl[0] = (unsigned long )18; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( short *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned long )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlstm.sqparm = sqlstm.sqharm; sqlstm.sqparc = sqlstm.sqharc; sqlstm.sqpadto = sqlstm.sqadto; sqlstm.sqptdso = sqlstm.sqtdso; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } if(sqlca.sqlcode < 0) { log_printf(LOG_ERROR, "Database session error%s%", sqlca.sqlerrm.sqlerrmc); abort(); } else { /* TEMPファイル出力 */ sprintf(temp_filename, "%s_%s.TEMP.%s" , get_config()->log_file , get_config()->process_name , timestamp_buf); temp_file = fopen(temp_filename, "ab+"); if(temp_file == NULL) { log_printf(LOG_ERROR, "TEMP file open error : [%s] %s(%d)",temp_filename, strerror(errno), errno); abort(); } fwrite(buffer, size, 1, temp_file); fclose(temp_file); log_printf(LOG_WARN, "SKIP TRN_FILE_ID=%s TEMP_FILE=%s", trn_head.trn_file_id, temp_filename); } return 0; } /*** add 2019/10/7 end ***/ /****************************************************************************** 仕上げの処理(発注トランヘッダテーブルと発注キューテーブルの登録) 返り値 0 : OK -1 : エラー ******************************************************************************/ int store_finish() { int retry_count = 0; BOOL sqldone = NO; _TRACE_(10, ("")); /* 発注トランヘッダテーブルへINSERT */ while(!sqldone) { /* EXEC SQL INSERT INTO BJ_T_ODR_TRN_HEAD (TRN_FILE_ID,HCHIKU_HEAD,TENBAN_HEAD, RCV_DATE,RCV_TIME,REC_RGST_DT,REC_RGST_PG_ID,REC_RGST_ID) VALUES (:trn_head.trn_file_id,:trn_head.hchiku_head,:trn_head.tenban_head ,:trn_head.rcv_date,:trn_head.rcv_time,systimestamp,:process_name,:process_name); */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 7; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.stmt = "insert into BJ_T_ODR_TRN_HEAD (TRN_FILE_ID,HCHIKU_HEA\ D,TENBAN_HEAD,RCV_DATE,RCV_TIME,REC_RGST_DT,REC_RGST_PG_ID,REC_RGST_ID) values\ (:b0,:b1,:b2,:b3,:b4,systimestamp,:b5,:b5)"; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )100; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstv[0] = (unsigned char *)(trn_head.trn_file_id); sqlstm.sqhstl[0] = (unsigned long )129; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( short *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned long )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqhstv[1] = (unsigned char *)(trn_head.hchiku_head); sqlstm.sqhstl[1] = (unsigned long )3; sqlstm.sqhsts[1] = ( int )0; sqlstm.sqindv[1] = ( short *)0; sqlstm.sqinds[1] = ( int )0; sqlstm.sqharm[1] = (unsigned long )0; sqlstm.sqadto[1] = (unsigned short )0; sqlstm.sqtdso[1] = (unsigned short )0; sqlstm.sqhstv[2] = (unsigned char *)(trn_head.tenban_head); sqlstm.sqhstl[2] = (unsigned long )6; sqlstm.sqhsts[2] = ( int )0; sqlstm.sqindv[2] = ( short *)0; sqlstm.sqinds[2] = ( int )0; sqlstm.sqharm[2] = (unsigned long )0; sqlstm.sqadto[2] = (unsigned short )0; sqlstm.sqtdso[2] = (unsigned short )0; sqlstm.sqhstv[3] = (unsigned char *)(trn_head.rcv_date); sqlstm.sqhstl[3] = (unsigned long )9; sqlstm.sqhsts[3] = ( int )0; sqlstm.sqindv[3] = ( short *)0; sqlstm.sqinds[3] = ( int )0; sqlstm.sqharm[3] = (unsigned long )0; sqlstm.sqadto[3] = (unsigned short )0; sqlstm.sqtdso[3] = (unsigned short )0; sqlstm.sqhstv[4] = (unsigned char *)(trn_head.rcv_time); sqlstm.sqhstl[4] = (unsigned long )10; sqlstm.sqhsts[4] = ( int )0; sqlstm.sqindv[4] = ( short *)0; sqlstm.sqinds[4] = ( int )0; sqlstm.sqharm[4] = (unsigned long )0; sqlstm.sqadto[4] = (unsigned short )0; sqlstm.sqtdso[4] = (unsigned short )0; sqlstm.sqhstv[5] = (unsigned char *)process_name; sqlstm.sqhstl[5] = (unsigned long )18; sqlstm.sqhsts[5] = ( int )0; sqlstm.sqindv[5] = ( short *)0; sqlstm.sqinds[5] = ( int )0; sqlstm.sqharm[5] = (unsigned long )0; sqlstm.sqadto[5] = (unsigned short )0; sqlstm.sqtdso[5] = (unsigned short )0; sqlstm.sqhstv[6] = (unsigned char *)process_name; sqlstm.sqhstl[6] = (unsigned long )18; sqlstm.sqhsts[6] = ( int )0; sqlstm.sqindv[6] = ( short *)0; sqlstm.sqinds[6] = ( int )0; sqlstm.sqharm[6] = (unsigned long )0; sqlstm.sqadto[6] = (unsigned short )0; sqlstm.sqtdso[6] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlstm.sqparm = sqlstm.sqharm; sqlstm.sqparc = sqlstm.sqharc; sqlstm.sqpadto = sqlstm.sqadto; sqlstm.sqptdso = sqlstm.sqtdso; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } if(sqlca.sqlcode < 0) { log_printf(LOG_ERROR, "Database insert BJ_T_ODR_TRN_HEAD error%s%", sqlca.sqlerrm.sqlerrmc); /*** mod 2019/10/7 start ***/ /* EXEC SQL ROLLBACK WORK;*/ /* if (retry_count < MAX_SQL_RETRY) {*/ /* sleep(SQL_RETRY_TIMER);*/ /* retry_count++;*/ /* } else {*/ /* abort();*/ /* }*/ if (retry_count < MAX_INS_RETRY) { sleep(SQL_RETRY_TIMER); retry_count++; } else { return -1; } /*** mod 2019/10/7 end ***/ } else { sqldone = YES; } } /* 発注キューテーブルへINSERT */ retry_count = 0; sqldone = NO; while(!sqldone) { /* EXEC SQL INSERT INTO BJ_T_HAT_QUETBL (TRN_FILE_ID,TENBAN_HEAD,PROC_FLG,REC_RGST_DT,REC_RGST_PG_ID,REC_RGST_ID) VALUES (:trn_head.trn_file_id,:trn_head.tenban_head,'0',systimestamp,:process_name,:process_name); */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 7; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.stmt = "insert into BJ_T_HAT_QUETBL (TRN_FILE_ID,TENBAN_HEAD,\ PROC_FLG,REC_RGST_DT,REC_RGST_PG_ID,REC_RGST_ID) values (:b0,:b1,'0',systimest\ amp,:b2,:b2)"; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )143; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstv[0] = (unsigned char *)(trn_head.trn_file_id); sqlstm.sqhstl[0] = (unsigned long )129; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( short *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned long )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqhstv[1] = (unsigned char *)(trn_head.tenban_head); sqlstm.sqhstl[1] = (unsigned long )6; sqlstm.sqhsts[1] = ( int )0; sqlstm.sqindv[1] = ( short *)0; sqlstm.sqinds[1] = ( int )0; sqlstm.sqharm[1] = (unsigned long )0; sqlstm.sqadto[1] = (unsigned short )0; sqlstm.sqtdso[1] = (unsigned short )0; sqlstm.sqhstv[2] = (unsigned char *)process_name; sqlstm.sqhstl[2] = (unsigned long )18; sqlstm.sqhsts[2] = ( int )0; sqlstm.sqindv[2] = ( short *)0; sqlstm.sqinds[2] = ( int )0; sqlstm.sqharm[2] = (unsigned long )0; sqlstm.sqadto[2] = (unsigned short )0; sqlstm.sqtdso[2] = (unsigned short )0; sqlstm.sqhstv[3] = (unsigned char *)process_name; sqlstm.sqhstl[3] = (unsigned long )18; sqlstm.sqhsts[3] = ( int )0; sqlstm.sqindv[3] = ( short *)0; sqlstm.sqinds[3] = ( int )0; sqlstm.sqharm[3] = (unsigned long )0; sqlstm.sqadto[3] = (unsigned short )0; sqlstm.sqtdso[3] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlstm.sqparm = sqlstm.sqharm; sqlstm.sqparc = sqlstm.sqharc; sqlstm.sqpadto = sqlstm.sqadto; sqlstm.sqptdso = sqlstm.sqtdso; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } if(sqlca.sqlcode < 0) { log_printf(LOG_ERROR, "Database insert BJ_T_HAT_QUETBL error%s%", sqlca.sqlerrm.sqlerrmc); /*** mod 2019/10/7 start ***/ /* EXEC SQL ROLLBACK WORK;*/ /* if (retry_count < MAX_SQL_RETRY) {*/ /* sleep(SQL_RETRY_TIMER);*/ /* retry_count++;*/ /* } else {*/ /* abort();*/ /* }*/ if (retry_count < MAX_INS_RETRY) { sleep(SQL_RETRY_TIMER); retry_count++; } else { return -1; } /*** mod 2019/10/7 end ***/ } else { sqldone = YES; } } return 0; } /****************************************************************************** トランザクションのコミット 返り値 0 : OK -1 : エラー ******************************************************************************/ int store_commit() { int retry_count = 0; BOOL sqldone = NO; _TRACE_(10, ("")); /* トランザクションのコミット */ while(!sqldone) { /* EXEC SQL COMMIT WORK; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 7; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )174; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } if(sqlca.sqlcode < 0) { log_printf(LOG_ERROR, "Database commit error%s%", sqlca.sqlerrm.sqlerrmc); /* EXEC SQL ROLLBACK WORK; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 7; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )189; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } if (retry_count < MAX_SQL_RETRY) { retry_count++; sleep(SQL_RETRY_TIMER); } else { abort(); } } else { sqldone = YES; } } log_printf(LOG_INFO, "END TRN_FILE_ID=%s", trn_head.trn_file_id); return 0; } /****************************************************************************** トランザクションのアボート 返り値 0 : OK ******************************************************************************/ int store_abort() { _TRACE_(10, ("")); /* トランザクションのアボート */ /* EXEC SQL ROLLBACK WORK; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 7; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )204; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } log_printf(LOG_INFO, "ABORT TRN_FILE_ID=%s", trn_head.trn_file_id); return 0; } /****************************************************************************** 発注データ(ヘッダ部)処理 返り値 0 : OK ******************************************************************************/ int store_add_head(interm_head_def *interm_head) { _TRACE_(10, ("")); /* 値のホスト変数への格納 */ memcpy(trn_head.hchiku_head, interm_head->hchiku, LEN_HCHIKU); memcpy(trn_head.tenban_head, interm_head->tenban, LEN_TENBAN); memcpy(trn_head.rcv_date, interm_head->rcv_date, LEN_RCV_DATE); memcpy(trn_head.rcv_time, interm_head->rcv_time, LEN_RCV_TIME); return 0; } /****************************************************************************** 発注データ(データ部)処理 返り値 0 : OK ******************************************************************************/ int store_add_data(interm_data_def *intinterm_data) { _TRACE_(10, ("")); /* 発注トラン明細用ホスト変数の初期化 */ memset(&trn_mei, 0, sizeof(trn_mei)); /* 値のホスト変数への格納 */ trn_mei.seq_no = intinterm_data->seq_no; memcpy(trn_mei.hchiku, intinterm_data->hchiku, LEN_HCHIKU); memcpy(trn_mei.tenban, intinterm_data->tenban, LEN_TENBAN); memcpy(trn_mei.hat_date, intinterm_data->hat_date, LEN_HAT_DATE); return 0; } /****************************************************************************** 発注データ(データ部)のTEXT部処理 返り値 0 : OK -1 : エラー ******************************************************************************/ int store_add_detail(interm_detail_def *interm_detail) { int retry_count = 0; BOOL sqldone = NO; _TRACE_(10, ("")); /* 値のホスト変数への格納 */ memcpy(trn_mei.hatsk_cd, interm_detail->hatsk_cd, LEN_HATSK_CD); memcpy(trn_mei.shohin_cd, interm_detail->shohin_cd, LEN_SHOHIN_CD); trn_mei.hat_baisu =interm_detail-> hat_baisu; memcpy(trn_mei.pop_kbn, interm_detail->pop_kbn, LEN_POP_KBN); memcpy(trn_mei.hat_type, interm_detail->hat_type, LEN_HAT_TYPE); /* 発注トラン明細テーブルへINSERT */ while(!sqldone) { /* EXEC SQL INSERT INTO BJ_T_ODR_TRN_MEI (TRN_FILE_ID,SEQ_NO,HCHIKU,TENBAN,HAT_DATE ,HATSK_CD,SHOHIN_CD,HAT_BAISU,POP_KBN,HAT_TYPE ,REC_RGST_DT,REC_RGST_PG_ID,REC_RGST_ID) VALUES (:trn_head.trn_file_id,:trn_mei.seq_no,:trn_mei.hchiku,:trn_mei.tenban,:trn_mei.hat_date ,:trn_mei.hatsk_cd,:trn_mei.shohin_cd,:trn_mei.hat_baisu,:trn_mei.pop_kbn,:trn_mei.hat_type ,systimestamp,:process_name,:process_name); */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 13; sqlstm.arrsiz = 12; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.stmt = "insert into BJ_T_ODR_TRN_MEI (TRN_FILE_ID,SEQ_NO,HCHI\ KU,TENBAN,HAT_DATE,HATSK_CD,SHOHIN_CD,HAT_BAISU,POP_KBN,HAT_TYPE,REC_RGST_DT,R\ EC_RGST_PG_ID,REC_RGST_ID) values (:b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,sys\ timestamp,:b10,:b10)"; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )219; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstv[0] = (unsigned char *)(trn_head.trn_file_id); sqlstm.sqhstl[0] = (unsigned long )129; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( short *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned long )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqhstv[1] = (unsigned char *)&(trn_mei.seq_no); sqlstm.sqhstl[1] = (unsigned long )sizeof(int); sqlstm.sqhsts[1] = ( int )0; sqlstm.sqindv[1] = ( short *)0; sqlstm.sqinds[1] = ( int )0; sqlstm.sqharm[1] = (unsigned long )0; sqlstm.sqadto[1] = (unsigned short )0; sqlstm.sqtdso[1] = (unsigned short )0; sqlstm.sqhstv[2] = (unsigned char *)(trn_mei.hchiku); sqlstm.sqhstl[2] = (unsigned long )3; sqlstm.sqhsts[2] = ( int )0; sqlstm.sqindv[2] = ( short *)0; sqlstm.sqinds[2] = ( int )0; sqlstm.sqharm[2] = (unsigned long )0; sqlstm.sqadto[2] = (unsigned short )0; sqlstm.sqtdso[2] = (unsigned short )0; sqlstm.sqhstv[3] = (unsigned char *)(trn_mei.tenban); sqlstm.sqhstl[3] = (unsigned long )6; sqlstm.sqhsts[3] = ( int )0; sqlstm.sqindv[3] = ( short *)0; sqlstm.sqinds[3] = ( int )0; sqlstm.sqharm[3] = (unsigned long )0; sqlstm.sqadto[3] = (unsigned short )0; sqlstm.sqtdso[3] = (unsigned short )0; sqlstm.sqhstv[4] = (unsigned char *)(trn_mei.hat_date); sqlstm.sqhstl[4] = (unsigned long )9; sqlstm.sqhsts[4] = ( int )0; sqlstm.sqindv[4] = ( short *)0; sqlstm.sqinds[4] = ( int )0; sqlstm.sqharm[4] = (unsigned long )0; sqlstm.sqadto[4] = (unsigned short )0; sqlstm.sqtdso[4] = (unsigned short )0; sqlstm.sqhstv[5] = (unsigned char *)(trn_mei.hatsk_cd); sqlstm.sqhstl[5] = (unsigned long )9; sqlstm.sqhsts[5] = ( int )0; sqlstm.sqindv[5] = ( short *)0; sqlstm.sqinds[5] = ( int )0; sqlstm.sqharm[5] = (unsigned long )0; sqlstm.sqadto[5] = (unsigned short )0; sqlstm.sqtdso[5] = (unsigned short )0; sqlstm.sqhstv[6] = (unsigned char *)(trn_mei.shohin_cd); sqlstm.sqhstl[6] = (unsigned long )9; sqlstm.sqhsts[6] = ( int )0; sqlstm.sqindv[6] = ( short *)0; sqlstm.sqinds[6] = ( int )0; sqlstm.sqharm[6] = (unsigned long )0; sqlstm.sqadto[6] = (unsigned short )0; sqlstm.sqtdso[6] = (unsigned short )0; sqlstm.sqhstv[7] = (unsigned char *)&(trn_mei.hat_baisu); sqlstm.sqhstl[7] = (unsigned long )sizeof(int); sqlstm.sqhsts[7] = ( int )0; sqlstm.sqindv[7] = ( short *)0; sqlstm.sqinds[7] = ( int )0; sqlstm.sqharm[7] = (unsigned long )0; sqlstm.sqadto[7] = (unsigned short )0; sqlstm.sqtdso[7] = (unsigned short )0; sqlstm.sqhstv[8] = (unsigned char *)(trn_mei.pop_kbn); sqlstm.sqhstl[8] = (unsigned long )3; sqlstm.sqhsts[8] = ( int )0; sqlstm.sqindv[8] = ( short *)0; sqlstm.sqinds[8] = ( int )0; sqlstm.sqharm[8] = (unsigned long )0; sqlstm.sqadto[8] = (unsigned short )0; sqlstm.sqtdso[8] = (unsigned short )0; sqlstm.sqhstv[9] = (unsigned char *)(trn_mei.hat_type); sqlstm.sqhstl[9] = (unsigned long )3; sqlstm.sqhsts[9] = ( int )0; sqlstm.sqindv[9] = ( short *)0; sqlstm.sqinds[9] = ( int )0; sqlstm.sqharm[9] = (unsigned long )0; sqlstm.sqadto[9] = (unsigned short )0; sqlstm.sqtdso[9] = (unsigned short )0; sqlstm.sqhstv[10] = (unsigned char *)process_name; sqlstm.sqhstl[10] = (unsigned long )18; sqlstm.sqhsts[10] = ( int )0; sqlstm.sqindv[10] = ( short *)0; sqlstm.sqinds[10] = ( int )0; sqlstm.sqharm[10] = (unsigned long )0; sqlstm.sqadto[10] = (unsigned short )0; sqlstm.sqtdso[10] = (unsigned short )0; sqlstm.sqhstv[11] = (unsigned char *)process_name; sqlstm.sqhstl[11] = (unsigned long )18; sqlstm.sqhsts[11] = ( int )0; sqlstm.sqindv[11] = ( short *)0; sqlstm.sqinds[11] = ( int )0; sqlstm.sqharm[11] = (unsigned long )0; sqlstm.sqadto[11] = (unsigned short )0; sqlstm.sqtdso[11] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlstm.sqparm = sqlstm.sqharm; sqlstm.sqparc = sqlstm.sqharc; sqlstm.sqpadto = sqlstm.sqadto; sqlstm.sqptdso = sqlstm.sqtdso; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } if(sqlca.sqlcode < 0) { log_printf(LOG_ERROR, "Database insert BJ_T_ODR_TRN_MEI error%s%", sqlca.sqlerrm.sqlerrmc); /*** mod 2019/10/7 start ***/ /* EXEC SQL ROLLBACK WORK;*/ /* if (retry_count < MAX_SQL_RETRY) {*/ /* sleep(SQL_RETRY_TIMER);*/ /* retry_count++;*/ /* } else {*/ /* abort();*/ /* }*/ if (retry_count < MAX_INS_RETRY) { sleep(SQL_RETRY_TIMER); retry_count++; } else { return -1; } /*** mod 2019/10/7 end ***/ } else { sqldone = YES; } } return 0; } /****************************************************************************** 締め処理(締め処理用の値を設定する) 返り値 0 : OK ******************************************************************************/ int store_order_closing(char *trn_file_id) { _TRACE_(10, ("")); _TRACE_(10, ("trn_file_id [%s]", trn_file_id)); /* 発注キュー及び発注トランヘッダ用ホスト変数の初期化 */ memset(&trn_head, 0, sizeof(trn_head)); /* トランファイルIDのホスト変数への格納 */ strncpy(trn_head.trn_file_id, trn_file_id, LEN_TRN_FILE_ID); /* それ以外の値へのスペースの格納 */ memset(trn_head.hchiku_head, ' ', LEN_HCHIKU); memset(trn_head.tenban_head, ' ', LEN_TENBAN); memset(trn_head.rcv_date, ' ', LEN_RCV_DATE); memset(trn_head.rcv_time, ' ', LEN_RCV_TIME); return 0; } /****************************************************************************** プログラムIDを設定する ******************************************************************************/ void store_set_process_name(const char *name) { _TRACE_(10, ("name [%s]", name)); /* プログラムIDのホスト変数への格納 */ strncpy(process_name, name, LEN_PG_ID); } /****************************************************************************** データベース名を設定する ******************************************************************************/ void store_set_name(const char *db_name) { _TRACE_(0, ("db_name [%s]", db_name)); /* データベース名のホスト変数への格納 */ strncpy(dbname, db_name, DB_NAME_LEN); } /****************************************************************************** ユーザとパスワードを設定する ******************************************************************************/ void store_set_user(const char *db_user, const char *db_password) { _TRACE_(0, ("db_user [%s]", db_user)); _TRACE_(0, ("db_password [%s]", db_password)); /* ユーザのホスト変数への格納 */ strncpy(username, db_user, DB_USERNAME_LEN); /* パスワードのホスト変数への格納 */ strncpy(password, db_password, DB_PASSWORD_LEN); } oracle转postgre 这个文件怎么改
最新发布
08-22
VALUES (:trn_head.trn_file_id, :trn_head.hchiku_head, :trn_head.tenban_head, :trn_head.rcv_date, :trn_head.rcv_time, systimestamp, :process_name, :process_name); ``` PostgreSQL（使用 libpq）实现： `...
pg_systimestamp() 不存在
05-09
好的，我现在需要解决用户在PostgreSQL中遇到的pg_systimestamp()函数不存在的问题。首先，我得确认用户提到的pg_systimestamp()是否是PostgreSQL内置的函数。根据我的知识，PostgreSQL并没有这个函数，可能用户是从...
oracle中systimestamp在opengauss中用什么替代
03-04
嗯，用户现在问的是在Oracle中的SYSTIMESTAMP函数在OpenGauss中应该用什么替代。我需要先确认这两个数据库系统中时间函数的不同之处。 首先，Oracle的SYSTIMESTAMP返回的是当前数据库服务器的系统日期和时间，包括...
Python-时间的基本操作
WRY的博客
11-02 255
""" 对时间的操作 DateTime 正常使用datetime 对象 ,如要sleep 类似操作 使用time,如要对时间进行加减操作使用 timedelta 时间操作相关介绍:https://www.cnblogs.com/jfl-xx/p/8024596.html """ from datetime import datetime, date, time, timedelta import time as time2 # 正常获取当前时间 print(datetime.now()) # 自定义时间
mysql systimestamp_操作系统的时区设置会影响数据库查询SYSDATE和SYSTIMESTAMP的值
weixin_39711867的博客
01-19 252
SYSDATE和SYSTIMESTAMP的并不受数据库参数DBTIMEZONE的影响，操作系统时区的环境变量(如TZ)会影响它们的输入，因为SYSDATE和SYSTIMESTAMP实际是调用操作系统底层接口直接返回。 DBTIMEZONE的设置只会影响数据库内两种数据类型的：一种是TimeStamp with TimeSYSDATE和SYSTIMESTAMP的值并不受数据库参数DBTIMEZONE...
MySQL常用操作
java界的小学生
08-14 1004
MySQL常用查询 在日常工作中，大部分项目采用的是MySQL作为数据库支撑，下面总结日常需要用到的一些比较实用的查询，后续会不断补充，感兴趣的可以留言好用的查询语句，与君共勉。 1. 查询数据库所有表明和对应的字段名称 select table_name, column_name from information_schema.columns where table_schema='db_nam...
Oracle systimestamp 平台依赖
FlySpace随笔
04-09 2042
Oracle 内置函数 systimestamp  依赖于操作系统类型 Win 32 可以返回毫秒值，最后3位始终为零；受Windows 内核限制。 Linux  可以返回微妙值，最后3位不为零。 
获取系统时间及Timestamp和String互转
〆`杨陆原じ₯㎕
11-11 309
【代码】获取系统时间及Timestamp和String互转。
设置时间为timestamp格式或者date格式，且为特定时间
yaliangirl的博客
06-24 1404
设置时间为timestamp格式，且为现在的时间的6个小时后：set CD_TIME=systimestamp+0.25； 设置时间为timestamp格式，且为现在的时间的3个小时后：set CD_TIME=systimestamp+0.125； 设置时间为timestamp格式，且为现在的时间的1天后：set CD_TIME=systimestamp+1； 设置时间为date格式，且为现在的时间的6个小时后：set CD_TIME=sysdate+0.25； 设置时间为date格式，且为现在的时
Timestamp类型浅析
weixin_34120274的博客
01-04 354
Oracle针对不同的数据需求，提供了多种类、多层次的数据类型体系。我们在实际应用中，最好可以依据业务数据的实际形态和前端应用的语言、框架特性来确定字段类型的选择。     Date类型是我们经常使用的时间类型数据表示，包括了年月日时分秒信息。作为Date类型的一个拓展，Oracle提供了Timestamp数据类型，作为高精度时间类型的体现。     1、Timestamp的高精度 ...
mysql中systimestamp_oracle数据库中timestamp是什么数据类型
weixin_30983965的博客
02-18 2422
展开全部oracle数据类型看起来非常简单，但用起来会发现有许多32313133353236313431303231363533e78988e69d8331333363373038知识点，本文是我对ORACLE日期数据类型的一些整理，都是开发入门资料，与大家分享：注：由于INTERVAL及TIME ZONE实际用得比较少，所以本文内容未涉及这两个方面。1、常用日期型数据类型1.1、DATE这是OR...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值