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

 

[pierrotsc]

When i mean "Without sucess", it meant it does not find a match where it should. No error. It just does not return a true value.
DataNo is the index in the TableXtra.

Thanks.

Regarding the other answer, I do have the masterfields joined in the gui.

 

[pierrotsc]

Djangman, I did you the locate the same way.
Tablextra.Locate('DataNo',tableTrial.FieldByName('Datano').AsInteger,[]);

And it did not return a true value. I got to be doing something wrong.

 

[DjangMan]

If it finds the record then .Locate will return True and the table will be pointed at the record. If it returns False then I wasn't able to find that record based on the field value you supplied.

You'll want to make sure that you have a key/index with the DataNo field (and only that field). You might also want to set the IndexName property of the table to make it use that index.

 

[pierrotsc]

I think I got it. I realized that the field relationship (Join) was not set. I did not map the dataNo field to match both table. Could that be the isssue? Does that mean that if I do not map the DataNo, I cannot find a record? The lookup function does return the right value now it looks like.
What do you think?
Thanks.

 

[towerbase]

I think that once you get the hang of SQL you will find it much simpler than what you have been through. SQL is also a valuable skill that is transferable unlike the Delphi specific stuff.

As you become accomplished in SQL you will find that you can do some pretty amazing things with one SQL statement that would require dozens of lines of Delphi code.

Andrew
Hampshire, UK

 

[pierrotsc]

I looked on line for sql books. There are so many. Are they all the same language? Microsoft SQL, Oracle SQL?
I am now confused. What should I get for my delphi programming with absolute DB?
Thanks for any pointers...
Pierrotsc

 

[Glenn9999]

SQL is an ANSI standard language, but there are always different vendor oriented extensions. There isn't anything, particularly, to shy away from when it comes to books on the topic, though what you want will vary depending on what you want to do.

For example, if you work with Oracle a lot, you'll probably want a book on that, because you can learn some of the Oracle specific tricks like PL-SQL and how to set up and run stored procedures on that platform.

So, the question might be what you're looking to do and what you're interested in - I can make numerous suggestions, depending on what those things are.

I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[DjangMan]

Not to jump in ahead of Glenn as I'm sure his list of SQL will be valuable even for me. I found "SQL for Dummies" was an easy read and covered enough for me to get going. For AbsoluteDB - look for any TDataSet documentation as it will apply. Marcu Cantu's books usually gives a good discussion on database programming in Delphi. About.Com has all sorts of database discussions to get you going.

 

[Glenn9999]

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

I should point out that this is bad form, and would be flunked on any code review done by someone who is knowledgable due to the numerous problems that using * presents. Most shop standards, as well, have strict prohibitions against using *.

1) It is not documentative, which means one can not tell what fields are in operation when it comes to the tables. This is a maintenance nightmare, trust me I know from experience.
2) The * wildcard involves all fields, and for the quoted statement to work, it would assume that both tables are always identical. This is not always possible and if you were to follow good design dictate, should never be a requirement. Again a maintenance nightmare, as the functionality of such a statement is a ticking time bomb waiting to go off.
3) Pulling all fields to satisfy a specific requirement is wasteful of resources. One thing when it comes to SQL statements is that they are resource-intensive and querying each field (and each op, like join or sorting) requires a certain amount of resources expended. Those resources add up very quickly, especially when it comes to tables which may have very large number of rows.

I'm sure there are other reasons that I forgot, but the main point is this: Don't use it!


I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[lespaul]

I was just trying to show the power of an inner join in locating matching records....

Leslie

 

[roo0047]

Regarding books, my first was for Sybase, but was generic enough to get me going way back when.

Now I only use this MySQL online manual. It's indexed in such a way that you can find what you need with little effort:

http://dev.mysql.com/doc/refman/5.4/en/index.html

Roo
Delphi Rules!

 

[Glenn9999]

I was just trying to show the power of an inner join in locating matching records....

I was just trying to point out that it is bad form, no more no less. You never know who might be watching and what skill level they are at, in terms of database programming. For someone that might not know and is figuring out on their own, it might be easy to just take whatever they see (bad habits and all) and not know any better. This can especially be problematic if they are the only programmer on premises who is doing such things, or if there isn't much quality oversight. In that respect, if we are helping people to learn by answering these forum posts, then it would be smart to make sure they learn the proper lessons.

To another topic: It occurred to me that it might be valuable to post the resources I have here, anyway. YMMV on most of this, since some of it vendor related, and again its usefulness depends on what you are doing with respect to the databases.

For practical technical knowledge of how an RDBMS works (mainly if you are beginning a study to be a wholesale DBA), the book I have on my shelf is "Oracle: A Beginner's Guide" by Michael Abbey and Michael J. Corey. It is Oracle specific, and I'm sure there are equivalent books out there for other major databases on the market. But even if you don't actually use the information at all, it's a good introductory read into the technicals behind a RDBMS (installation, maintenance issues, typical toolsets, and so on) that can present good background understanding.

For database & table design concepts, along with the basic concepts of databases (for example, what is the difference between something like Access and SQL Server, and why would you use each?), I haven't found much better than "Database Processing: Fundamentals, Design, and Implementation" by David M. Kroenke This information is useful as a programmer if you are adding/changing tables to a current database processing system or creating a new database processing system.

The introductory/reference book I have on my shelf for SQL is "The SQL Guide to Oracle" by Rick F. van der Laans. While most books of this nature will be vendor-specific, I find this one to be very thorough regarding the things you can do with SQL. It doesn't just cover how to query tables (data retrieval and modification), but also does a good job on describing how to create and alter tables.

Again, there is a structured ANSI SQL standard language, but there are vendor-specific extensions in this language as any other. For example, any C book works for instruction with any C compiler, but if the C book describes a vendor extension in the compiler, then it won't be useful for the other vendors compilers.

If you're looking for a good intermediate to master level SQL book, almost the only choice is "Joe Celko's SQL for Smarties: Advanced SQL Programming". The author himself recommends at least a year of basic experience before you jump in on this book, so keep that in mind if you persue this one. He also has a good book of problems, if you're really interested in chasing after some solid SQL experience, though not really necessary if you're looking to learn something.

___________________________
If you notice, I've stayed away from recommending any programming language specific books, since the process for program integration is suprisingly similar between the different languages. It is also identical for every statement you run. The process is always "submit statement, query return code, deal with data, if necessary".

Much of this is an advantage with RDBMS, since if it is set up properly for programs to connect to it, anything should be able to do it. There are different methods to accomplish the connection, but the process never changes.

But this process is always RDBMS specific, too, so you would need to look towards the documentation of the specific RDBMS you are targeting, or look for examples to adapt. But generally, the process isn't too complex - sometimes you have to run a pre-compiler, other times you just have to make sure the DBMS client is running on the computer and simply call it.

If someone has happened to write a programming-language specific RDBMS book (or even a chapter) and you find it, make sure it covers an extremely significant cross-section of databases on the market, along with examples. If it doesn't, pass it up since it likely won't be of much use in the long run.

Hope all that helps.

I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[Glenn9999]

Also, if I still have it somewhere, I can dig out a set of Powerpoint slides I did once upon a time and posted to another site. It covers a specific language in examples (COBOL) and is very brief. But it might present a good thumbnail sketch of what is considered a good round skill-set for SQL programming (the goal I had in doing it).

If I can find it, I can offer it if it would be useful to someone.

I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy