Embedded SQL程序的意外输出

huangapple go评论65阅读模式
英文:

Unexpected output of embedded SQL program

问题

我正在尝试使用ECPG在C中运行嵌入式SQL查询来实现一个绝对基本的玩具示例。这是我的代码:

int main() {

	EXEC SQL WHENEVER NOT FOUND DO BREAK;
	EXEC SQL BEGIN DECLARE SECTION;
	char v_country_iso2[2], v_name[32];
	EXEC SQL END DECLARE SECTION;
    
	// 连接到数据库
	EXEC SQL BEGIN DECLARE SECTION;
	const char *target = "exampledb@localhost";
	const char *user = "demo";
	const char *password = "topsecret";
	EXEC SQL END DECLARE SECTION;
	EXEC SQL CONNECT TO :target USER :user USING :password;
    
	// 声明游标
	EXEC SQL DECLARE c CURSOR FOR
	SELECT country_iso2, name FROM cities LIMIT 3;
    
	// 打开游标
	EXEC SQL OPEN c;
    
	// 循环遍历游标并显示结果
	for (;;) {
    	EXEC SQL FETCH NEXT FROM c INTO :v_country_iso2, :v_name;
    	printf(">>> Country (ISO-2): %s, City: %s\n", v_country_iso2, v_name);
	}
    
	// 清理(关闭游标,提交,断开连接)
	EXEC SQL CLOSE c;
	EXEC SQL COMMIT;
	EXEC SQL DISCONNECT;
    
	return 0;   
}

代码编译和运行都很好。然而,输出如下:

>>> Country (ISO-2): JPTokyo, City: Tokyo
>>> Country (ISO-2): IDJakarta, City: Jakarta
>>> Country (ISO-2): INDelhi, City: Delhi

而期望的输出是:

>>> Country (ISO-2): JP, City: Tokyo
>>> Country (ISO-2): ID, City: Jakarta
>>> Country (ISO-2): IN, City: Delhi

似乎v_country_iso2是国家代码和城市名称的连接,我不知道这是在哪里以及为什么发生的。我确保数据库表中的数据是正确的。而且,如果我只获取国家代码,使用 SELECT country_iso2 FROM cities LIMIT 3;,那么我确实只获取到国家代码。

我正在使用一个具有简单表cities的PostgreSQL数据库,其中只有四列,包括country_iso2和名称。

我确信我在某个地方犯了一个非常愚蠢的错误,但我无法看出来。我已经很久没有接触C/C++了,但代码看起来足够简单。

英文:

I'm trying to implement an absolute basic toy example using ECPG to run an embedded SQL query in C. This is my code:

int main() {

	EXEC SQL WHENEVER NOT FOUND DO BREAK;
	EXEC SQL BEGIN DECLARE SECTION;
	char v_country_iso2[2], v_name[32];
	EXEC SQL END DECLARE SECTION;
    
	// Connect to database
	EXEC SQL BEGIN DECLARE SECTION;
	const char *target = "exampledb@localhost";
	const char *user = "demo";
	const char *password = "topsecret";
	EXEC SQL END DECLARE SECTION;
	EXEC SQL CONNECT TO :target USER :user USING :password;
    
	// Declare cursor
	EXEC SQL DECLARE c CURSOR FOR
	SELECT country_iso2, name FROM cities LIMIT 3;
    
	// Open cursor
	EXEC SQL OPEN c;
    
	// Loop through cursor and display results
	for (;;) {
    	EXEC SQL FETCH NEXT FROM c INTO :v_country_iso2, :v_name;
    	printf(">>> Country (ISO-2): %s, City: %s\n", v_country_iso2, v_name);
	}
    
	// Clean-up (close cursor, commit, disconnect)
	EXEC SQL CLOSE c;
	EXEC SQL COMMIT;
	EXEC SQL DISCONNECT;
    
	return 0;   
}

The code compiles and runs just fine. However, the output is the following

>>> Country (ISO-2): JPTokyo, City: Tokyo
>>> Country (ISO-2): IDJakarta, City: Jakarta
>>> Country (ISO-2): INDelhi, City: Delhi

when the expected output is:

>>> Country (ISO-2): JP, City: Tokyo
>>> Country (ISO-2): ID, City: Jakarta
>>> Country (ISO-2): IN, City: Delhi

It seems that v_country_iso2 is a concatenation of the country code and the city name, and I have no idea where and why this happens. I'm sure the data is correct in the database table. Also, if I only fetch the country code with SELECT country_iso2 FROM cities LIMIT 3; then I get indeed only the country code.

I'm using a PostgreSQL database with a simple table cities with just for columns including country_iso2 and name.

I'm sure that I'm making a really stupid mistake somewhere, but just can see it. I haven't touched C/C++ in ages, but the code looks simple enough.

答案1

得分: 1

问题是由使用%s格式的printf引起的。当您传递一个未终止的字符串时,printf会继续打印超出数组边界的内容。在您的情况下,它碰巧打印了内存中的以下字符串,但它也可能会导致崩溃。要解决这个问题,增加大小,并在从SQL读取2个字符后添加终止符再打印字符串。

v_country_iso2[2] = '\0';

请注意,对于城市,您很可能也应该这样做。

英文:

The problem is caused by printf with %s format. When you pass an unterminated string, printf keeps printing outside the bounds of the array. In your case it happens to print the following string in memory but it could just as easily crash. To fix this increase the size and after reading 2 chars from the SQL add the terminator before printing the string.

v_country_iso2[2] = '
v_country_iso2[2] = '\0';
';

Note that you should most likely do this for city as well.

huangapple
  • 本文由 发表于 2023年7月3日 22:20:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76605637.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定