Re: [DUG] Sql query
Call Post Edward Koryagin --- On Fri, 14/1/11, Marshland Engineering marshl...@marshland.co.nz wrote: From: Marshland Engineering marshl...@marshland.co.nz Subject: [DUG] Sql query To: delphi@delphi.org.nz Received: Friday, 14 January, 2011, 12:49 PM I'm viewing a SQL query in a dbgrid. When I add a record to the source db and I run QToDo.Refresh it does not include the new record in the dbgrid. If I close the application and re-run it, the record is displayed. Is refresh the correct property or is there something else. Thanks Wallace -Inline Attachment Follows- ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
Re: [DUG] Sql query
Try QToDo.Requery. Cheers, Laurence Bevan From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Marshland Engineering Sent: Friday, 14 January 2011 12:50 p.m. To: delphi@delphi.org.nz Subject: [DUG] Sql query I'm viewing a SQL query in a dbgrid. When I add a record to the source db and I run QToDo.Refresh it does not include the new record in the dbgrid. If I close the application and re-run it, the record is displayed. Is refresh the correct property or is there something else. Thanks Wallace ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
Re: [DUG] Sql query
This issue may also be Database Transaction related. Depending on how your app is structured you need to be aware of which transaction(s) things are running within. For example (and somewhat simplified) the record-add you did may have happened in a different transaction to the one QToDo is assigned to, if the record-add transaction wasn’t committed then the new record would not be visible to other transactions, hence QToDo did not see this record. In this example closing your app may have automatically triggered the commit of the add-record transaction. From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Marshland Engineering Sent: Friday, 14 January 2011 12:50 p.m. To: delphi@delphi.org.nz Subject: [DUG] Sql query I'm viewing a SQL query in a dbgrid. When I add a record to the source db and I run QToDo.Refresh it does not include the new record in the dbgrid. If I close the application and re-run it, the record is displayed. Is refresh the correct property or is there something else. Thanks Wallace___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
Re: [DUG] Sql query
Rather than doing a Refresh you could do an Open and a Close. If you do
this you probably want to store what record you are on
id := QToDo.FieldByName(id).AsInteger;
QToDo.Close;
QToDo.Open
QToDo.Locate('id', id, []);
substituting id for whatever your primary key is. Not elegant but
better than restarting the application.
Alister Christie
Computers for People
Ph: 04 471 1849 Fax: 04 471 1266
http://www.salespartner.co.nz
PO Box 13085
Johnsonville
Wellington
On 14/01/2011 12:49 p.m., Marshland Engineering wrote:
I'm viewing a SQL query in a dbgrid. When I add a record to the source
db and I run QToDo.Refresh it does not include the new record in the
dbgrid. If I close the application and re-run it, the record is
displayed.
Is refresh the correct property or is there something else.
Thanks Wallace
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
Re: [DUG] sql query issue
Hey Naven
My problem got solved
i actually missed soemthign in where clause i have added the option called
aprove date in it and it fetched the record in 10 minutes time
Thanks so much for ur time in replying to my mail
Regards
Vik
On 5/8/08, Vikas... [EMAIL PROTECTED] wrote:
Hi Naven
thanskf ro the reply
please find the query below
SELECT top 100 percent convert(varchar,a.approvedate,112) as [date],
rtrim(a.accountnumber)+replicate(' ',16-len(rtrim(a.accountnumber))) as
accountnumber,
dbo.Get_Value(accountname,100) as accountname ,
convert(varchar, convert(datetime,createdate),112) as AccountOpenDate ,
dbo.Get_Value(a.comment,1016) as OperatingIns,
dbo.Get_Value((select acctstatuscode from svs_account..acctstatus c where
c.acctstatusid=a.statusid),16) as AccountStatus ,
(rtrim(icnumber1)+replicate (' ',15-len(rtrim(icnumber1 as CUSTID ,
dbo.Get_Value(signaturename,100) as [CUSTID Name],
(case when (select count(*) from svs_account..signcardtable s where
s.accountnumber=b.accountnumber and s.branchnumber=b.branchnumber and
s.signcardnumber=b.signcardnumber)=1 then
'ENABLE '
else 'DISABLE ' end)as CUSTIDstatus,
rtrim(a.imagefilename)+replicate(' ',22-len(rtrim(a.imagefilename)))as
imagefilename ,icnumber1 as CustomerID,signaturenumber ,
(rtrim(b.ICNumber1)+'.tiff')+replicate('
',22-len(rtrim(b.ICNumber1)+'.tiff')) as SignatureFile ,a.Branchnumber,
approvedate
from svs_account..account a , svs_account..signature b
where a.accountnumber=b.accountnumber and a.branchnumber=b.branchnumber
and b.icnumber1 not in (
select imagefile from legacydetailtbl)
On 5/8/08, Neven MacEwan [EMAIL PROTECTED] wrote:
Vikas
Can you post the text of the query?
Basic Pointers
1/ Add indexes for ther foreign keys (these can allow duplicates)
2/ Restrict the result set (I assume you dont want the query to return
10 million rows)
3/ use ansi join syntax
N
Hi
I have an query related to an sql sever 200o using delphi 5. Not sure
if this is the right palce to ask it. Still i belive may be soemone can
help
me in this
I am creating a store procedure.
In that i am fetching the information on the basis of join between
two table.
say table 1 and table2. Now thign is at my client place table 1 has
10 million records and table 2 has 15 million records.
Now i have done a join between these two table depending on
tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in
colum
x of table 1 that emans it automaticaly created a clustered idex. Now
problem is when i execute this query it takes more than hour to run this
query. Any way i can reduce the time of query using any option.i can't use
primary keys on table2 sicne i do have duplicate values.
Regards
Vikas
Solution consultant
NCR coorporation
--
vikas
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with
Subject: unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject:
unsubscribe
--
vikas
--
vikas
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] sql query issue
Hi Naven
thanskf ro the reply
please find the query below
SELECT top 100 percent convert(varchar,a.approvedate,112) as [date],
rtrim(a.accountnumber)+replicate(' ',16-len(rtrim(a.accountnumber))) as
accountnumber,
dbo.Get_Value(accountname,100) as accountname ,
convert(varchar, convert(datetime,createdate),112) as AccountOpenDate ,
dbo.Get_Value(a.comment,1016) as OperatingIns,
dbo.Get_Value((select acctstatuscode from svs_account..acctstatus c where
c.acctstatusid=a.statusid),16) as AccountStatus ,
(rtrim(icnumber1)+replicate (' ',15-len(rtrim(icnumber1 as CUSTID ,
dbo.Get_Value(signaturename,100) as [CUSTID Name],
(case when (select count(*) from svs_account..signcardtable s where
s.accountnumber=b.accountnumber and s.branchnumber=b.branchnumber and
s.signcardnumber=b.signcardnumber)=1 then
'ENABLE '
else 'DISABLE ' end)as CUSTIDstatus,
rtrim(a.imagefilename)+replicate(' ',22-len(rtrim(a.imagefilename)))as
imagefilename ,icnumber1 as CustomerID,signaturenumber ,
(rtrim(b.ICNumber1)+'.tiff')+replicate('
',22-len(rtrim(b.ICNumber1)+'.tiff')) as SignatureFile ,a.Branchnumber,
approvedate
from svs_account..account a , svs_account..signature b
where a.accountnumber=b.accountnumber and a.branchnumber=b.branchnumber
and b.icnumber1 not in (
select imagefile from legacydetailtbl)
On 5/8/08, Neven MacEwan [EMAIL PROTECTED] wrote:
Vikas
Can you post the text of the query?
Basic Pointers
1/ Add indexes for ther foreign keys (these can allow duplicates)
2/ Restrict the result set (I assume you dont want the query to return 10
million rows)
3/ use ansi join syntax
N
Hi
I have an query related to an sql sever 200o using delphi 5. Not sure
if this is the right palce to ask it. Still i belive may be soemone can help
me in this
I am creating a store procedure.
In that i am fetching the information on the basis of join between two
table.
say table 1 and table2. Now thign is at my client place table 1 has 10
million records and table 2 has 15 million records.
Now i have done a join between these two table depending on
tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in colum
x of table 1 that emans it automaticaly created a clustered idex. Now
problem is when i execute this query it takes more than hour to run this
query. Any way i can reduce the time of query using any option.i can't use
primary keys on table2 sicne i do have duplicate values.
Regards
Vikas
Solution consultant
NCR coorporation
--
vikas
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject:
unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject:
unsubscribe
--
vikas
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] sql query issue
Vikas Can you post the text of the query? Basic Pointers 1/ Add indexes for ther foreign keys (these can allow duplicates) 2/ Restrict the result set (I assume you dont want the query to return 10 million rows) 3/ use ansi join syntax N Hi I have an query related to an sql sever 200o using delphi 5. Not sure if this is the right palce to ask it. Still i belive may be soemone can help me in this I am creating a store procedure. In that i am fetching the information on the basis of join between two table. say table 1 and table2. Now thign is at my client place table 1 has 10 million records and table 2 has 15 million records. Now i have done a join between these two table depending on tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in colum x of table 1 that emans it automaticaly created a clustered idex. Now problem is when i execute this query it takes more than hour to run this query. Any way i can reduce the time of query using any option.i can't use primary keys on table2 sicne i do have duplicate values. Regards Vikas Solution consultant NCR coorporation -- vikas ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
RE: [DUG] sql query issue
I think this is in general a sql server question. sql 2000 has a sql enterprise manager/ studio which I can analyze sql query plan which you may check which index you are using. The step what I do is debug a normal sql query in sql query analyzer, once I am happy, then I will put the code in store procedure Have a nice day Regards Leigh www.smootharm.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Neven MacEwan Sent: Thursday, May 08, 2008 6:04 PM To: NZ Borland Developers Group - Delphi List Subject: Re: [DUG] sql query issue Vikas Can you post the text of the query? Basic Pointers 1/ Add indexes for ther foreign keys (these can allow duplicates) 2/ Restrict the result set (I assume you dont want the query to return 10 million rows) 3/ use ansi join syntax N Hi I have an query related to an sql sever 200o using delphi 5. Not sure if this is the right palce to ask it. Still i belive may be soemone can help me in this I am creating a store procedure. In that i am fetching the information on the basis of join between two table. say table 1 and table2. Now thign is at my client place table 1 has 10 million records and table 2 has 15 million records. Now i have done a join between these two table depending on tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in colum x of table 1 that emans it automaticaly created a clustered idex. Now problem is when i execute this query it takes more than hour to run this query. Any way i can reduce the time of query using any option.i can't use primary keys on table2 sicne i do have duplicate values. Regards Vikas Solution consultant NCR coorporation -- vikas ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
RE: [DUG]: SQL Query Builder
Neven, I didn't get around to persevering with either of these and ended up writing my own (mainly because I needed to include stored procedures), but they looked promising at the time last year: http://www.DuckTech.com http://www.korzh.com/ regards, Steve -Original Message- From: Neven MacEwan [mailto:[EMAIL PROTECTED]] Sent: Monday, 2 April 2001 23:27 To: Multiple recipients of list delphi Subject: [DUG]: SQL Query Builder Hi All Can anyone suggest a good open source (pref delphi) SQL Query Builder, Sort of Like a cross between the MS SQL One (visual linking, field picking) without the clumsy Access type literal where clause input (and the ability to order) with a column 'browse data' and paste value ala Crystal Reports would be good, A template ability and syntax highlighting would be good too TIA Neven --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
RE: [DUG]: SQL Query Builder
[Reply] Sergey Orlik's Open QBuilder (OQBuilder) http://www.geocities.com/SiliconValley/Way/9006/index.html Kind Regards Gary At 23:31 on 2/04/2001 you wrote To : CC : From: Neven MacEwan, [EMAIL PROTECTED] Hi All Can anyone suggest a good open source (pref delphi) SQL Query Builder, Sort of Like a cross between the MS SQL One (visual linking, field picking) without the clumsy Access type literal where clause input (and the ability to order) with a column 'browse data' and paste value ala Crystal Reports would be good, A template ability and syntax highlighting would be good too TIA Neven Gary Benner - Software Developer [EMAIL PROTECTED] Corporate Software New Zealand Limited Auckland - New Zealand tel: +64-9 846-6067 (24hr) fax: +64-9 846-6152 mob: (021)-966-992 Software System Design - Consulting - Mentoring - Data Modelling Client Server - Delphi - Interbase - Oracle - Web-based Technologies Electronic Automation and Systems - Microcontroller Design Software http://www.corporate.co.nz Ref#: 41006 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: SQL Query...
Struan Judd wrote: INSERT INTO table1 ( field11, field12 ) SELECT field21, field22 FROM table2 Hi Struan, I just tried the following in database desktop (again a paradox table): INSERT INTO CalcMethod ( ConfigurationID, Object, Attribute, Method, Description ) SELECT 1, Object, Attribute, Method, Description FROM CalcMethod Unfortunately I get "Type Mismatch in Expression", I assume this is possibly due to attempting to hardcode the value of ConfigurationID. If I run the SELECT portion on its own it works ok, but returns 1 as 1.0 which is the wrong type for ConfigurationID which is a straight integer. Any idea how I can force the SELECT to return 1 instead of 1.0? Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: SQL Query...
Mark Derricutt wrote: Unfortunately I get "Type Mismatch in Expression", I assume this is possibly due to attempting to hardcode the value of ConfigurationID. If I run the SELECT portion on its own it works ok, but returns 1 as 1.0 Ok, looking in my "SQL for Dummies" book I found I can get around this using: INSERT INTO CalcMethod ( ConfigurationID, Object, Attribute, Method, Description ) SELECT CAST(1 AS INTEGER), Object, Attribute, Method, Description FROM CalcMethod and casting 1 to an INTEGER. Works fine when I just do the SELECT, so I know LocalSQL/Paradox lets me do CASTing, but when I try the INSERT INTO I now get "Capability not supported". Does LocalSQL not allow me to use INSERT INTO and SELECT in the same query? Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
RE: [DUG]: SQL Query...
The syntax you gave is almost correct. You have to make sure that the
query is going to return a matching number of fields (with matching types
for the table). It's a good idea to explicitly name the fields.
e.g.
INSERT INTO MY_TABLE (MY_FIELD1, MY_FIELD2)
SELECT MY_FIELD3, MY_FIELD4
FROM MY_OTHER_TABLE
WHERE MY_FIELD3 = 'x';
You can do this with any query that returns a result set that will be
suitable for the table being loaded. Note: I know this works under Oracle
and Interbase - can't speak for others.
Cheers.
BJ...
--
From: Mark Derricutt[SMTP:[EMAIL PROTECTED]]
Reply To: [EMAIL PROTECTED]
Sent: Monday, 15 March 1999 10:15
To: Multiple recipients of list delphi
Subject:[DUG]: SQL Query...
Is it possible to do an "insert into" query and supply a series of
values from a "select from" query?
I want to do something like:
insert into sometable values {
select * from someothertable where id = 2 }
???
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
RE: [DUG]: SQL Query...
Mark,
Yes.
The Syntax is
INSERT INTO table1
SELECT * FROM table2
This assumes that the two tables have the same number of fields and
compatible types.
If not you need to specify either a field list in the insert, ie:
INSERT INTO table1 ( field1, field2, ... )
SELECT * FROM table2
or a field list in the select statement, ie:
INSERT INTO table1
SELECT field1, field2, ... FROM table2
or even both, ie:
INSERT INTO table1 ( field11, field12 )
SELECT field21, field22 FROM table2
Clear?
TTFN.
Struan Judd * EMail: [EMAIL PROTECTED]
CJN Technologies LtdPhone: +64 (9) 415 4796
All opinions mine only Mobile: +64 (21) 685 335
-Original Message-
From: Mark Derricutt [mailto:[EMAIL PROTECTED]]
Sent: Monday, 15 March 1999 10:15
To: Multiple recipients of list delphi
Subject: [DUG]: SQL Query...
Is it possible to do an "insert into" query and supply a series of
values from a "select from" query?
I want to do something like:
insert into sometable values {
select * from someothertable where id = 2 }
???
--
-
New Zealand Delphi Users group - Delphi List -
[EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
