Solved

Delphi and sql

Posted on 2001-06-21
20
152 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PDF library for Delphi 2 107
tidtcpserver connection lost handle 2 72
Convert MS Word document to a PDF file 9 65
Not able to call Delphi XE10 dll function from Delphi 6.0 4 20
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…
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

22 Experts available now in Live!

Get 1:1 Help Now