postgresql - 使用 Ada Gnatcoll 在 Postgresql 中创建 INSERT 和 UPDATE 查询
问题描述
我已经在我的 Postgresql(在 Slackware 14.2 上运行的 10.10 版)设置上运行了 SELECT 查询,但是在尝试找出 INSERT 和 UPDATE 时有点卡住了。阅读文档,我尝试构建查询,但由于我可能缺乏理解而失败。
我正在寻找的只是允许我使用 Gnatcoll 的最简单的示例。
假设我有一个表“xyzzy”,有两列:整数类型的“id”(自动增量和主键)和文本类型的“术语”。
我的第一个插入可能是:
INSERT INTO xyzzy (term) VALUES ('foo');
之后,更新:
UPDATE xyzzy SET term = 'bar' WHERE id = 1;
Postgres 文档中的示例对于 SELECT 来说已经足够详细了;我只是很难理解 INSERT 和 UPDATE。
感激地收到任何帮助。
更新:我已经走得更远了,现在可以使用以下方法进行基本的插入:
GNATCOLL.SQL.Exec.Execute (My_DB, "INSERT INTO xyzzy (term) VALUES ('bar');");
下一步是弄清楚如何使用 SQL_Query 类型正确执行此操作。
解决方案
给定一个现有的 PostgreSQL 数据库,该数据库以mydb2
使用创建的表命名
个人.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE persons (
person_uid uuid DEFAULT uuid_generate_v4 () PRIMARY KEY,
first_name VARCHAR (20) NOT NULL,
last_name VARCHAR (20) NOT NULL
);
GRANT SELECT ON persons TO deedee;
GRANT INSERT ON persons TO deedee;
GRANT UPDATE ON persons TO deedee;
GRANT DELETE ON persons TO deedee;
然后通过生成所需的 Ada 数据类型
$ gnatcoll_postgres2ada -dbmodel dschema.txt
和
dschema.txt
| TABLE | persons | || The contents of person |
| person_uid | TEXT | PK || Auto-generated id |
| first_name | TEXT | NOT NULL || First name |
| last_name | TEXT | NOT NULL || Last name |
以下代码说明了如何使用SQL_Insert
,SQL_Update
和SQL_Delete
. 请注意,为简单起见,我省略了错误检查(例如,请参见 function GNATCOLL.SQL.Exec.Success
)。
主文件
with Ada.Text_IO; use Ada.Text_IO;
with GNATCOLL.Traces; use GNATCOLL.Traces;
with GNATCOLL.SQL.Postgres; use GNATCOLL.SQL.Postgres;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
with GNATCOLL.VFS; use GNATCOLL.VFS;
with GNATCOLL.SQL.Inspect; use GNATCOLL.SQL.Inspect;
with GNATCOLL.SQL; use GNATCOLL.SQL;
with Database;
procedure Main is
DB_Descr : GNATCOLL.SQL.Exec.Database_Description;
-- Datebase description.
DB : GNATCOLL.SQL.Exec.Database_Connection;
-- Database connection.
procedure Dump_Table;
-- Dumps the content of the table "persons" to standard output.
----------------
-- Dump_Table --
----------------
procedure Dump_Table is
S : constant SQL_Query :=
SQL_Select (Fields => Database.Persons.First_Name & -- 0
Database.Persons.Last_Name, -- 1
From => Database.Persons);
R : Forward_Cursor;
begin
Put_Line ("---------- TABLE ----------");
-- Perform the actual query, show results if OK.
R.Fetch (DB, S);
if Success (DB) then
while Has_Row (R) loop
Put_Line (Value (R, 0) & " " & Value (R, 1));
Next (R);
end loop;
else
Put_Line ("FAILED");
end if;
New_Line;
end Dump_Table;
begin
-- Database description.
DB_Descr := GNATCOLL.SQL.Postgres.Setup
(Database => "mydb2",
User => "deedee",
Host => "localhost",
Password => "xxxx");
-- Database connection.
DB := DB_Descr.Build_Connection;
-- Insert two records.
declare
I1 : constant SQL_Query :=
SQL_Insert (Values => (Database.Persons.First_Name = "John") &
(Database.Persons.Last_Name = "Doe"));
I2 : constant SQL_Query :=
SQL_Insert (Values => (Database.Persons.First_Name = "Jane") &
(Database.Persons.Last_Name = "Doe"));
begin
Execute (Connection => DB, Query => I1);
Execute (Connection => DB, Query => I2);
Dump_Table;
end;
-- Update one of the records.
declare
U : constant SQL_Query :=
SQL_Update (Table => Database.Persons,
Set => (Database.Persons.First_Name = "Johnny"),
Where => (Database.Persons.First_Name = "John") and
(Database.Persons.Last_Name = "Doe" ));
begin
Execute (Connection => DB, Query => U);
Dump_Table;
end;
-- Delete one of the records.
declare
D : constant SQL_Query :=
SQL_Delete (From => Database.Persons,
Where => (Database.Persons.First_Name = "Jane") and
(Database.Persons.Last_Name = "Doe" ));
begin
Execute (Connection => DB, Query => D);
Dump_Table;
end;
-- Commit changes to the database.
Commit (DB);
Free (DB); -- for all connections you have opened
Free (DB_Descr);
end Main;
输出
$ ./obj/main
---------- TABLE ----------
John Doe
Jane Doe
---------- TABLE ----------
Jane Doe
Johnny Doe
---------- TABLE ----------
Johnny Doe
推荐阅读
- asp.net - Azure Front-Door - X-Azure-ClientIP 显示的是 IPV6 地址而不是 IPV4
- windows - WTSQueryUserToken 抛出错误 1008,即使在 LocalSystem 下运行时也是如此
- python - 熊猫在时间上求和
- python - Django:将上传的文件保存在特定目录中
- java - 如何按字母顺序对 ZipFile 内容进行交互?
- python - 在python中拼接二进制图像
- html - 无法在反应中显示 base64 图像?
- ssas - 在多维数据集中添加一列以暗表部署后
- angular - 停止对话框中的保存按钮以关闭 Angular jqxScheduler 中的对话框
- php - 如何使用网络路由