Cannot open dataset. Dynamic SQL Error = -204

Discuss a software development with IBProvider

Cannot open dataset. Dynamic SQL Error = -204

Postby Mike Watson » 11 Feb 2010, 17:25

Can anyone help with this problem?

I cannot open client dataset. When I set the dataset.active = True I receive the following error message:

"Prepare SQL statement failed. Dynamic SQL Error SQL error code = -204 Table unknown MYTABLE At line 1, column 15."

I have converted an Access mdb to Firebird (ver 2.1.3.18185). With IBExpert I can see all the tables and data correctly.

In a Delphi form I am using a TADOConnection and a TADOTable component. The connection component is 'connected' and in the table component I choose the MYTABLE table fronm the drop-down list. When I try to set the TADOTable.Active property to true I receive the error.

Details:
operating system : Windows 7 x64 build 7600

Connection string:
Provider=LCPI.IBProvider.3;Password=masterkey;Persist Security Info=True;User ID=SYSDBA;Location=c:\_work\gaa\firebird\gaa.fdb;
ctype=ASCII;garbage_collect=True;
auto_commit=True;auto_commit_level=65536;bicursor=False;free_threading=True;array_vt_type=True;inner_trans=True;
truncate_char=True;schema_cache=1;support_odbc_query=False;force_prepare_stmt=False;
unicode_mode=False;unicode_stmt=False;clear_param_info=True;auto_param_describe=True;force_param_describe=True;
named_param_prefix=:;ignore_unprepare=True;exec_sp_named_param=False;odbc_call_sp=2;auto_commit_ddl=0;
auto_commit_ddl_level=4096;rowset_vm_using=512;nested_trans=False;ado_exec_sp=2;schema_sp_params=2;
ignore_err_param=0;dbclient_type=ib;support_square_delim_name=0;keywords_list=0;tr_abort_flags=0;
array_type=1;reset_ds_tr_rule=1;numeric_i2_rules=0;numeric_i4_rules=0;numeric_i8_rules=0;
dbclient_lock_rules=1;stmt_cache__time=60;stmt_cache__size=1024;stmt_cache__req_life_time=30;
rowset_file__sys_flags=0;rowset__memory_usage

clUseClient
ilUnspecified
cmShareDenyNone

Regards,

Mike Watson
Mike Watson
 
Posts: 2
Joined: 11 Feb 2010, 17:14

Re: Cannot open dataset. Dynamic SQL Error = -204

Postby Kovalenko Dmitry » 11 Feb 2010, 17:52

Mike Watson wrote:Details:
operating system : Windows 7 x64 build 7600

Connection string:
Provider=LCPI.IBProvider.3;Password=masterkey;User ID=SYSDBA;Location=c:\_work\gaa\firebird\gaa.fdb;
ctype=ASCII;auto_commit=True;"

clUseClient
ilUnspecified
cmShareDenyNone


Try to use "Location=localhost:c:\_work\gaa\firebird\gaa.fdb;"

Could you show:
- IBProvider version
- the database charset
- the structure of your table
?
Kovalenko Dmitry
Site Admin
 
Posts: 843
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Re: Cannot open dataset. Dynamic SQL Error = -204

Postby Mike Watson » 11 Feb 2010, 19:17

Hi Dmitry,

Connection string:
Provider=LCPI.IBProvider.3;

I had previously tried it with version 2 of the IBProvider but it gave exactly the same result.

The character set is ASCII. Previously, I had tried with NONE and WIN1251, same result.

Table structure. The table is Immobili. Extract from create script below.

Cheers,
Mike Watson

