Database programming question

[pierrotsc]

I am starting to learn database programming. I am using absolute database if it matters.
In my table, i have 3 fields.:
ImageNo (ImageIndex is the index pointing to ImageNo)
ImagePath (String)
ImageCurve (String)

I want to be able to retrieve the imagecurve field by knowing the record #. I really do not want to find the index because it may not match the record #.

If i have 10 records, the imageNo field could have these values 1,3,4,5,8,9,....Depending if i have deleted/added record. So if i want to get the imageCurve field value of the 3rd record that has an ImageNo of 4, how can I do that?

I hope i am making sense and it is an easy answer.
Thanks.
Pierrotsc

 

[roo0047]

  MyQuery.Close;
  MyQuery.SQL.Text:= 'select ImageCurve from MyTable where ImageNo = 4';
  MyQuery.Open;

You should really get intimate with a SQL Tutorial.

Roo
Delphi Rules!

 

[pierrotsc]

So I guess you are suggesting I should learn SQL I was trying to avoid that. Although, I am not sure your code will work. I am trying to access the 3rd record. I really do not know the value of ImageNo. I just want to go to a specific record # regardless on its index.
So i i have 10 records, I may want to go to the 6th one or 4th one. Right now, I am using the next command in a repeat statement until the recNo matches what i want. Not too efficient I think.

Thanks for getting back to me.

 

[pierrotsc]

Never mind..I digged into the help file. By setting the recno variable to the record # i want did the trick. I thought the recno was a readonly variable at first.
Thanks.

 

[smays]

I should learn SQL I was trying to avoid that.

Ahhh,...

SQL + Quality_Programming_Language = Powerful_Program

Let Quality_Programming_Language = 'Delphi'
-or-
Let Quality_Programming_Language = 'C++ (Builder)'

There are few limitations to him/her who knows how to write a program AND who knows SQL.

Steve.

 

[smays]

MyQuery.RecNo := 3;
MyImageCurveData := MyQuery.FieldByName('ImageCurve').Text;

This will give you the text or other data from the third record. SQL is the way to go.

Steve.

 

[Griffyn]

There's no guarantee that the record number of a TTable object (or equivalent) will be the same forever across program instances. It's possible that deleting and adding new records to the database will change the record number.

Every database table should contain a Primary Key field - that contains a value that is unique across all records in the table. You use this field to find particular records. ImageNo may be a candidate for a primary key in your example - I don't know.

 

[pierrotsc]

Thanks..But not this time. I am using to catalog pictures. Every time I add an image, it goes at the end of the table. If i delete the 3rd image that was record #3, then the 4th image becomes the 3rd record. The index for the 4th image does not change.
Hope that makes sense.
Pierrotsc

 

[TonHu]

No, you don't have to listen to the advise of a pro, but it would be rather wise to do so.

 

[lespaul]

the best analogy is from one of the Access MVPs DHookum - think of records in a database like marbles in a bag there is no order, no first, last, third, until you put the results in a query and order them in a specific way.

Leslie

 

[Griffyn]

There's no need to feel daunted by SQL. An easy way to get started is to use Access and use it's designer to set up a simple query by dragging and dropping tables and fields into place. You can then view the underlying SQL by changing the view. You can modify the SQL directly and see how that changes the view in the designer and vice versa.

SQL, as it's acronym declares, is very structured, and thus easy to learn and remember once you know the rules. Search for 'SQL SELECT' to get some help getting started.

 

[towerbase]

I don't know Absolute Database but it might have something which is the equivalent of the MySQL LIMIT clause. I think the following which is valid for MySQL does what you want:

SELECT ImageCurve 
  FROM MyTable 
  ORDER BY ImageNo
  LIMIT 3,1

In English, this means obtain all the ImageCurve fields from MyTable in ascending order of ImageNo but ignore the first 3 records and just retrieve the next 1 record.

I have found the book Simply SQL by Rudy Limeback to be an excellent introduction to SQL. No doubt other people will also have their own recommendations.

Andrew
Hampshire, UK

 

[roo0047]

I am starting to learn database programming ...

So I guess you are suggesting I should learn SQL I was trying to avoid that.

Convinced yet?!? [LOL]

Roo
Delphi Rules!

 

[pierrotsc]

ok guys...i got it...I will look into learning SQL..Thanks for all the feedback. I really appreciate it.
I am trying to find out on how to export/import only one record from a table. so I guess my answer is: SQL !!!

 

[smays]

Good man, Charlie Brown (just kidding)!

I taught myself SQL using a book called "SQL for Dummies" and added to that the incredible help from MySQL. The query browser, which is no longer made available {boo, hiss}, has all of the MySQL documentation built into it. I would imagine, based on the above recommendations, the book labeled "Simply SQL" will be helpful. I will probably make a few people cringe with this suggestion:

http://www.sqlcourse.com/

- and -

http://sqlcourse2.com/

I do not recommend the sites above as a replacement for any of the written material mentioned above. The sites I mentioned above provide a simple method for experimenting with queries. As was mentioned above, experimenting with Access is also a possibility.

Steve.

 

[DjangMan]

FWIW Absolute has a reasonable support of SQL. I use Absolute and use SQL in a TABSQuery often.

 

[pierrotsc]

So you use absolute too !! may i ask you a question. I think the way i am doing it is not efficient.
I have 2 table in a master/detail relationship.
Master is name tablextra and detail is name tabletrial.
I am on a record in tabletrial and i am trying to find the corresponding record in tablextra. I tried lookup, findkey and locate without success. The only way i can make it work is to code like that:
TableXtra.first;
while not TableXtra.eof do
begin
if TableXtra.FieldByName('Datano').AsInteger=tableTrial.FieldByName('Datano').AsInteger then
break;
TableXtra.Next;
end;

I tried without success:
v := Tablextra.Lookup('DataNo',tableTrial.FieldByName('Datano').AsInteger,'DataNo');

Do you know what i am doing wrong? I spent hours looking at the demo code....

Thanks
Pierrotsc

 

[towerbase]

When you say "without success" do you mean you had a compilation error or a run time failure?

If compilation error what was the error message?

If it was a run time failure then how did you know you had no success? What were the symptoms of the failure?

Does the table have an index using Datano?

Andrew
Hampshire, UK

 

[lespaul]

if i'm understanding you correctly just need to JOIN in the query to get all the information from both tables about that specific ID (DataNo).

Try this:

SELECT * FROM TableXtra
INNER JOIN TableTrial on TableXtra.DataNo = TableTrial.DataNo

You will also find this reading helpful:
Understanding SQL Joins

Leslie

 

[DjangMan]

I use Locate and specify the key field names and the values I'm looking for:

if tbSettings.Locate('ENTRYID',SettingID,[]) then

if you have multiple fields then it gets a little trickier. You separate the field names with semicolons and use the VarArrayOf function to create an array of variants.

if AbSDocPayment.Locate( 'CustomerID;DocumentID;PaymentNo',
VarArrayOf([trim(ado_dm.AROBP.fieldbyname('IDCUST').AsString),
 trim(ado_dm.AROBP.fieldbyname('IDINVC').AsString),
 ado_dm.AROBP.fieldbyname('CNTPAYMNBR').AsInteger]),[]) then
then