踩了Access数据库的两个坑,吐血中 by oflyhigh

View this thread on steempeak.com
· @oflyhigh ·
$102.73
踩了Access数据库的两个坑,吐血中
之前学习了[MFC中使用Access数据库](https://steemit.com/database/@oflyhigh/mfc-access),以及[MFC中向Access 数据库插入数据](https://steemit.com/database/@oflyhigh/5oc9jp-mfc-access),想着总要学以致用嘛,就打算把自己在STEEM区块链上的所有文章插入到数据库中。

![](https://steemitimages.com/DQmRkLq6rRew3mHfx4vYGWyqpC8wSebLPeC2iZCXAdpuGkR/image.png)
(图源 :[pixabay](https://pixabay.com/))

# 变长文本
原本以为应该是很简单的事情,先用Access建好数据表,然后从steem区块链读数据,再逐条插入即可。因为我关心的只有Title以及文章内容,所以只插入这些数据就好,为了方便定位到文章在steemit.com上的链接,我还插入了URL,当然了,还有文章的创建时间。

为了方便测试,先插入3、5条数据,插入很成功,然而为啥文章数据没插全呢?找了半天,才想起来我建表就是随便点点创建的,没做啥详细设置。一看body字段,我设置的是text类型,默认的长度限制255个字符,难怪被截短了。

因为文章长度很长,又不确定多长,所以我打算设置为变长类型,可是找了半天也没发现什么变长类型,只有一个Memo类型看起来貌似能满足需求。试着将字段类型改成Memo,果然OK了。搜了一下微软的相关网页:[The Memo data type is now called “Long Text”](https://support.office.com/en-us/article/The-Memo-data-type-is-now-called-Long-Text-dffe5e34-953e-4451-a05e-fba5d9b564b5),我这还是Memo应该是我Office版本太旧的缘故(2010)。

# 单引号替换

解决了文章内容被截短的问题,我又开始了插数据,这次使劲插吧。结果程序噼里啪啦的报错,尽管数据大部分都插入成功,但是丢了大概几十篇文章的样子。这让我有些不爽。调试了一下发现是文章内容中的**`'`**和SQL语句中的**`'`**冲突。大概类似这样:
>`insert into posts(title) values('ddd'ddd')`

Microsoft站点上找到一个和我问题类似的帖子,[SQL Statement to insert a single quote i.e., ' into a column of the into MSAccess table](https://social.msdn.microsoft.com/Forums/windows/en-US/fd03edf2-124f-4720-8987-0f477a9f5401/sql-statement-to-insert-a-single-quote-ie-into-a-column-of-the-into-msaccess-table),别人回复中给的解决方法是字符串替换大法。把**`'`**替换成**`''`**(两个单引号)。试着重新跑一遍程序,这次只少插入十几篇文章。

那里又出错了呢?让我再查查,晕,原来我有的帖子标题里也用到了**`'`**,真是郁闷,就不能好好的起标题,愉快地插数据吗?哎,继续我的替换大法,搞定之。

<center>![](https://steemitimages.com/DQmRx3oBr2RyQ3dnTgXBo1pm9GgQEeEy3PojzsyrEKmUE3D/image.png)</center>

# 总结

说了这么多,其实就俩坑

* 变长文本用Memo类型(新版本变成Long Text)否则会被截短。
* 文本中的**`'`**要转义或者替换,否则和SQL语法冲突。

当然了,这两个坑或许有其它更好的解决方法,不过对我而言,好用就好啦,不去深究咯,回头大不了删库跑路。

# 相关链接

* [每天进步一点点:MFC中使用Access数据库](https://steemit.com/database/@oflyhigh/mfc-access)
* [每天进步一点点:MFC中向Access 数据库插入数据](https://steemit.com/database/@oflyhigh/5oc9jp-mfc-access)
* [The Memo data type is now called “Long Text”](https://support.office.com/en-us/article/The-Memo-data-type-is-now-called-Long-Text-dffe5e34-953e-4451-a05e-fba5d9b564b5)
* [SQL Statement to insert a single quote i.e., ' into a column of the into MSAccess table](https://social.msdn.microsoft.com/Forums/windows/en-US/fd03edf2-124f-4720-8987-0f477a9f5401/sql-statement-to-insert-a-single-quote-ie-into-a-column-of-the-into-msaccess-table)
👍  , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , and 60 others
properties (23)
post_id48,685,436
authoroflyhigh
permlinkaccess
categorydatabase
json_metadata"{"links": ["https://steemit.com/database/@oflyhigh/mfc-access", "https://steemit.com/database/@oflyhigh/5oc9jp-mfc-access", "https://pixabay.com/", "https://support.office.com/en-us/article/The-Memo-data-type-is-now-called-Long-Text-dffe5e34-953e-4451-a05e-fba5d9b564b5", "https://social.msdn.microsoft.com/Forums/windows/en-US/fd03edf2-124f-4720-8987-0f477a9f5401/sql-statement-to-insert-a-single-quote-ie-into-a-column-of-the-into-msaccess-table"], "format": "markdown", "app": "steemit/0.1", "image": ["https://steemitimages.com/DQmRkLq6rRew3mHfx4vYGWyqpC8wSebLPeC2iZCXAdpuGkR/image.png"], "tags": ["database", "odbc", "mfc", "cn-programming", "cn"]}"
created2018-05-15 15:42:24
last_update2018-05-15 15:42:24
depth0
children3
net_rshares20,797,203,294,672
last_payout2018-05-22 15:42:24
cashout_time1969-12-31 23:59:59
total_payout_value87.203 SBD
curator_payout_value15.528 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length2,364
author_reputation1,148,153,621,496,884
root_title踩了Access数据库的两个坑,吐血中
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (124)
@liuzg ·
$0.23
神算用mfc会不会被码农 @coder-bts 鄙视
👍  
properties (23)
post_id48,686,815
authorliuzg
permlinkre-oflyhigh-access-20180515t155103060z
categorydatabase
json_metadata"{"app": "busy/2.4.0", "community": "busy", "tags": ["database"]}"
created2018-05-15 15:51:03
last_update2018-05-15 15:51:03
depth1
children1
net_rshares46,841,068,726
last_payout2018-05-22 15:51:03
cashout_time1969-12-31 23:59:59
total_payout_value0.173 SBD
curator_payout_value0.057 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length26
author_reputation499,906,621,111,017
root_title踩了Access数据库的两个坑,吐血中
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (1)
@oflyhigh ·
鄙视啊鄙视啊,就习惯了
properties (22)
post_id48,737,176
authoroflyhigh
permlinkre-liuzg-re-oflyhigh-access-20180515t231358434z
categorydatabase
json_metadata"{"app": "steemit/0.1", "tags": ["database"]}"
created2018-05-15 23:14:03
last_update2018-05-15 23:14:03
depth2
children0
net_rshares0
last_payout2018-05-22 23:14:03
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length11
author_reputation1,148,153,621,496,884
root_title踩了Access数据库的两个坑,吐血中
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
@shine.wong ·
别呀,才开始就想着
> 回头大不了删库跑路

继续继续…………
properties (22)
post_id48,749,817
authorshine.wong
permlinkre-oflyhigh-access-20180516t012524038z
categorydatabase
json_metadata"{"tags": ["database"], "app": "steemit/0.1"}"
created2018-05-16 01:25:33
last_update2018-05-16 01:25:33
depth1
children0
net_rshares0
last_payout2018-05-23 01:25:33
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length31
author_reputation1,373,339,077,399
root_title踩了Access数据库的两个坑,吐血中
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000