Solved

Delphi and sql

Posted on 2001-06-21
20
158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

632 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