相关文章推荐
潇洒的牙膏  ·  BTC数据结构 - 掘金·  1 年前    · 
焦虑的脸盆  ·  使用Python从JSON获取值-腾讯云开发 ...·  1 年前    · 
飘逸的打火机  ·  LinkedTreeMap强转实体类_lin ...·  1 年前    · 
刚毅的火柴  ·  RDS ...·  1 年前    · 
聪明的椅子  ·  Kotlin 中的contract ...·  1 年前    · 
Code  ›  Proc 动态SQL实现SQLPlus功能源代码_sqlcolumnnullcheck_「已注销」的博客
char select printf bind
https://blog.csdn.net/kongguoqing791025/article/details/8573816
呐喊的小蝌蚪
1 年前

#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>

#define MAX_ITEMS 40
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

int parse_flag = 0;
jmp_buf jmp_continue;
char * dml_command[] = {"SELECT", "select", "UPDATE", "update", "DELETE", "INSERT", "insert"};
SQLDA * bind_dp;
SQLDA * select_dp;

EXEC SQL BEGIN DECLARE SECTION;
char dyn_statement[1024];
EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;

void sql_error();
int oracle_connect();
int alloc_descriptors(int, int, int);
int get_dyna_statement();
void set_bind_variables();
void process_select_list();
void help();

void main(int argc, char * argv[])
{
int i;
if (oracle_connect() != 0)
exit(1);

if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
exit(1);

while (1)
{
setjmp(jmp_continue);

if (get_dyna_statement() != 0)
break;

EXEC SQL WHENEVER SQLERROR DO sql_error();
parse_flag = 1;

EXEC SQL PREPARE S FROM :dyn_statement;
parse_flag = 0;

EXEC SQL DECLARE C CURSOR FOR S;

set_bind_variables();

EXEC SQL OPEN C USING DESCRIPTOR bind_dp;

process_select_list();

for (i = 0; i < 8; i++)
{
if (strncmp(dyn_statement, dml_command[i], 6) == 0)
{
printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
break;
}
}

}

//释放资源
//printf("start free 1...\n");
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V[i] != (char *) NULL)
free(bind_dp->V[i]);
free(bind_dp->I[i]);

if (select_dp->V[i] != (char *) NULL)
free(select_dp->V[i]);
free(select_dp->I[i]);
}
//printf("free 1 ok!\n");

SQLSQLDAFree(NULL, bind_dp);
SQLSQLDAFree(NULL, select_dp);

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK RELEASE;

puts("\nprogram complete normal!\n");
EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}


void sql_error()
{
printf("\n\n%.70s", sqlca.sqlerrm.sqlerrmc);
if (parse_flag)
printf("parse error at character offset %d in sql statement.\n", sqlca.sqlerrd[4]);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK;
longjmp(jmp_continue, 1);
}

int oracle_connect()
{
char * cp;

EXEC SQL BEGIN DECLARE SECTION;
varchar username[20];
varchar password[40];
EXEC SQL END DECLARE SECTION;
printf("username:");
fflush(stdin);
fgets((char *)username.arr, sizeof(username.arr), stdin);
cp = strchr(username.arr, '\n');
if (cp)
*cp = '\0';
username.len = strlen((char*) username.arr);

printf("password:");
fflush(stdin);
fgets((char*)password.arr, sizeof(password.arr), stdin);
cp = strchr(password.arr, '\n');
if (cp)
*cp = '\0';
password.len = strlen((char*) password.arr);

EXEC SQL WHENEVER SQLERROR GOTO connect_error;
printf("start connect database...\n");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("connect database successfully!\n");
return 0;

connect_error:
printf("can not connect database as user: %.*s\n", username.len, (char*) username.arr);
return -1;

}

int alloc_descriptors(int size, int max_vname_len, int max_iname_len)
{
int i;
bind_dp = SQLSQLDAAlloc(NULL, size, max_vname_len, max_iname_len);
if (bind_dp == (SQLDA*)NULL)
{
printf("can not allocate memory for bind descriptor\n");
return -1;
}
bind_dp->N = MAX_ITEMS;

select_dp = SQLSQLDAAlloc(NULL, size, max_vname_len, max_iname_len);
if (select_dp == (SQLDA *)NULL)
{
printf("can not allocate memory for select descriptor\n");
return -1;
}

select_dp->N = MAX_ITEMS;

for (i = 0; i < MAX_ITEMS; i++)
{
bind_dp->I[i] = (short *) malloc(sizeof(short));
select_dp->I[i] = (short *) malloc(sizeof(short));
bind_dp->V[i] = (char *) malloc(1);
select_dp->V[i] = (char *) malloc(1);
}
return 0;
}

