Solved

Delphi and sql

Posted on 2001-06-21
20
154 Views
Last Modified: 2010-04-06
I like to make some copies of some sql records.
I like to do this whitout storing the information in a temporary variable.

say that I have a table test with the records
a b c d e f.

I like to do a select * from test where a=1
and then insert a new (or more than one if the select return more than one answer)
record with a=2 and b,c,d,e and f should be the same as in the result from the select above.

Can someone help me out with this....
0
Comment
Question by:pucko
  • 9
  • 7
  • 3
  • +1
20 Comments
 
LVL 9

Expert Comment

by:ITugay
ID: 6214338
Hi pucko,
here is it:

insert into test (a, b, c, d, e)
select 2,b,c,d,e from test where a = 1

------
Igor

PS: be careful about new value for "a". It should not exists in the table.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6214340
maybe you should expalin a bit more

insert into atable select 2, b, c, d, e, f from atable where a = 1
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6214343
hi igor ;-)
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 9

Expert Comment

by:ITugay
ID: 6214345
hi :-)
0
 
LVL 1

Author Comment

by:pucko
ID: 6214385
I think I explaind a bit stupid.

I have a table with the field_names a, b, c , d

a                b            c              d
------------------------------------------------?
1               2             3              4
2               3             5              6
3               4             7              9

say that I like to do a select * from table where a=2

it gives me 2 3 5 6

Now I like to have this row added whit a new a value (select max a)+1

so the table looks like

1          2      3     4
2          3      5     6
3          4      7     9
4          3      5     6


Is this what you described abowe?
0
 
LVL 1

Expert Comment

by:Kristian
ID: 6214403
you ar a bit vague here do you mean you want to add extra rows manually or columns?
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6214420
Hi pucko,
yes, seems my sample does exactly what you mean

if you want to generate new value for a = max(a)+1 then it would be:

insert into test (a, b, c, d)
select (select max(A)+1 from test),b,c,d from test where a = 1

-----
Igor.
0
 
LVL 1

Author Comment

by:pucko
ID: 6214437
I like to insert a extra row that are a copy of another row (but with a new a value.)

is it easyer if I say that I have a table
that looks like this:

id       car        color
---------------------------
1        volvo       blue
2        fiat        green
2        ferarri     yellow
2        saab        red
3        opel        green

and I like to loog for every car with id=2
and create a new record with it.

here I like to insert:

4       fiat       green
4       ferarri    yellow
4       saab       red

because I make a select * from table where id=2

SO after I like to have this table:

id       car        color
---------------------------
1        volvo       blue
2        fiat        green
2        ferarri     yellow
2        saab        red
3        opel        green
4        fiat        green
4        ferarri     yellow
4        saab        red


0
 
LVL 9

Expert Comment

by:ITugay
ID: 6214453
Hi pucko,

yes, I understand you. Try my sample, it is excactly what you need.

-----
Igor
0
 
LVL 1

Author Comment

by:pucko
ID: 6214528
Mabe I missunderstand you:

I do a select * from test where a=7 b=1 c=1:


it gives me:

a   b  c    d   e
7   1  1    1   7
7   1  1    1   8

then I make a

insert into table (a,b,c,d,e) select 411,b,c,d,e from test where a=7,b=1,c=1

and try
a select * from test where a=411 and b=1 and c=1

and it gives me 0 rows....
0
 
LVL 9

Accepted Solution

by:
ITugay earned 100 total points
ID: 6214555
Hi pucko,
sounds very strange, I just checked it again, all seems fine for me.
btw, I checked it for Interbase.

-----
Igor.
0
 
LVL 1

Author Comment

by:pucko
ID: 6214626
Sorry that was me that miss-spelled something I think.
But I still have one problem.

my sql statement becomes more than 255 char so I get:

[Error] Nusse.pas(3610): String literals may have at most 255 elements



The row it complains over is :


 Buf:= 'insert into q_block(quote_no,version,alternate,block_no,block_id,header,blockfactor,arrange,extra1,extra2,
extra3,extra4,extra5,
extra6,extra7,extra8,extra9,extra10,remark,block_type) select max_no,,version,alternate,block_no,block_id,header,blockfactor,arrange,extra1,extra2,extra3,extra4,extra5,extra6,
extra7,extra8,extra9,extra10,remark,block_type from q_term where quote_no=' +IntToStr
(ThisQuotation.quote_no)+ ' and alternate=' + intToStr(ThisQuotation.alternate) + ' and version=' + intToStr
(ThisQuotation.version)


How can I by-pass this?
0
 
LVL 1

Author Comment

by:pucko
ID: 6214639
Fortget... I use wideString instead
0
 
LVL 1

Author Comment

by:pucko
ID: 6214645
Thanks for the help...

0
 
LVL 9

Expert Comment

by:ITugay
ID: 6214651
Hi pucko,
OK, I hope my help was usefull for you :-)
Thanx for points,
Igor.
0
 
LVL 1

Author Comment

by:pucko
ID: 6214652
(Edited by Computer101)... thje wideString doesn't work either.... what shall I do,.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6214741
which delphi version do you use?

if you can't declare

buf : string; //lower delphi versions are limited to 255 chars

then use a tstringlist instead

meikl ;-)

igor, you got it :-))
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6214785
here is the way:

var
  Q: TQuery;

begin
....
  Q.SQL.Clear;
  Q.SQL.Add('insert into q_block(quote_no,version,alternate,block_no,block_id,header,');
  Q.SQL.Add('blockfactor,arrange,extra1,extra2,extra3,extra4,extra5,extra6,extra7,extra8,');
  Q.SQL.Add('extra9,extra10,remark,block_type) select');
....
  Q.ExecSQL;

-----
Igor.
0
 
LVL 1

Author Comment

by:pucko
ID: 6215863
Thnks...
0
 
LVL 1

Author Comment

by:pucko
ID: 6223615
Still have one problem:

I have this:


      try
        Query1.Close;
        Query1.SQL.Clear;
        Buf:= 'insert into q_term (quote_no,version,alternate,cust_alt,id,idx,termtext,type,sortorder)'+
        ' select ' + intToStr(max_no)+',version,alternate,cust_alt,id,idx,termtext,type,sortorder from '+
        'q_term where quote_no=' +IntToStr(ThisQuotation.quote_no)+ ' and alternate=' + intToStr(ThisQuotation.alternate)
        + ' and version=' + intToStr(ThisQuotation.version);
        Query1.SQL.Add(Buf);
        Query1.Open;
      except on E: Exception do
        begin
          Screen.Cursor:= crDefault;
          Application.MessageBox(PChar(E.Message), PChar(Application.Title), mb_OK+mb_IconStop);
          Exit;
        end;
      end;



And I get the error message:

E.Database Error with message

Query1: CommandText does not return a result set.

When I toggle to inspect mode and cut the sql query from the inspector into the queryAnalyzer the sql query does not give any errors.

What is wrong in my code?
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now