/* Original table name is "Immobili" */
CREATE TABLE "Immobili" (
"Id" INTEGER NOT NULL /* "Id" */ ,
"Tipo" INTEGER /* "Tipo" */ ,
"Code" VARCHAR(20) /* "Code" */ ,
"Nome" VARCHAR(50) /* "Nome" */ ,
"Condominio" VARCHAR(50) /* "Condominio" */ ,
"Indirizzo" VARCHAR(40) /* "Indirizzo" */ ,
"Civico" VARCHAR(10) /* "Civico" */ ,
"Interno" VARCHAR(10) /* "Interno" */ ,
"Citta" VARCHAR(40) /* "Citta" */ ,
"Prov" CHAR(2) /* "Prov" */ ,
"CAP" VARCHAR(5) /* "CAP" */ ,
"Descr" BLOB SUB_TYPE 1 /* "Descr" */ ,
"Postiletto" INTEGER /* "Postiletto" */ ,
"Locali" INTEGER /* "Locali" */ ,
"Bagni" INTEGER /* "Bagni" */ ,
"Mq" INTEGER /* "Mq" */ ,
"RiscAut" SMALLINT NOT NULL /* "RiscAut" */ ,
"Piano" VARCHAR(10) /* "Piano" */ ,
"PostoAuto" SMALLINT NOT NULL /* "PostoAuto" */ ,
"Box" SMALLINT NOT NULL /* "Box" */ ,
"Ascensore" SMALLINT NOT NULL /* "Ascensore" */ ,
"Arredato" SMALLINT NOT NULL /* "Arredato" */ ,
"Note" BLOB SUB_TYPE 1 /* "Note" */ ,
"web" SMALLINT NOT NULL /* "web" */ ,
"microzona" INTEGER /* "microzona" */ ,
"foglio" INTEGER /* "foglio" */ ,
"mappale" VARCHAR(20) /* "mappale" */ ,
"subalterno" VARCHAR(20) /* "subalterno" */ ,
"categoria" CHAR(4) /* "categoria" */ ,
"classe" INTEGER /* "classe" */ ,
"vani" FLOAT /* "vani" */ ,
"rendita" NUMERIC(15,4) /* "rendita" */ ,
"attivo" SMALLINT NOT NULL /* "attivo" */ ,
"annofab" INTEGER /* "annofab" */ ,
"piscina" SMALLINT NOT NULL /* "piscina" */ ,
"giardino" SMALLINT NOT NULL /* "giardino" */ ,
"DistCentro" INTEGER /* "DistCentro" */ ,
"scala" VARCHAR(10) /* "scala" */ ,
"postoautocond" SMALLINT NOT NULL /* "postoautocond" */ ,
"Lunghezza" FLOAT /* "Lunghezza" */ ,
"Larghezza" FLOAT /* "Larghezza" */ ,
"Altezza" FLOAT /* "Altezza" */ ,
"larghezzaporta" FLOAT /* "larghezzaporta" */ ,
"altezzaporta" FLOAT /* "altezzaporta" */ ,
"creationdate" TIMESTAMP /* "creationdate" */ ,
"ariacondizionata" SMALLINT NOT NULL /* "ariacondizionata" */ ,
"balcone" SMALLINT NOT NULL /* "balcone" */ ,
"camino" SMALLINT NOT NULL /* "camino" */ ,
"disabili" SMALLINT NOT NULL /* "disabili" */ ,
"lastupdate" TIMESTAMP /* "lastupdate" */ ,
"terrazzo" SMALLINT NOT NULL /* "terrazzo" */ ,
"mainfoto" VARCHAR(50) /* "mainfoto" */ ,
"regione" VARCHAR(50) /* "regione" */ ,
"webshowaddr" SMALLINT NOT NULL /* "webshowaddr" */ ,
"cantina" SMALLINT NOT NULL /* "cantina" */ ,
"webshowprices" SMALLINT NOT NULL /* "webshowprices" */ ,
"mansarda" SMALLINT NOT NULL /* "mansarda" */ ,
"lat" FLOAT /* "lat" */ ,
"lng" FLOAT /* "lng" */ ,
"descr1" BLOB SUB_TYPE 1 /* "descr1" */ ,
"descr2" BLOB SUB_TYPE 1 /* "descr2" */ ,
"descr3" BLOB SUB_TYPE 1 /* "descr3" */ ,
"descr4" BLOB SUB_TYPE 1 /* "descr4" */ ,
"gzona" VARCHAR(50) /* "gzona" */ ,
"esp_sud" SMALLINT NOT NULL /* "esp_sud" */ ,
"esp_nord" SMALLINT NOT NULL /* "esp_nord" */ ,
"esp_est" SMALLINT NOT NULL /* "esp_est" */ ,
"esp_ovest" SMALLINT NOT NULL /* "esp_ovest" */ ,
"posizione" INTEGER /* "posizione" */ ,
"portineria" SMALLINT NOT NULL /* "portineria" */ ,
"mqscoperti" INTEGER /* "mqscoperti" */ ,
"naccessori" INTEGER /* "naccessori" */ ,
"distmetro" INTEGER /* "distmetro" */ ,
"distmezzi" INTEGER /* "distmezzi" */ ,
"distautostrada" INTEGER /* "distautostrada" */ ,
"disttreno" INTEGER /* "disttreno" */ ,
"pagmesi" INTEGER /* "pagmesi" */ ,
"caparra" NUMERIC(15,4) /* "caparra" */ ,
"zona" VARCHAR(10) /* "zona" */ ,
"supcat" FLOAT /* "supcat" */ ,
"millesimi" FLOAT /* "millesimi" */ ,
"millesimivscala" FLOAT /* "millesimivscala" */ ,
"millesimirisc" FLOAT /* "millesimirisc" */ ,
"millesimiascensore" FLOAT /* "millesimiascensore" */ ,
"luce_cod" VARCHAR(50) /* "luce_cod" */ ,
"gas_cod" VARCHAR(50) /* "gas_cod" */ ,
"acqua_cod" VARCHAR(50) /* "acqua_cod" */ ,
"caldaia_mat" VARCHAR(50) /* "caldaia_mat" */ ,
"tele" SMALLINT NOT NULL /* "tele" */ ,
"tele_codice" VARCHAR(50) /* "tele_codice" */ ,
"forn_luce" VARCHAR(50) /* "forn_luce" */ ,
"forn_gas" VARCHAR(50) /* "forn_gas" */ ,
"forn_acqua" VARCHAR(50) /* "forn_acqua" */ ,
"forn_tele" VARCHAR(50) /* "forn_tele" */ ,
"admin" INTEGER /* "admin" */ ,
"canonemensile" NUMERIC(15,4) /* "canonemensile" */ ,
"speseforfettarie" NUMERIC(15,4) /* "speseforfettarie" */ ,
"arredo" BLOB SUB_TYPE 1 /* "arredo" */ ,
"costoacqua" NUMERIC(15,4) /* "costoacqua" */ ,
"costofissoacqua" NUMERIC(15,4) /* "costofissoacqua" */ ,
"costofissogas" NUMERIC(15,4) /* "costofissogas" */ ,
"costofissoluce" NUMERIC(15,4) /* "costofissoluce" */ ,
"costogas" NUMERIC(15,4) /* "costogas" */ ,
"costoluce" NUMERIC(15,4) /* "costoluce" */ ,
"dataletturaacqua" TIMESTAMP /* "dataletturaacqua" */ ,
"dataletturagas" TIMESTAMP /* "dataletturagas" */ ,
"dataletturaluce" TIMESTAMP /* "dataletturaluce" */ ,
"dotazione" BLOB SUB_TYPE 1 /* "dotazione" */ ,
"letturaacqua" INTEGER /* "letturaacqua" */ ,
"letturagas" INTEGER /* "letturagas" */ ,
"letturaluce" INTEGER /* "letturaluce" */ ,
"costifissicli" NUMERIC(15,4) /* "costifissicli" */ ,
"costofissotele" NUMERIC(15,4) /* "costofissotele" */ ,
"costotele" NUMERIC(15,4) /* "costotele" */ ,
"dataletturatele" TIMESTAMP /* "dataletturatele" */ ,
"dataletuuraluce" TIMESTAMP /* "dataletuuraluce" */ ,
"letturatele" INTEGER /* "letturatele" */ ,
"notacostifissi" VARCHAR(100) /* "notacostifissi" */ ,
"optcauzione" NUMERIC(15,4) /* "optcauzione" */ ,
"tipologia" VARCHAR(50) /* "tipologia" */ ,
"uffreg" INTEGER /* "uffreg" */ ,
"millesimicomprensorio" FLOAT /* "millesimicomprensorio" */ ,
"paga" SMALLINT NOT NULL /* "paga" */ ,
"annorist" INTEGER /* "annorist" */ ,
"dataacquisto" TIMESTAMP /* "dataacquisto" */ ,
"datareg" TIMESTAMP /* "datareg" */ ,
"notaio" INTEGER /* "notaio" */ ,
"noteacquisto" BLOB SUB_TYPE 1 /* "noteacquisto" */ ,
"raccolta" VARCHAR(20) /* "raccolta" */ ,
"repertorio" VARCHAR(20) /* "repertorio" */ ,
"tipoatto" INTEGER /* "tipoatto" */ ,
"locatorerif" INTEGER /* "locatorerif" */ ,
"elettricista" INTEGER /* "elettricista" */ ,
"fabbro" INTEGER /* "fabbro" */ ,
"idraulico" INTEGER /* "idraulico" */ ,
"muratore" INTEGER /* "muratore" */ ,
"pittore" INTEGER /* "pittore" */ ,
"pulizie" INTEGER /* "pulizie" */ ,
"cond" INTEGER /* "cond" */ ,
"last_ric_anno" INTEGER /* "last_ric_anno" */ ,
"last_ric_n" INTEGER /* "last_ric_n" */ ,
"indirizzocat" VARCHAR(50) /* "indirizzocat" */ ,
"sezioneurbana" VARCHAR(5) /* "sezioneurbana" */ ,
"centrocosto" VARCHAR(20) /* "centrocosto" */ ,
"compgest" INTEGER /* "compgest" */ ,
"compperc" FLOAT /* "compperc" */ ,
"mccond" FLOAT /* "mccond" */ ,
"mqcond" FLOAT /* "mqcond" */ ,
"millesimiacqua" FLOAT /* "millesimiacqua" */ ,
"millesimiportineria" FLOAT /* "millesimiportineria" */ ,
"nudoproprietario" INTEGER /* "nudoproprietario" */ ,
"mp" INTEGER /* "mp" */ ,
"rendicontabile" SMALLINT NOT NULL /* "rendicontabile" */ ,
"certificazioni" BLOB SUB_TYPE 1 /* "certificazioni" */ ,
"searching" SMALLINT NOT NULL /* "searching" */ ,
"valrival" NUMERIC(15,4) /* "valrival" */ ,
"valstorico" NUMERIC(15,4) /* "valstorico" */ ,
"regnum" INTEGER /* "regnum" */ ,
"trasnum" VARCHAR(25) /* "trasnum" */ ,
"regimposta" NUMERIC(15,4) /* "regimposta" */ ,
"regserie" INTEGER /* "regserie" */ ,
"reguff" INTEGER /* "reguff" */ ,
"trasdata" TIMESTAMP /* "trasdata" */ ,
"trasluogo" VARCHAR(50) /* "trasluogo" */ ,
"valacquisto" NUMERIC(15,4) /* "valacquisto" */ ,
"gestcauzione" SMALLINT NOT NULL /* "gestcauzione" */ ,
"compfissa" NUMERIC(15,4) /* "compfissa" */ ,
"baseici" NUMERIC(15,4) /* "baseici" */ ,
"storico" SMALLINT NOT NULL /* "storico" */
);

/* Generators for AutoInc fields for table "Immobili" */
/* SELECT max([Immobili].[Id]) FROM [Immobili] */

CREATE GENERATOR "GEN_Immobili_Id";
SET GENERATOR "GEN_Immobili_Id" TO 429;

SET TERM ^;
CREATE TRIGGER "TRIG_Immobili_BI" FOR "Immobili" BEFORE INSERT
AS BEGIN
IF(NEW."Id" IS NULL) THEN NEW."Id" = GEN_ID("GEN_Immobili_Id",1);
END ^
SET TERM ;^
Mike Watson
 
Posts: 2
Joined: 11 Feb 2010, 17:14

Re: Cannot open dataset. Dynamic SQL Error = -204

Postby Kovalenko Dmitry » 11 Feb 2010, 20:21

You should use a "quote" name in your SQL and in property values:

TableName="Immobili"
Kovalenko Dmitry
Site Admin
 
Posts: 843
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk


Return to Programming with IBProvider

Who is online

Users browsing this forum: No registered users and 3 guests

cron