乐者为王

Do one thing, and do it well.

com.sybase.jdbc2.jdbc.SybSQLException: SQL Anywhere Error -143: Column '@p3' not found

数据库:Sybase ASA 10

在移植PB程序到Java程序时,碰到了如下的嵌入式SQL语句:

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT
    bhr_emp_job.emp_nbr,
    bhr_emp_job.cyr_nyr_flg,
    bhr_emp_job.pay_freq,
    bhr_emp_job.job_cd,
    bhr_emp_job.contr_non_std_flg,
    bhr_emp_job.pay_type
FROM bhr_emp_job
WHEN :as_cal_code <> ''
AND :as_cal_code = bhr_emp_job.cal_cd

转换后的SQL代码如下:

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT
    bhr_emp_job.emp_nbr,
    bhr_emp_job.cyr_nyr_flg,
    bhr_emp_job.pay_freq,
    bhr_emp_job.job_cd,
    bhr_emp_job.contr_non_std_flg,
    bhr_emp_job.pay_type
FROM bhr_emp_job
WHEN ? <> ''
AND ? = bhr_emp_job.cal_cd

结果在执行的时候出现了下面的异常:

1
2
3
4
5
6
7
8
9
10
11
<ERROR>openQuery in org.rsccc.dao.hrs4300.DHrs4300JobcopyDao when executing SELECT DISTINCT
    bhr_emp_job.emp_nbr,
    bhr_emp_job.cyr_nyr_flg,
    bhr_emp_job.pay_freq,
    bhr_emp_job.job_cd,
    bhr_emp_job.contr_non_std_flg,
    bhr_emp_job.pay_type
FROM bhr_emp_job
WHEN ? <> ''
AND ? = bhr_emp_job.cal_cd
com.sybase.jdbc2.jdbc.SybSQLException: SQL Anywhere Error -143: Column '@p3' not found

在Sybase站点上找到的相关资料是Column '%1' not found

Probable cause: You misspelled the name of a column, or the column you are looking for is in a different table.

想到在PB中是在填充变量值后再将SQL语句扔到数据库中执行,而移植后的Java程序中采用的是PreparedStatement来执行SQL语句,所以会出现Column not found的错误。解决方法是将?移到赋值符号的右边:

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT
    bhr_emp_job.emp_nbr,
    bhr_emp_job.cyr_nyr_flg,
    bhr_emp_job.pay_freq,
    bhr_emp_job.job_cd,
    bhr_emp_job.contr_non_std_flg,
    bhr_emp_job.pay_type
FROM bhr_emp_job
WHEN '' <> ?
AND bhr_emp_job.cal_cd = ?

Comments