- SQL
- Databases
- Microsoft SQL Server
- Query Syntax
SQL SYNTAX TO UPDATE A RECORD IN A FIREBIRD FILE SQL DIALECT 3
I have a FIREBIRD data base connected by OBDC
Using VB6 i can call customer data wich is in 2 tables located
first call SELECT * FROM stammkunde where KUNDENNR = ' 7'
will return only the name and phone and of customer and email in that table
and a second key ADRKUNDID value to use in next call
to make second call to other tabel stammadresse
SELECT * FROM stammadresse where ADRESSID = ADRKUNDID
this second table stammadresse contains
STRASSE from (stammadresse street
PLZ (from (stammadresse Zip
ORT (from (stammadresse City
etc.
I need to update in both table the here showed fields with new data
please advise teh syntax for both SQL 's to realize this update in VB6
Thanks
Using VB6 i can call customer data wich is in 2 tables located
first call SELECT * FROM stammkunde where KUNDENNR = ' 7'
will return only the name and phone and of customer and email in that table
and a second key ADRKUNDID value to use in next call
to make second call to other tabel stammadresse
SELECT * FROM stammadresse where ADRESSID = ADRKUNDID
this second table stammadresse contains
STRASSE from (stammadresse street
PLZ (from (stammadresse Zip
ORT (from (stammadresse City
etc.
I need to update in both table the here showed fields with new data
please advise teh syntax for both SQL 's to realize this update in VB6
Thanks
Have you tried the official Documentation : https://firebirdsql.org/refdocs/langrefupd21-update.html
Also is not very clear what you want to update with values coming from where ?
Also is not very clear what you want to update with values coming from where ?
UPDATE stammadresse SET STRASSE ='AAA',PLZ ='123',ORT = 'Berlin' WHERE <YourCriteria>
I tried this in a console program (Interbase Query www.mitec.cz)
SELECT * FROM stammadresse WHERE ADRESSID = '0000000009'
is ok will give
ADRESSNR ADRESSID ANREDE NACHNAME
CHAR(10) CHAR(10) CHAR(40) CHAR(40)
--------------------------
9 0000000009 jack niekerk sample
1 row(s) fetched (1 row(s) listed).
Exec time: 0.00 s / Fetch time: 0.02 s
thus found
then i try this
UPDATE stammadresse SET NACHNAME ='bernard niekerk finall' WHERE ADRESSID = '0000000009'
will give
[#1: UPDATE]
invalid request BLR at offset 139
function FB_UDF_PADLEFT is not defined
module name or entrypoint could not be fout
Or in vb6
works ok:
strSQL = "SELECT * FROM stammadresse WHERE ADRESSID = '0000000009'
Rst.Source = Trim(strSQL)
then the update
error in vb6 also
The arguments are of the wrong type The arguments are of the wrong type, fall outside the permitted range or conflict with each other type, fall outside the allowed range or conflict with each other
err no 3001
SELECT * FROM stammadresse WHERE ADRESSID = '0000000009'
is ok will give
ADRESSNR ADRESSID ANREDE NACHNAME
CHAR(10) CHAR(10) CHAR(40) CHAR(40)
--------------------------
9 0000000009 jack niekerk sample
1 row(s) fetched (1 row(s) listed).
Exec time: 0.00 s / Fetch time: 0.02 s
thus found
then i try this
UPDATE stammadresse SET NACHNAME ='bernard niekerk finall' WHERE ADRESSID = '0000000009'
will give
[#1: UPDATE]
invalid request BLR at offset 139
function FB_UDF_PADLEFT is not defined
module name or entrypoint could not be fout
Or in vb6
works ok:
strSQL = "SELECT * FROM stammadresse WHERE ADRESSID = '0000000009'
Rst.Source = Trim(strSQL)
then the update
error in vb6 also
The arguments are of the wrong type The arguments are of the wrong type, fall outside the permitted range or conflict with each other type, fall outside the allowed range or conflict with each other
err no 3001
this " little rereading showed that you have a UDF named : FB_UDF_PADLEFT"" says nothing to me ?? i am download the mentioned program
if there is something on the market , even if to pay for it, to do the update call by command line , i would be happy
if there is something on the market , even if to pay for it, to do the update call by command line , i would be happy
When you find the "culprit" you could update it no matter where you execute it from...
If its command line then Firebird has its own command line...but since you are using VB6 you just issue the UPDATE command and of you go....
Also the FB_UDF_PADLEFT should be a User Defined Function that you defined it (or someone else for you)...i guess it was to pad with the extra characters to fill the 40 characters
If its command line then Firebird has its own command line...but since you are using VB6 you just issue the UPDATE command and of you go....
Also the FB_UDF_PADLEFT should be a User Defined Function that you defined it (or someone else for you)...i guess it was to pad with the extra characters to fill the 40 characters
try this statement
UPDATE stammadresse SET NACHNAME =' bernard niekerk finall' WHERE ADRESSID = '0000000009'
tried that one , samer error what i do see in the tabel viewer wich means nothing to me is this
DECLARE EXTERNAL FUNCTION FB_UDF_PADLEFT
CSTRING(4096) CHARACTER SET NONE, CSTRING(16) CHARACTER SET NONE, INTEGER
RETURNS CSTRING(4096) CHARACTER SET NONE
ENTRY_POINT 'ibe_PadLeft' MODULE_NAME 'BizerbaUDF';
DECLARE EXTERNAL FUNCTION FB_UDF_PADLEFT
CSTRING(4096) CHARACTER SET NONE, CSTRING(16) CHARACTER SET NONE, INTEGER
RETURNS CSTRING(4096) CHARACTER SET NONE
ENTRY_POINT 'ibe_PadLeft' MODULE_NAME 'BizerbaUDF';
this is a bit strange as this function returns a string of 4096 chars long
anyway...can you try
anyway...can you try
UPDATE stammadresse SET NACHNAME =FB_UDF_PADLEFT('bernard niekerk finall') WHERE ADRESSID = '0000000009'
Again reading the docs
UPDATE stammadresse SET NACHNAME =ibe_PadLeft('bernard niekerk finall') WHERE ADRESSID = '0000000009'
Some more reading...sry never have found similar case
UPDATE stammadresse SET NACHNAME =ibe_PadLeft('bernard niekerk finall',' ',40) WHERE ADRESSID = '0000000009'
on this NACHNAME =ibe_PadLeft('bernard n
[#1: UPDATE]
Dynamic SQL Error
SQL error code = -804
Function unknown
IBE_PADLEFT
[#1: UPDATE]
Dynamic SQL Error
SQL error code = -804
Function unknown
IBE_PADLEFT
Can you take a look here : https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx04-functions.html
and check if the BizerbaUDF.dll (or BizerbaUDF.so if Linux) is present
and check if the BizerbaUDF.dll (or BizerbaUDF.so if Linux) is present
Firebird is a bit picky..check if you need the space removed...but the problem is that IBE_PADLEFT is missing.....
Just run a simple
SELECT * FROM stammadresse WHERE ADRESSID = '0000000009'
and in the Result Buffer...just try to change the value manually
SELECT * FROM stammadresse WHERE ADRESSID = '0000000009'
and in the Result Buffer...just try to change the value manually
these BizerbaUDF.dll are not present, for reading it was never needed, i think its not downloadable somewere due to Bizerba the
manufacturer for this cashdrawer software https://www.bizerba.com/en_us/home/ wll not share that
manufacturer for this cashdrawer software https://www.bizerba.com/en_us/home/ wll not share that
I know little to nothing about Firebird, but:
According to the documentation, it support triggers (https://firebirdsql.org/refdocs/langrefupd21-ddl-trigger.html).
According to your symptoms, it looks like there is a trigger running when you perform an update operation on the table.
The trigger seems to call the
FB_UDF_PADLEFT
function wich does not exist anymore.
You might want to check this.
There is a chance that you could drop the trigger ...if you have a backup you can test it..
and then use the builtin commands of Firebird to perform the padding.
LPAD,RPAD...can pad nicely with the need of an elusive external function
and then use the builtin commands of Firebird to perform the padding.
LPAD,RPAD...can pad nicely with the need of an elusive external function
You can query the RDB$FUNCTIONS table...find the triggers applied to your table...drop the ones that deal with the UPDATE and perform the update ...maybe manipulate the trigger to use one of the above FB functions to substitute its functionality without the need of an external function library
More info here : https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx04-triggers.html
More info here : https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx04-triggers.html
Just run a select on RDB$FUNCTIONS...filter the table..
get the triggers
find the problematic one
delete it or alter or disable :https://firebirdsql.org/refdocs/langrefupd25-ddl-trigger.html
run your update
get the triggers
find the problematic one
delete it or alter or disable :https://firebirdsql.org/refdocs/langrefupd25-ddl-trigger.html
run your update
Quick info....FB can deactivate triggers ...so for testing
then
https://firebirdsql.org/refdocs/langrefupd21-intfunc-lpad.html
probably you will have to make a substitution like (guessing)
ALTER TRIGGER trigger_name INACTIVE;
then
ALTER TRIGGER trigger_name ACTIVE;
https://firebirdsql.org/refdocs/langrefupd21-intfunc-lpad.html
probably you will have to make a substitution like (guessing)
ibe_PadLeft(Cstring,Cstring,Int)
Lpad(String,Int,String)
maybe is named differently...check the records of the table...you are seeing the complete definition of trigger
Premium Content
You need an Expert Office subscription to comment.Start Free Trial