Avatar of jack niekerk
jack niekerk
Flag for Netherlands asked on

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
ProgrammingSQL* vb6

Avatar of undefined
Last Comment
John Tsioumpris

8/22/2022 - Mon
John Tsioumpris

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 ?
UPDATE stammadresse SET STRASSE  ='AAA',PLZ ='123',ORT = 'Berlin' WHERE <YourCriteria>

Open in new window

jack niekerk

ASKER
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
John Tsioumpris

How about working with a graphical UI like :sql manager lite for interbase and firebird
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
John Tsioumpris

A little rereading showed that you have a UDF named : FB_UDF_PADLEFT
jack niekerk

ASKER
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
John Tsioumpris

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

try this statement
UPDATE stammadresse SET NACHNAME  ='                  bernard niekerk finall'   WHERE ADRESSID  = '0000000009'

Open in new window

jack niekerk

ASKER
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';
John Tsioumpris

this is a bit strange as this function returns a string of 4096 chars long
anyway...can you try
UPDATE stammadresse SET NACHNAME  =FB_UDF_PADLEFT('bernard niekerk finall')   WHERE ADRESSID  = '0000000009'

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
John Tsioumpris

Correction 4096..is the maximum Length allowed..
John Tsioumpris

Again reading the docs

UPDATE stammadresse SET NACHNAME  =ibe_PadLeft('bernard niekerk finall')   WHERE ADRESSID  = '0000000009'

Open in new window

John Tsioumpris

Some more reading...sry never have found similar case
UPDATE stammadresse SET NACHNAME  =ibe_PadLeft('bernard niekerk finall',' ',40)   WHERE ADRESSID  = '0000000009'

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jack niekerk

ASKER
No luck,
John Tsioumpris

How about using the ide to manually change a value ? is this allowed or you still get a message
jack niekerk

ASKER
on this  NACHNAME  =ibe_PadLeft('bernard n

[#1: UPDATE]
Dynamic SQL Error
SQL error code = -804
Function unknown
IBE_PADLEFT
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
John Tsioumpris

Now this is something...i think somehow the library BizerbaUDF has to be declared.
John Tsioumpris

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
jack niekerk

ASKER
i use this (see image)  to test  then in VB6 IDE  both give errosthe console
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

Firebird is a bit picky..check if you need the space removed...but the problem is that IBE_PADLEFT is missing.....
John Tsioumpris

Just run a simple
SELECT *  FROM  stammadresse  WHERE ADRESSID  = '0000000009'
and in the Result Buffer...just try to change the value manually
jack niekerk

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Fabrice Lambert

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.


John Tsioumpris

I think its best to contact them....they surely know more about their product.
jack niekerk

ASKER
ok will send them mail with this issue, keep you informed
thanks for help sofar  Regards
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

@Fabrice should be correct...
John Tsioumpris

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
jack niekerk

ASKER
please sample syntax,   backup we have plenty!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
John Tsioumpris

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
John Tsioumpris

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
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jack niekerk

ASKER
found list of all triggers but none for  stammadresse
all kins of otherstruiggers
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

maybe is named differently...check the records of the table...you are seeing the complete definition of trigger
jack niekerk

ASKER
ok keep you informed,   there  is no way , mayby third party software >?  to force updating a record  if I  can not solve i have to hire someone and give hin copy database ans let him write something to force it    Got mail from Bizerba , no programmer support  .   just sales is their interest
John Tsioumpris

Great support...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
jack niekerk

ASKER
no , very bad ,  you have any interest to develop something (payable)
John Tsioumpris

i would like to take a look ..contact me privately
jack niekerk

ASKER
Email adres please?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

just pm here.... i don't think its allowed to share it here...
jack niekerk

ASKER
John is a real expert in solving this incuding the source needed for VB6 to get it done, great!!!
John Tsioumpris

Thanks Jack
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
jack niekerk

ASKER
we are working on the server version now!
John Tsioumpris

:)