Solved

Delphi and sql

Posted on 2001-06-21
20
151 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

20 Experts available now in Live!

Get 1:1 Help Now