With ucanaccess5.0.1 either via python or the console.sh launcher, I consistently get the following error:
net.ucanaccess.jdbc.UcanaccessSQLException: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 unexpected token: AND required: VALUE
Here is an example table in the access file and example data that causes the issue:
CREATE TABLE `Channel_Normal_Table` (
`Test_ID` INTEGER,
`Data_Point` INTEGER,
`Test_Time` DOUBLE,
`Step_Time` DOUBLE,
`DateTime` DOUBLE,
`Step_Index` INTEGER,
`Cycle_Index` INTEGER,
`Is_FC_Data` INTEGER,
`Current` DOUBLE,
`Voltage` DOUBLE,
`Charge_Capacity` DOUBLE,
`Discharge_Capacity` DOUBLE,
`Charge_Energy` DOUBLE,
`Discharge_Energy` DOUBLE,
`dV/dt` DOUBLE,
`Internal_Resistance` DOUBLE,
`AC_Impedance` DOUBLE,
`ACI_Phase_Angle` DOUBLE,
PRIMARY KEY ( `Test_ID`, `Data_Point` )
) CHARACTER SET 'UTF8';
INSERT INTO `Channel_Normal_Table`(`Test_ID`,`Data_Point`,`Test_Time`,`Step_Time`,`DateTime`,`Step_Index`,`Cycle_Index`,`Is_FC_Data`,`Current`,`Voltage`,`Charge_Capacity`,`Discharge_Capacity`,`Charge_Energy`,`Discharge_Energy`,`dV/dt`,`Internal_Resistance`,`AC_Impedance`,`ACI_Phase_Angle`)
VALUES(3,1,300.0317741715223,299.541194887277,42439.40247685185,1,1,0,0,3.137637,0,0,0,0,0.0001462596,0,0,0),
(3,2,600.0763955656979,599.5858166480613,42439.40594907408,1,1,0,0,3.138294,0,0,0,0,0.0001493698,0,0,0),
(3,3,900.081434966953,899.5908556827076,42439.4094212963,1,1,0,0,3.138459,0,0,0,0,-6.268177e-05,0,0,0),
(3,4,1200.121875557119,1199.631295906265,42439.41289351852,1,1,0,0,3.139116,0,0,0,0,0,0,0,0),
(3,5,1500.146912722062,1499.656333804425,42439.41636574074,1,1,0,0,3.139609,0,0,0,0,0,0,0,0),
(3,6,1800.155976385471,1799.665396734618,42439.41983796296,1,1,0,0,3.139773,0,0,0,0,-7.467654e-05,0,0,0),
(3,7,2100.196447770757,2099.70586885312,42439.42331018519,1,1,0,0,3.140431,0,0,0,0,0,0,0,0),
(3,8,2400.236885428055,2399.746306510418,42439.4267824074,1,1,0,0,3.140595,0,0,0,0,-0.0004344818,0,0,0),
(3,9,2700.24609793454,2699.755518650294,42439.43025462963,1,1,0,0,3.141088,0,0,0,0,0.0002632971,0,0,0),
(3,10,2700.511304032706,2700.020725115069,42439.4302662037,1,1,0,0,3.141088,0,0,0,0,0.0002632971,0,0,0),
(3,11,2700.796365332008,0.265081817865202,42439.43028935185,2,1,0,-1.549392,3.005509,5.483e-12,5.679546262e-05,1.722e-11,0.000174544749343,-0.5108455,0,0,0),
(3,12,2701.685516834828,1.154233687293897,42439.43030092592,2,1,0,-1.549046,2.98924,5.483e-12,0.000439436350364,1.722e-11,0.001321790743707,-0.02045225,0,0,0),
(4,1,300.0226404646186,299.8219818492102,42439.40248842593,1,1,0,0,3.122866,0,0,0,0,0.0001646538,0,0,0),
(4,2,600.0631496105857,599.8624906285687,42439.40596064815,1,1,0,0,3.123359,0,0,0,0,7.479329e-05,0,0,0),
(4,3,900.0724156419196,899.8717566599028,42439.40943287036,1,1,0,0,3.123688,0,0,0,0,0,0,0,0),
(4,4,1200.112816271753,1199.912157656345,42439.41290509259,1,1,0,0,3.124346,0,0,0,0,6.983971e-05,0,0,0),
(4,5,1500.137653268423,1499.936994653014,42439.41637731482,1,1,0,0,3.12451,0,0,0,0,-5.958042e-05,0,0,0),
(4,6,1800.146877506381,1799.946218890972,42439.41984953704,1,1,0,0,3.125167,0,0,0,0,0,0,0,0),
(4,7,2100.187351091318,2099.986692109301,42439.42332175926,1,1,0,0,3.125332,0,0,0,0,0,0,0,0),
(4,8,2400.227830175383,2400.027171193366,42439.42679398148,1,1,0,0,3.125989,0,0,0,0,0.0005079957,0,0,0),
(4,9,2700.205724412479,2700.005065430463,42439.4302662037,1,1,0,0,3.126318,0,0,0,0,-0.000263683,0,0,0),
(4,10,2700.237142905431,0.031064209656103,42439.43027777778,2,1,0,-0.003326768,3.018975,0,2.8706507e-08,0,8.6664233e-08,0,0,0,0),
(4,11,2700.471202673624,0.26512434445807,42439.43028935185,2,1,0,-1.548955,2.994646,0,5.049082513e-05,0,0.000151816700694,-0.1039431,0,0,0),
(4,12,2820.572026468664,120.0068878416104,42439.43168981482,4,2,0,0,3.123359,0,0,0,0,-0.0005849777,0,0,0);
When attempting to open with console.sh, I get the following error:
Cannot execute:CREATE CACHED TABLE CHANNEL_NORMAL_TABLE(TEST_ID INTEGER,DATA_POINT INTEGER,TEST_TIME DOUBLE,STEP_TIME DOUBLE,DATETIME DOUBLE,STEP_INDEX SMALLINT,CYCLE_INDEX SMALLINT,IS_FC_DATA SMALLINT,CURRENT NUMERIC(100,7),VOLTAGE NUMERIC(100,7),CHARGE_CAPACITY DOUBLE,DISCHARGE_CAPACITY DOUBLE,CHARGE_ENERGY DOUBLE,DISCHARGE_ENERGY DOUBLE,"DV/DT" NUMERIC(100,7),INTERNAL_RESISTANCE NUMERIC(100,7),AC_IMPEDANCE NUMERIC(100,7),ACI_PHASE_ANGLE NUMERIC(100,7), check (3.4028235E+38>=CURRENT AND -3.4028235E+38<=CURRENT), check (3.4028235E+38>=VOLTAGE AND -3.4028235E+38<=VOLTAGE), check (3.4028235E+38>="DV/DT" AND -3.4028235E+38<="DV/DT"), check (3.4028235E+38>=INTERNAL_RESISTANCE AND -3.4028235E+38<=INTERNAL_RESISTANCE), check (3.4028235E+38>=AC_IMPEDANCE AND -3.4028235E+38<=AC_IMPEDANCE), check (3.4028235E+38>=ACI_PHASE_ANGLE AND -3.4028235E+38<=ACI_PHASE_ANGLE)) unexpected token: AND required: VALUE
I have tried the python solution, where it is easier to switch out the particular hsqldb jar I'm using, and had the same problem with hsqldb2.5.0, 2.5.1, 2.5.2 and 2.6.1. I changed hsqldb jar by switching the jar in the UCanAccess/lib folder and changing the jar required in the ucanacess_jars in the below:
import pandas as pd
import jaydebeapi
def fetch_data(filepath : str, query : str) -> pd.DataFrame:
ucanaccess_jars = [
"JDBC/UCanAccess/ucanaccess-5.0.1.jar",
"JDBC/UCanAccess/lib/commons-lang3-3.8.1.jar",
"JDBC/UCanAccess/lib/commons-logging-1.2.jar",
"JDBC/UCanAccess/lib/hsqldb-2.5.0.jar", # swap version number here
"JDBC/UCanAccess/lib/jackcess-3.0.1.jar",
]
classpath = ":".join(ucanaccess_jars)
with jaydebeapi.connect(
"net.ucanaccess.jdbc.UcanaccessDriver",
f"jdbc:ucanaccess://{filepath}",
["", ""],
classpath
) as connection:
with connection.cursor() as cursor:
cursor.execute(query, params)
df = pd.DataFrame(cursor.fetchall(), columns=[x[0] for x in cursor.description])
return df
Swapping jars, understandably, breaks the console script.
Any suggestions? Is there a simple tweak to avoid creating the AND check constraints when the cached tables are corrected? Any hints if this is a hsqldb or ucanaccess issue?
