sql - IsNull returns TRUE when inserting empty (NOT NULL) strings in a LONG VARCHAR field (SQL Anywhere)
问题描述
Delphi 10.3.2 enterprise, database ASA (SQL Anywhere 17.0.9.4913).
I have this table
CREATE TABLE string_null(
lo_key integer NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
str_short char(100) NOT NULL DEFAULT '',
str_long long varchar NOT NULL DEFAULT '');
and I want to insert records using FireDAC TFDConnection
and TFDQuery
components.
qry.Insert;
If I assign non-empty strings, everything works properly.
qry.FindField('str_short').Asstring := 'ABC';
qry.FindField('str_long').Asstring := 'XYZ';
If I insert a record using empty strings (empty, not null!)
qry.FindField('str_short').Asstring := ''; // IsNull is FALSE, ok
qry.FindField('str_long').Asstring := ''; // IsNull becomes TRUE (wrong!)
the IsNull
property on the LONG VARCHAR field returns TRUE even if it has been assigned a NOT NULL value (the empty string), while the short field behavior is correct.
(The property FormatOptions.StrsEmpty2Null
for both the connection and the query is FALSE).
Furthermore, when I execute the Post()
method, Delphi raises the exception on the LONG VARCHAR
field:
Field 'str_long' must have a value
If I set
qry.FindField('str_long').Required := FALSE;
qry.Post;
the record is successful inserted in the database, with empty strings values.
To make short a long story: if I insert a record with empty string values in a LONG VARCHAR
field, the IsNull
property returns a wrong value and I have to assign Required := False
in order to get the INSERT
operation executed.
In this small demonstrative application I try to insert
a record with NOT EMPTY strings (and everything works properly)
a record with EMPTY strings.
unit Unit1;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf,
FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.ASA, FireDAC.Phys.ASADef, FireDAC.VCLUI.Wait,
FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client, Vcl.StdCtrls,
FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef, FireDAC.Stan.ExprFuncs, FireDAC.Phys.ODBCBase, FireDAC.Comp.UI;
type
TForm1 = class(TForm)
conn: TFDConnection;
qry: TFDQuery;
memo: TMemo;
procedure FormCreate(Sender: TObject);
private
procedure exec_insert(str_value : string);
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
const
LONG_FIELD = 'str_long';
SHORT_FIELD = 'str_short';
procedure TForm1.exec_insert(str_value : string);
procedure msg(s : string);
begin
memo.Text := memo.Text + s + #13#10
end;
begin
msg('======================');
msg('INSERT VALUE = "' + str_value + '"');
try
qry.Insert;
if qry.FindField(SHORT_FIELD).IsNull then msg('BEFORE assign SHORT field is NULL')
else msg('BEFORE assign SHORT field is NOT NULL');
if qry.FindField(LONG_FIELD).IsNull then msg('BEFORE assign LONG field is NULL')
else msg('BEFORE assign LONG field is NOT NULL');
msg('');
qry.FindField(SHORT_FIELD).AsString := str_value;
qry.FindField(LONG_FIELD).Asstring := str_value;
if qry.FindField(SHORT_FIELD).IsNull then msg('AFTER assign SHORT field is NULL')
else msg('AFTER assign SHORT field is NOT NULL');
if qry.FindField(LONG_FIELD).IsNull then msg('AFTER assign LONG field is NULL')
else msg('AFTER assign LONG field is NOT NULL');
//qry.FindField(LONG_FIELD).Required := FALSE;
qry.Post
except
on e: Exception do msg('EXCEPTION: ' + e.message)
end;
msg('')
end;
procedure TForm1.FormCreate(Sender: TObject);
var
s : string;
begin
conn.params.Text := 'Database=jolly'#$D#$A'User_Name=jop'#$D#$A'Password=jpw'#$D#$A'Server=jolly'#$D#$A'DriverID=ASA'#$D#$A;
conn.FormatOptions.StrsEmpty2Null := FALSE;
conn.Connected := TRUE;
qry.FormatOptions.StrsEmpty2Null := FALSE;
qry.SQL.Text := 'select * from string_null';
qry.UpdateOptions.RequestLive := TRUE;
qry.Active := TRUE;
exec_insert('ABC');
exec_insert('')
end;
end.
object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 299
ClientWidth = 635
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
DesignSize = (
635
299)
PixelsPerInch = 96
TextHeight = 13
object memo: TMemo
Left = 120
Top = 8
Width = 273
Height = 283
Anchors = [akLeft, akTop, akBottom]
TabOrder = 0
end
object conn: TFDConnection
Left = 28
Top = 12
end
object qry: TFDQuery
Connection = conn
Left = 28
Top = 64
end
end
program Project1;
uses
Vcl.Forms,
Unit1 in 'Unit1.pas' {Form1};
{$R *.res}
begin
Application.Initialize;
Application.MainFormOnTaskbar := True;
Application.CreateForm(TForm1, Form1);
Application.Run;
end.
I have not tested the behavior with other databases.
解决方案
推荐阅读
- python - 基于正则表达式模式突出显示数据框中的文本
- r - 在 Shiny R 中使用类似代码(最初有效)再次出现错误
- accessibility - 标签上的 for 和 select 标签上的 aria-labelledby 是否正确?
- mongodb - 如何使用 spring 的 mongo 模板中的 elemMatch 执行 mongo 查询?
- docker - 无法发送 docherize smpt 服务
- office365 - 在使用 office-js 通用 API 运行 SetSelectedDataAsync 时,是否可以取回插入的形状 ID/内联图片 ID?
- r - 上传到 R 时,CSV 文件中出现分号
- javascript - 如何在超测失败时打印请求日志(如请求 url、请求正文、请求 queryParam)?
- c++ - Boost_option 解析配置文件
- python - 在 catplot 子图标题中仅显示列值而不是 column_name = 列值