Hi:

I have a client (a very good client) who is still using an application I wrote for him in Delphi and DBISAM V3 Client/Server. For various reasons he doesn't want to upgrade his system (the latest version of his software operates somewhat differently) and he's run across a problem I'm trying to help him with.

In particular, in one of his tables, he needs to batch change one of his fields as follows: the field contains strings of the form "S99999" and I need to change them to "S099999"; i.e. insert a "0" after the "S" and before the numbers. The oldest version of Delphi/DBISAM I have is BDS2008 with DBISAM 4.2x. I tried to write a small program to do the update but the DBISAMTable component won't open the file (reports, understandably, that it's the wrong version). The only V3 stuff I have is DBSYS and SRVADMIN. Is there any way using DBSYS that I could maybe write a SQL statement to do what I need? I can't think of any but before I tell my client he's out of luck I thought I'd at least try asking on the DBISAM boards (I've also thought of upgrading the table, doing the change then downgrading, until I read that you can't downgrade).

Thanks for any thoughts.


Norman

On 3/17/2013 3:16 PM, Norman Kleinberg wrote:
> he needs to batch change one of his fields as follows: the field contains strings of the form "S99999" and I need to change them to "S099999"; i.e. insert a "0" after the "S" and before the numbers. <...> The only V3 stuff I have is DBSYS and SRVADMIN. Is there any way using DBSYS that I could maybe write a SQL statement to do what I need?
>

Norman,

Using DBSYS this is fairly trivial.

I do not recall what SQL functions v3 had available but i'm pretty sure
SUBSTRING existed as did REPLACE so you try one of the following:

If S only appears as the first character of your column then something
like this might be easiest (mytable is table name and myfield is the
field with value you want to change):

UPDATE mytable SET myfield = REPLACE("S" WITH "S0" IN myfield)

If that does not work for you (if S can be later in the value as well)
then something like this (i'm cheating and assuming your values are no
more than 11 char long for 2nd substring):

UPDATE mytable SET myfield =  SUBSTRING(myfield FROM 1 FOR 1) + "0" +
SUBSTRING(myfield FROM 2 FOR 10)

You need to check syntax and quotes and such as above was written from
memory.

Raul

NB! If you're a licensed user of DBISAM v4 then download section also
should contain older v3 build in there.

Norman,

Yes, you can use DbSys V3 and SQL to do it:

UPDATE Table1 SET Field1 = SUBSTRING(Field1 FROM 1 FOR 1) + '0' + SUBSTRING(Field1 FROM 2)

--
Fernando Dias
[Team Elevate]

Raul, Fernando:

Thanks to both of you for taking the time to reply.

Not knowing what SQL functions DBISAM offered (I estimated it would take me more time to look that info up then it would to come up with my own scheme) I actually tried to accomplish what I needed to do by exporting the V3 file to text, emptying the V3 file, upgrading it to V4, importing the text back in, running my Delphi code, exporting the data back out to text and importing it into an empty version of the original file. Whew! I thought this worked until my client called me and told me his memo data was missing: I guess when I emptied the V3 file so I could import things back that also emptied the BLB file. Using the original BLB file didn't work at all, I guess the pointers were messed up. I was about to try to figure out how to export the BLB information when I spied your two posts.

I will give these a try. At least your posts point me in the right direction.

Thanks much again.


Norman

Fernando Dias wrote:

Norman,

Yes, you can use DbSys V3 and SQL to do it:

UPDATE Table1 SET Field1 = SUBSTRING(Field1 FROM 1 FOR 1) + '0' + SUBSTRING(Field1 FROM 2)

--
Fernando Dias
[Team Elevate]