int get_dyna_statement()
{
char * cp, linebuf[256];
int iter, plsql;

for (plsql = 0, iter = 1; ;)
{
if (iter == 1)
{
printf("\nSQL> ");
dyn_statement[0] = '\0';
}

fgets(linebuf, sizeof(linebuf), stdin);
cp = strchr(linebuf, '\n');

if (cp && cp != linebuf)
*cp = '\0';
else if (cp == linebuf)
continue;

if ((strncmp(linebuf, "EXIT", 4) == 0) || (strncmp(linebuf, "exit", 4) == 0))
{
return -1;
}
else if ((linebuf[0] == '?') || (strncmp(linebuf, "help", 4) == 0) || (strncmp(linebuf, "HELP", 4) == 0))
{
help();
iter = 1;
continue;
}

if (strstr(linebuf, "BEGIN") || strstr(linebuf, "begin"))
plsql = 1;

strcat(dyn_statement, linebuf);
if ((plsql && (cp = strchr(dyn_statement, '/'))) || (!plsql && (cp = strchr(dyn_statement, ';'))))
{
*cp = '\0';
break;
}
else
{
iter++;
printf("%3d ", iter);
}
}


return 0;
}

void set_bind_variables()
{
int i, n;
char * cp;
char bind_var[64];
EXEC SQL WHENEVER SQLERROR DO sql_error();
bind_dp->N = MAX_ITEMS;

EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

if (bind_dp->F < 0)
{
printf("\ntoo many bind variables (%d) maximum is %d.\n", -bind_dp->F, MAX_ITEMS);
return;
}
bind_dp->N = bind_dp->F;

for (i = 0; i < bind_dp->F; i++)
{
printf("\nEnter value for bind variables %.*s", (int) bind_dp->C[i], bind_dp->S[i]);
fgets(bind_var, sizeof(bind_var), stdin);
cp = strchr(bind_var, '\n');
if (cp)
*cp = '\0';
n = strlen(bind_var);
bind_dp->L[i] = n;
bind_dp->V[i] = (char*) realloc(bind_dp->V[i], bind_dp->L[i]);
strncpy(bind_dp->V[i], bind_var, n);
if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) || (strncmp(bind_dp->V[i], "null", 4) == 0))
*(bind_dp->I[i]) = -1;
else
*(bind_dp->I[i]) = 0;
bind_dp->T[i] = 1;
}
}

void process_select_list()
{
int i, null_ok, precision, scale;
char title[MAX_VNAME_LEN];

if ((strncmp(dyn_statement, "SELECT", 6) != 0) && (strncmp(dyn_statement, "select", 6) != 0))
{
select_dp->F = 0;
return;
}

select_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

if (select_dp->F < 0)
{
printf("\nToo many select_list items (%d) maximum is %d\n", -select_dp->F, MAX_ITEMS);
return;
}

select_dp->N = select_dp->F;
printf("\n");

for (i = 0; i < select_dp->F; i++)
{
SQLColumnNullCheck(NULL, &(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
switch (select_dp->T[i])
{
case 1: break;  //VARCHAR2
case 2: //NUMBER
SQLNumberPrecV6(NULL, &(select_dp->L[i]), &precision, &scale);
if (precision == 0)
precision = 40;
if (scale > 0)
select_dp->L[i] = sizeof(float);
else
select_dp->L[i] = sizeof(int);
break;

case 8: //LONG
select_dp->L[i] = 240;
break;
case 11: //ROW ID
select_dp->L[i] = 18;
break;
case 12: //DATE
select_dp->L[i] = 9;
break;
case 23: //RAW
break;
case 24: //LONG RAW
select_dp->L[i] = 240;
break;
}

if (select_dp->T[i] != 2)
select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i] + 1);
else
select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i]);

memset(title, '\0', MAX_VNAME_LEN);
strncpy(title, select_dp->S[i], select_dp->C[i]);

if (select_dp->T[i] == 2)
if (scale > 0)
printf("%-*.*s ", select_dp->L[i] + 3, select_dp->C[i], title);
else
printf("%-*.*s ", select_dp->L[i], select_dp->C[i], title);
else
printf("%-*.*s ", select_dp->L[i], select_dp->C[i], title);

if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
select_dp->T[i] = 1;
else if (select_dp->T[i] == 2)
{
if (scale > 0)
select_dp->T[i] = 4;     //float 类型
else
select_dp->T[i] = 3;     //int 类型
}
}
printf("\n\n");

EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;

while (1)
{
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
for (i = 0; i < select_dp->F; i++)
{
if (*(select_dp->I[i]) < 0)
{
if (select_dp->T[i] == 4)
printf("%-*c", (int) select_dp->L[i] + 3, '\0');
else
printf("%-*c", (int) select_dp->L[i], '\0');
}
else
{
if (select_dp->T[i] == 3)  //integer
printf("%-*d ", (int) select_dp->C[i] > (int) select_dp->L[i] ? (int) select_dp->C[i] : (int) select_dp->L[i], *((int*) select_dp->V[i]));
else if (select_dp->T[i] == 4) //float
printf("%-*.2f ", (int) select_dp->C[i] > (int) select_dp->L[i] ? (int) select_dp->C[i] : (int) select_dp->L[i], *((float*) select_dp->V[i]));
else //string
printf("%-*.*s ", (int) select_dp->L[i], (int) select_dp->L[i], select_dp->V[i]);

}
}
printf("\n");
}

