boolean OCI_API OCI_QueueAlter
(
OCI_Connection *con,
const otext *queue_name,
unsigned int max_retries,
unsigned int retry_delay,
unsigned int retention_time,
const otext *comment
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("BEGIN ")
OTEXT(" DBMS_AQADM.ALTER_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name, ")
OTEXT(" max_retries => :max_retries, ")
OTEXT(" retry_delay => :retry_delay, ")
OTEXT(" retention_time => :retention_time, ")
OTEXT(" comment => :comment ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":max_retries"), &max_retries);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retry_delay"), &retry_delay);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retention_time"), &retention_time);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueTableAlter
(
OCI_Connection *con,
const otext *queue_table,
const otext *comment,
unsigned int primary_instance,
unsigned int secondary_instance
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("BEGIN ")
OTEXT(" DBMS_AQADM.ALTER_QUEUE_TABLE ")
OTEXT(" (")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" comment => :comment, ")
OTEXT(" primary_instance => :primary_instance, ")
OTEXT(" secondary_instance => :secondary_instance ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment: OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":primary_instance"), &primary_instance);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":secondary_instance"), &secondary_instance);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Resultset *rs;
char rowid[OCI_SIZE_ROWID + 1] = "";
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
OCI_Immediate(cn, "select rowid from products where code = 1", OCI_ARG_TEXT, rowid);
OCI_Prepare(st, "select code, name, rowid from products where rowid = :id");
OCI_BindString(st, ":id", rowid, (unsigned int) strlen(rowid));
OCI_Execute(st);
rs = OCI_GetResultset(st);
OCI_FetchNext(rs);
printf("code [%d], name [%s], rowid [%s]", OCI_GetInt(rs, 1), OCI_GetString(rs, 2), OCI_GetString(rs, 3));
OCI_StatementFree(st);
OCI_ConnectionFree(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
double get_estoque(requisicao_estoque *req) {
double saldo;
OCI_Statement *stmt = OCI_StatementCreate(oracledb);
check_ociliberror(stmt);
OCI_Prepare(stmt, "select sum(SALD_CTR) from table(saldo_inicial_tipoest(:emp, :est, :prod, :data, 'S', null, 0))");
OCI_BindShort(stmt, ":emp", &req->empresa);
OCI_BindShort(stmt, ":est", &req->controle);
OCI_BindString(stmt, ":prod", req->produto, 0);
OCI_Date *d = OCI_DateCreate(NULL);
OCI_DateSetDate(d, req->data_ano, req->data_mes, req->data_dia);
OCI_BindDate(stmt, ":data", d);
check_ociliberrorb(OCI_Execute(stmt));
OCI_Resultset *rs = OCI_GetResultset(stmt);
check_ociliberror(rs);
if (OCI_FetchNext(rs)) {
saldo = OCI_GetDouble(rs, 1);
}
else {
saldo = 0.0;
}
OCI_FreeStatement(stmt);
return saldo;
}
boolean OCI_API OCI_QueueStart
(
OCI_Connection *con,
const otext *queue_name,
boolean enqueue,
boolean dequeue
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_enqueue BOOLEAN := FALSE; ")
OTEXT(" v_dequeue BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:enqueue = 1) then ")
OTEXT(" v_enqueue := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" IF (:dequeue = 1) then ")
OTEXT(" v_dequeue := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.START_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name, ")
OTEXT(" enqueue => v_enqueue, ")
OTEXT(" dequeue => v_dequeue ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_BindInt(st, OTEXT(":enqueue"), &enqueue);
call_status = call_status && OCI_BindInt(st, OTEXT(":dequeue"), &dequeue);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueTableDrop
(
OCI_Connection *con,
const otext *queue_table,
boolean force
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_force BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:force = 1) then ")
OTEXT(" v_force := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.DROP_QUEUE_TABLE ")
OTEXT(" (")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" force => v_force ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *)queue_table, 0);
call_status = call_status && OCI_BindInt(st, OTEXT(":force"), &force);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueDrop
(
OCI_Connection *con,
const otext *queue_name
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("BEGIN ")
OTEXT(" DBMS_AQADM.DROP_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueCreate
(
OCI_Connection *con,
const otext *queue_name,
const otext *queue_table,
unsigned int queue_type,
unsigned int max_retries,
unsigned int retry_delay,
unsigned int retention_time,
boolean dependency_tracking,
const otext *comment
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
OCI_CHECK_ENUM_VALUE(con, NULL, queue_type, QueueTypeValues, OTEXT("Queue type"))
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_dependency_tracking BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:dependency_tracking = 1) then ")
OTEXT(" v_dependency_tracking := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.CREATE_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name, ")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" queue_type => :queue_type, ")
OTEXT(" max_retries => :max_retries, ")
OTEXT(" retry_delay => :retry_delay, ")
OTEXT(" retention_time => :retention_time, ")
OTEXT(" dependency_tracking => v_dependency_tracking, ")
OTEXT(" comment => :comment ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":queue_type"), &queue_type);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":max_retries"), &max_retries);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retry_delay"), &retry_delay);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retention_time"), &retention_time);
call_status = call_status && OCI_BindInt(st, OTEXT(":dependency_tracking"), &dependency_tracking);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueTableCreate
(
OCI_Connection *con,
const otext *queue_table,
const otext *queue_payload_type,
const otext *storage_clause,
const otext *sort_list,
boolean multiple_consumers,
unsigned int message_grouping,
const otext *comment,
unsigned int primary_instance,
unsigned int secondary_instance,
const otext *compatible
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_payload_type)
OCI_CHECK_ENUM_VALUE(con, NULL, message_grouping, GroupingModeValues, OTEXT("Grouping mode"))
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_multiple_consumers BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:multiple_consumers = 1) then ")
OTEXT(" v_multiple_consumers := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.CREATE_QUEUE_TABLE ")
OTEXT(" (")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" queue_payload_type => :queue_payload_type, ")
OTEXT(" storage_clause => :storage_clause, ")
OTEXT(" sort_list => :sort_list, ")
OTEXT(" multiple_consumers => v_multiple_consumers, ")
OTEXT(" message_grouping => :message_grouping, ")
OTEXT(" comment => :comment, ")
OTEXT(" primary_instance => :primary_instance, ")
OTEXT(" secondary_instance => :secondary_instance, ")
OTEXT(" compatible => :compatible")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_payload_type"), (otext *) queue_payload_type, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":storage_clause"), (otext *) (storage_clause ? storage_clause : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindString(st, OTEXT(":sort_list"), (otext *) (sort_list ? sort_list : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindInt(st, OTEXT(":multiple_consumers"), &multiple_consumers);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":message_grouping"), &message_grouping);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *)( comment ? comment : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":primary_instance"), &primary_instance);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":secondary_instance"), &secondary_instance);
call_status = call_status && OCI_BindString(st, OTEXT(":compatible"), (otext *) (compatible ? compatible : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
void Statement::bindParam(Param *parameter)
{
Param::ParamType paramType=parameter->getParamType();
DateTime *paramDT;
switch(paramType){
case Param::Integer:
OCI_BindInt(ociStmt,
parameter->getParamName().toStdWString().c_str(),
(int*)parameter->data);
break;
case Param::String:
OCI_BindString(ociStmt,
parameter->getParamName().toStdWString().c_str(),
(dtext*)parameter->data,
parameter->getMaxStringLength());
break;
case Param::Double:
OCI_BindDouble(ociStmt,
parameter->getParamName().toStdWString().c_str(),
(double*)parameter->data);
break;
case Param::Datetime:
paramDT=parameter->getDateTimeValue();
paramDT->setConnection(this->connection);
paramDT->copyToOci();
OCI_BindDate(ociStmt,
parameter->getParamName().toStdWString().c_str(),
paramDT->ociDate());
break;
case Param::Stmt:
{
Statement *paramStmt=parameter->getStmtValue();
paramStmt->setConnection(this->connection);
/*if(useScrollableResultsets){
int res=OCI_SetFetchMode(paramStmt->ociStatement(), OCI_SFM_SCROLLABLE);
qDebug() << "OCI_SetFetchMode for param returned" << res;
}*/
OCI_BindStatement(ociStmt,
parameter->getParamName().toStdWString().c_str(),
paramStmt->ociStatement());
}
break;
case Param::ReturningInto:
OCI_RegisterString(ociStmt, parameter->getParamName().toStdWString().c_str(), 250);
break;
case Param::StringList:
this->bindArrayOfStrings(parameter->getParamName(), (dtext*)parameter->data,
parameter->getMaxStringLength(), parameter->getArraySize());
break;
default:
Q_ASSERT(false);
break;
}
DbUtil::checkForOciError(this);
setParamDirection(parameter);
if(parameter->isNull()){
OCI_BindSetNull(OCI_GetBind2(ociStmt, parameter->getParamName().toStdWString().c_str()));
}
DbUtil::checkForOciError(this);
}
