“ where子句”中的未知列“ x” [英] Unknown column 'x' in 'where clause'

问题描述

我正在使用MySql和Delphi。
我在下面的代码中出了什么问题？

I'm using MySql , Delphi. What's my wrong at below code ??

我在where子句中得到未知列'x'
错误->未知列' _code_ehda_konandeh'在 where子句
中，因此我需要它。
i必须在2天后执行此项目
.....

I get "Unknown Column 'x' in where clause" Error --> Unknown column '_code_ehda_konandeh' in 'where clause' I so need it. i have to do this proj for 2 days later please........

   DELIMITER $$

USE `bimarestan`$$

DROP PROCEDURE IF EXISTS `Save_Ehda_Konande`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Save_Ehda_Konande`(
IN _code_ehda_konandeh VARCHAR(50),
IN _tarikh_moarefi VARCHAR(50),
IN _tarikh_tashkil_parvandeh VARCHAR(50),
IN _name VARCHAR(50),
IN _name_khanevadegi VARCHAR(50),
IN _name_pedar VARCHAR(50),
IN _tarikh_tavalod VARCHAR(20),
IN _tahsilat VARCHAR(50),
IN _vaziyat_taahol VARCHAR(50),
IN _noe_bimeh VARCHAR(50),
IN _gorooh_khoon VARCHAR(50),
IN _rezayat_dahandeh VARCHAR(50),
IN _shoghl VARCHAR(50),
IN _address VARCHAR(50),
IN _telphone VARCHAR(50),
OUT _out INT)
BEGIN
IF LTRIM(RTRIM(_code_ehda_konandeh))<>'' THEN
 BEGIN
 SET _out = 0;
  IF NOT EXISTS(SELECT * FROM bimar WHERE `_code_ehda_konandeh` = `code_ehda_konandeh`) THEN
    INSERT INTO `ehda_konandeh` (
code_ehda_konandeh,
tarikh_moarefi,
tarikh_tashkil_parvandeh,
NAME,
name_khanevadegi,
name_pedar,
tarikh_tavalod,
tahsilat,
vaziyat_taahol,
noe_bimeh,
gorooh_khoon,
rezayat_dahandeh,
shoghl,
address,
telphone)
    VALUES(
_code_ehda_konandeh,
_tarikh_moarefi,
_tarikh_tashkil_parvandeh,
_name,
_name_khanevadegi,
_name_pedar,
_tarikh_tavalod,
_tahsilat,
_vaziyat_taahol,
_noe_bimeh,
_gorooh_khoon,
_rezayat_dahandeh,
_shoghl,
_address,
_telphone
);
  ELSE
    SET _out = 1;  
  END IF;
    END; 
END IF;
END$$

DELIMITER ;

---

在MySql上存储Proc运行没有问题，但是当我想向其中发送delphi参数时，我会收到错误消息！

---

that Stored Proc Run at MySql Without any problems but when i want send delphi parameters to that, i get that error!!

这是我的delphi代码：

and this is my delphi code :

if flag_new_edit = 1 then
  sp_sabt.StoredProcName := 'Save_Ehda_Konande'
 else
  sp_sabt.StoredProcName := 'Edit_Ehda_Konande';
   With sp_sabt Do
   Begin
   ParamByName('_code_ehda_konandeh').Value := Trim(txt_code_ehda_konande.Text);
   ParamByName('_tarikh_moarefi').Value := Trim(txt_tarikh_tavalod.Text);
   ParamByName('_tarikh_tashkil_parvandeh').Value := Trim(txt_name_khanevadegi.Text);
   ParamByName('_name').Value := Trim(txt_name.Text);
   ParamByName('_name_khanevadegi').Value := Trim(txt_name_khanevadegi.Text);
   ParamByName('_name_pedar').Value := Trim(txt_name_pedar.Text);
   ParamByName('_tarikh_tavalod').Value := Trim(txt_tarikh_tavalod.Text);
   ParamByName('_tahsilat').Value := Trim(cmb_tahsilat.Text);
   ParamByName('_vaziyat_taahol').Value := Trim(cmb_vaziyat_taahol.Text);
   ParamByName('_noe_bimeh').Value := Trim(cmb_noe_bime.Text);
   ParamByName('_gorooh_khoon').Value := Trim(cmb_gorooh_khoon.Text);
   ParamByName('_rezayat_dahandeh').Value := Trim(txt_rezayat_dahande.Text);
   ParamByName('_shoghl').Value := Trim(txt_shoghl.Text);
   ParamByName('_address').Value := Trim(txt_adress.Text);
   ParamByName('_telphone').Value := Trim(txt_tel.Text);
   ExecProc;
End;

致谢

推荐答案

尽管我指出了您的sql代码中可能出现的错误，但我想提供更多详细信息。

Though I pointed out what the error could be in your sql code, I would like to give more details on it.

在MySql上运行的存储过程没有任何问题，但是将Delphi参数发送给sp时，我得到了错误!! 但是你错了。

That Stored Procedure Runing at MySql WithOut any problems but when send Delphi parameters to sp , i get that Error !! But you are wrong.

有 过程创建中的已知错误 。由于某种原因，引发了运行时异常。
。

存储过程代码的语义未在CREATE时检查。在运行时，将检测到未声明的变量，并为每个对未声明变量的引用生成一条错误消息。但是，SP似乎认为任何引用都代表一列，即使句法上下文将其排除在外。如果执行该步骤，则会导致非常混乱的错误消息。

There is a known bug in your procedure creation. Due to the reason a runtime exception was raised.
Semantics of Stored procedure code is not checked at CREATE time. At runtime, undeclared variables are detected, and an error message is generated for each reference to an undeclared variable. However, SP's seem to believe any reference denotes a column, even though the syntactic context excludes that. This leads to a very confusing error message in case the procedure.

标准测试示例如下所示：

A standard test example is as shown below:

mysql> drop procedure proc_test;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE proc_test()
    -> BEGIN
    ->     select current_day;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

在这里您可以理解

mysql> delimiter ;
mysql> call proc_test();
ERROR 1054 (42S22): Unknown column 'current_day' in 'field list'
mysql>

有了这个，您应该理解 在MySql中运行的存储过程没有任何问题... 是不正确的。

With this you should understand that That Stored Procedure Runing at MySql WithOut any problems ... is not correct.

对查询的快速修复将解决此问题。您提到 我用_前缀定义了输入参数。我不知道该做些什么！ 。如果是真的，那么

更改

A quick fix to your query will resolve the issue. You mentioned that I defined input parameters with _ prefix. I don't know waht i must do !. If that is true, then
change

SELECT * FROM bimar WHERE `_code_ehda_konandeh` = `code_ehda_konandeh`

到

SELECT * FROM bimar WHERE `code_ehda_konandeh` = _code_ehda_konandeh

，它应该可以正常工作。在这里，我假设 code_ehda_konandeh 也是表 bimar 的列。

and it should be working. Here I assumed code_ehda_konandeh is a column of table bimar too.

这篇关于“ where子句”中的未知列“ x”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！