end_select_loop:
return;
}

void help()
{
puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
puts("Statements can be continued over several lines, except");
puts("within string literals.");
puts("Terminate a SQL statement with a semicolon.");
puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
puts("with a slash (/).");
puts("Typing \"exit\" (no semicolon needed) exits the program.");
puts("You typed \"?\" or \"help\" to get this message.\n\n");
}

#include #include #include #include #include #include #include #define MAX_ITEMS 40#define MAX_VNAME_LEN 30#define MAX_INAME_LEN 30int parse_flag = 0;jmp_buf jmp_continue;char
代码如下: ALTER proc [dbo].[sp_common_paypal_AddInfo] ( @paypal sql varchar(max),–不包含用户表的paypal sql 语句 @paypaluser sql varchar(max),–paypal用户表的 sql 语句 @ebay sql varchar(max),–不包含用户表的ebay sql 语句 @ebayuser sql varchar(max),–ebay的用户表 sql 语句 @paypaluserwhere varchar(max),–paypal用户表查询ID语句 @ebayuserwhere varchar(max),–eb
SQL 完整性约束引言1.创建表时添加约束 ☆1）写法1：使用列级约束 √2）写法2：使用表级约束2.修改表时添加约束：Alter table ...3.删除约束：ALTER TABLE注意1.自增长列2.联合主键 完整性约束：用于限制字段的值必须满足一定的条件，从而保证数据表中的数据的一致性和完整性！！！ 1.常见的约束： NOT NULL ：非空 要求非空字段为必填项 DEFAUL...
以上就是题目的要求，我们该如何该如何使用嵌入式 SQL 语言 实现 呢？？？如果你是小白请跟随我的脚步，我会用最简单方式教您怎么 实现 ，如果不是我若有不足的地方请多多指教，我们一块儿学习(比心~) 第二：解决问题 小白请跟随我的脚步： 声明：我用的编译器是DEVC++去 实现 的 首先头文件的声明： #include<stdio.h> #include<stdlib.h>
基本概念在有些情况下， 在编码时 SQL 语句还不能完整地写出来， 而是在程序执行时才能 构造出来，这种在程序执行临时生成的 SQL 语句叫动态 SQL 语句. 利用动态 SQL 来 编写 Pro*C 程序的方法叫动态 SQL 技术！ 目的：加强应用程序的 功能 和灵活 静态 SQL —- 在编写应用程序时，使用EXEC SQL 关键字直接嵌入的 SQL 语句；在 proc 编译应用程序生成c语言的时，都已经确
/root/go/pkg/mod/github.com/alexbrainman/odbc@v0.0.0-20210605012845-39f8520b0d5f/api/api_unix.go:14:18: fatal error: sql .h: No such file or directory // #include < sql .h> compilation termina
SQL CA的说明     执行PRO*C程序时, ORACLE把每个 SQL 语句执行的状态信息存入到 SQL CA中, 这些信息包扩错误代码、警告标志、诊断正文和处理行数。因此返回的信息来检测 SQL 语句的执行情况。 SQL CA的组成     PRO*C程序中的 SQL CA时C语言中的一个结构变量, 组成为:     struct sql ca          char sql ca
最近准备ORACLE的作业，其中有一题要求模糊，不管怎么，我用了一个看似很麻烦的方法完成了这样一件事： 输入一个表名，输出它的列名类型，然后输出所有的里面的值。 具体做法使用了 动态SQL 里面的最麻烦的方法，即使用 SQL DA，网上的使用例子很少，所以我把自己写的贴上来，希望能给像我一样的PRO*C初学者一点启发，其中肯定有不足之处，还望高手能不吝赐教。 [code="pro*c"]#inclu...
with open(os.path.join('/ proc ', pid, 'cmdline'), 'rb') as f: cmdline = f.read().decode().replace('\x00', ' ') with open(os.path.join('/ proc ', pid, 'stat'), 'rb') as f: stat = f.read().decode() stat = stat.split(' ') stat = [s for s in stat if s] pid, comm, state = stat[], stat[1], stat[2] print(f'{pid}\t{state}\t{cmdline}') except (FileNotFoundError, PermissionError):
 
推荐文章
潇洒的牙膏  ·  BTC数据结构 - 掘金
1 年前
焦虑的脸盆  ·  使用Python从JSON获取值-腾讯云开发者社区-腾讯云
1 年前
飘逸的打火机  ·  LinkedTreeMap强转实体类_linkedtreemap 怎么强转_youxiu_123的博客-CSDN博客
1 年前
刚毅的火柴  ·  RDS MySQL空间优化最佳实践-阿里云开发者社区
1 年前
聪明的椅子  ·  Kotlin 中的contract 到底有什么用？ - 知乎
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号