I.T. Discussion Community!
-Collapse +Expand
Delphi
Search Delphi Group:

Advanced
-Collapse +Expand Delphi To/From
To/FromCODEGuides
-Collapse +Expand Delphi Store
PRESTWOODSTORE

Prestwood eMagazine

October Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyPascal and Delphi Coding BoardDelphi Single User Apps Topic   Print This     

Database Problems

Database Problems in Delphi Single User Apps topic (part of our Pascal and Delphi Coding group).

Quick Search: Database   Problems   Database Problems  
Daydreamer
 (Inactive)
Muskegon, MI USA

Hello everyone!  I am new to Delphi and I am picking it up really quickly by the way.  My problem is I haven't seen a working Mysql database that worked from examples I have downloaded.  I have gotten a few Access database running with Delphi.  What I can't seem to do however is get a certain recordset.  I can't find no information on pulling a specific recordset with Access/Delphi.

Here is what I am trying to do:  when a person logs on to the chatroom server, I want them to have access  to the information they entered in there personal record only?

If there is an easier way to do this besides using Access, can someone point me in the right direction please.  I am using Delphi 7.

Thanking you all in advance!!! 

 Posted 12 years ago (Thread Starter)
Comment Quote
About Daydreamer -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12206, 9 replies
Thread Started 10/4/2007 10:05:10 AM
View Counter=4874
Last Reply Posted 10/31/2008 10:34:52 AM)
Location=Muskegon, MI USA 
Joined=12 years ago   MB Posts=3  
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

Given the huge popularity of MySQL in the Linux world, compared to its slowly building popularity on Windows, it's not surprising that examples are hard to find.

That said, and given that you're using Delphi 7, I'll make a couple suggestions.

First of all, your use of the term "recordset" in the same paragraph as Access suggests you may be approaching this problem with a VBA mindset.  Stop that! ;-)

In the Delphi world, when working with SQL databases, we almost always use Delphi's TQuery component - or database-specific replacements for TQuery.  TQuery has a SQL property, in which you provide the SQL statement that will retrieve the data rows (and columns) you want. Once you've specified the SQL, you call the TQuery's Open method.

When Open finishes its work, you can inspect the TQuery's RecordCount property to determine how many rows were returned.  You can also use First, Next, and Eof to naviate through the records.

Delphi's native TQuery can be used with MySQL - provided your first set up an ODBC DSN for your database.  The downside of this approach is that you'll be dragging all the BDE baggage into your project - when there are better ways. 

I'd strongly suggest you check out Kyle Cordes's BDE Alternative Guide (BAG), here:
http://kylecordes.com/bag/listbytype.html 

Scroll down that page to the MySQL listings.  There you'll see several MySQL-specific products that can be used to eliminate the BDE.

Explore those deeply, and I'll bet you'll even find examples.

Note: Later versions of Delphi include components that afford more support for MySQL than came with Delphi 7.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #12211 (Level 1.1)  Reply to 12206
Thread Started 10/4/2007 6:09:26 PM
View Counter=2
Location=Prestwood IT office in Citrus Heights, CA 
Joined=15 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

Flat files are certainly an option, but they have limitations:

  • Searching is almost always by brute force.
  • They don't have the advantage of automatically maintained indexes.
  • You have to keep them maintained (ordered)
  • You become totally responsible for "binding" the data in them to the visual controls in your application.

Whatever you do, don't reinvent the wheel! 

If all you require is a single-uer, local data store, later versions of Delphi include ExpressDb, a cool little database that uses XML files for storage, and behaves like other BDE-compliant databases.

There are also some free - and inexpensive - databases suitable for Delphi. Of the inexpensive ones, my favorite is DBISAM (and its new successor, ElevateDB) by Elevate Software.  It is awesome, and the client/server version of ElevateDb is sure to compare favorably with MySQL.

As for what versions of Delphi I use?  D5, D6, D7, D2005, and D2006 (at Prestwood, we maintain existing Delphi applications for numerous clients.  Many don't care to update their code to a later version of Delphi, so we us what they use).  My day-to-day standby, though, is Delphi 7.   2005 ad 2006 introduced some compelling new features.  Unfortunately, 2005 is something of a train-wreck, and the help system in 2006 is wimpy compared to D7.

You might poke around places like eBay, and see if you can find a second-hand copy of Delphi 7 Enterprise.  It's a rock-solid product, and the Enterprise version will get you the cool ExpressDb.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 11 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #12520 (Level 1.2)  Reply to 12206
Reply Posted 3/1/2008 7:47:25 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=15 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

rdkam,

I hope it's okay that I reply "email-style."

>What have you found with ElevateDB as compared to MySQL or MS SQL 2008?

[wp] I found that, eager and enthused as I am about ElevateDB, I can't seem to find the spare time to give it a fair workout. Undecided

> I refactored my code to work with the ElevateDB from previously using MySQL. When I run the two side by side, with same DB structure and size, the ElevateDB runs a bit slower.

[wp] You don't mention the DB "engine" in use with your MySQL database.  There are now several, and I'll guess that they aren't all equally endowed in the performance department.

Which isn't very useful to you because I'm not a heavy MySQL user, so probably couldn't comment on the differences, anyway.

>Thoughts or suggestions?

[wp] Yes.  One of the reasons I've been such a longtime fan of DBISAM is the fantastic level of support from Elevate Software. 

And it's stability, of course.  (For one of our clients, Prestwood developed their flagship product with DBISAM.  Since that product's inception in 2005, not a single support incident has traced to a flaw in DBISAM.)

I would suggest that you email Elevate support with specific examples of significant differences in performance. My experience is that Tim Young (Elevate's owner) is eager to improve his products, welcomes challenges, replies promptly, and strives to make his products best in class.

I well remember the birth of DBISAM.  During its initial public beta, I had several conversations with Tim.  I was amazed at the breadth and depth of his knowledge of other databases of the time.  He freely borrowed good ideas from several, rejected the bad ideas.

Over time, DBISAM matured into one of the most solid databases I've used.  I expect to see the same with ElevateDB.

So do your part, and let Tim know about performance disappointments.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 11 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #13094 (Level 1.3)  Reply to 12206
Reply Posted 10/29/2008 8:03:06 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=15 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

>Well, I don't bang away at MySQL either. The most we had was about 8 users putting in about 1000 transactions in 20 minutes or so. Certainly not a work out like Amazon uses MySQL.

[wp] And I think it's safe to assume that Amazon utilizes a lot of load-balancing and server farms.

>I think I figured out what ElevateDB was slowing down on:
I am a believer in not leaving things lay around after I am done using them and to this end, maybe I was over zealous - I kept closing the entire connection to Elevate.

[wp] In general, I agree when it comes to cleaning up after myself.  That said, one of the most expensive operations in any client-server database applications is obtaining a connection.  If I have a desktop application that makes heavy use of a database, I try to open the connection once, and close it once (at application termination).  Tables and queries, on the other hand, should generally be opened when needed, updated as quickly as possible, and closed when finished.

>Now, I am using the same code for Elevate as MySQL, however, the only difference is MySQL uses ADO and Elevate, well, uses Elevate components.

[wp] And, frankly, I don't know all about all the differences.  Again, I'd "ping" Elevate Support for suggestions.

>I create everything at runtime and so, what was happening - I believe, was on my close of the connection in a try/finally block, I was not only closing the query, but also the Datbase, and Session.

[wp] Because you haven't mentioned the architecture of your application, it's a little hard to comment on this.  I also sometimes create things at runtime. However, Delphi's TDataModule is a very convenient, very powerful construct.  It lets you (figuratively) create many of your database objects at design time, which I find to be a great convenience.

Then, implemented as a Singleton pattern, you can ensure that one, and only one, instance of your data module will exist during the lifetime of your application session.  This can be great for both performance and control.

>I am thinking maybe there is a lot more cycles used in Elevate to create the DB, Session and subsequent connection than there is in ADO - at least it appears this way.

[wp] You don't mention the exact architecture of your application.  Is it web-based? ASP .NET?  I ask because the ASP .NET/ADO architecture supports connection "pooling," which can immensely speed up the acquisition of connections.

>When I just closed the query after finishing whatever and leaving the close connection until the object was destroyed, I have a lot better performance.

[wp] Sounds like progress to me. Smile

>Two things I noticed straight away with Elevate - great responsiveness and good documentation.

I think we will be moving forward with Elevate.

[wp] I'm glad to hear it.  In our industry, competition benefits all of us, and Elevate is a worthy competitor.

There is one other thing you'll notice about Elevate: Their newsgroups and forums are uncommonly friendly.  Nice bunch of people.

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 11 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #13096 (Level 1.4)  Reply to 12206
Reply Posted 10/29/2008 8:49:34 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=15 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Most Recent Post
Wes Peterson
Prestwood IT
Prestwood IT office in Citrus Heights, CA

>Aritechture? I do not mean any disrespect but this term is thrown around with many contexts.

[wp] You are, of course, correct.  "Architecture" is one of those "baggage words;" it has lots of baggage attached.  I could have chosen a better word - or been more specific.

>Delphi 2007 Windows Based Forms applications with a few services. Mostly btrieve DB currently, but migrating over to another (MySQL/Elevate/MS SQL Server 2008/Access - depending on the needs of the app)

[wp] Btrieve.  Wow.  That goes back a ways.  I still have my fat, red Novell slipcase/book on Btrieve.  I don't know why I hang on to it; sentimental value, I guess.  I'm just curious: What tools to you use with Delphhi to access Btrieve data?

>I have used the TDataModule, but lean towards my own home-grown, TDBClass = class(TObject) and then put everything in it - instantiating at startup, etc. Then, I can sub-class him within himself or from an external source. This is just what I grew up on and I have a lot of solid code working in this methodology. Not anything is wrong with TDataModule...

[wp] That's one of the exciting things about developing software; there is seldom just one "right," or "best" way to do a given task.  Your approach is equally valid as TDataModule.

For what it's worth, we often use an abstract, "base" TDataModule, then create descendants that implement the abstract methods.  This eases the pain of switching databases later.  It sounds like your approach offers the same benefit.

>A few XML and form scraping from web, but no web applications via Delphi - yet.

[wp] Sounds like an ambitious - and fun - project.  Have fun!

Wes Peterson
Senior Software Engineer
Prestwood IT Solutions
http://www.prestwood.com

 Posted 11 years ago
Comment Quote
About Wes Peterson -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Wes Peterson

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.


Post ID #13099 (Level 1.5)  Reply to 12206
Reply Posted 10/31/2008 10:34:52 AM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=15 years ago   MB Posts=158   KB Posts=163   KB Comments=34   BLOG, Topics=20  
Daydreamer
 (Inactive)
Muskegon, MI USA
Thank you so much!  I think im gonna just use regular flat files for the storage.  It will make things less complicated.  Now that B.A.G. site has alot of good stuff!  And I notice you caught my vb recordset reference hahahahahaha.  What version of Delphi are you using by the way?  I have linux installed on a computer in the other room but I don't like the feel of Lazarus, oh and kylix just seems to work with certain linux kernelsBig Grin!
 Posted 12 years ago (Thread Starter)
Comment Quote
About Daydreamer -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12212 (Level 1.6)  Reply to 12206
Reply Posted 10/4/2007 8:20:06 PM
Location=Muskegon, MI USA 
Joined=12 years ago   MB Posts=3  
Daniel Kram
-- UNK

What have you found with ElevateDB as compared to MySQL or MS SQL 2008? I refactored my code to work with the ElevateDB from previously using MySQL. When I run the two side by side, with same DB structure and size, the ElevateDB runs a bit slower.

Thoughts or suggestions?

 Posted 11 years ago
Comment Quote
About Daniel Kram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #13091 (Level 1.7)  Reply to 12206
Reply Posted 10/29/2008 5:48:29 PM
Location=-- UNK 
Joined=11 years ago   MB Posts=25   KB Posts=4   KB Comments=11  
Daniel Kram
-- UNK

Wes
Email style is fine.

Well, I don't bang away at MySQL either. The most we had was about 8 users putting in about 1000 transactions in 20 minutes or so. Certainly not a work out like Amazon uses MySQL.

I think I figured out what ElevateDB was slowing down on:
I am a believer in not leaving things lay around after I am done using them and to this end, maybe I was over zealous - I kept closing the entire connection to Elevate.

Now, I am using the same code for Elevate as MySQL, however, the only difference is MySQL uses ADO and Elevate, well, uses Elevate components.

I create everything at runtime and so, what was happening - I believe, was on my close of the connection in a try/finally block, I was not only closing the query, but also the Datbase, and Session.

I am thinking maybe there is a lot more cycles used in Elevate to create the DB, Session and subsequent connection than there is in ADO - at least it appears this way.

When I just closed the query after finishing whatever and leaving the close connection until the object was destroyed, I have a lot better performance.

Two things I noticed straight away with Elevate - great responsiveness and good documentation.

I think we will be moving forward with Elevate.

 

Thank you for the input. It is much appreciated.

 Posted 11 years ago
Comment Quote
About Daniel Kram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #13095 (Level 1.8)  Reply to 12206
Reply Posted 10/29/2008 8:35:20 PM
Location=-- UNK 
Joined=11 years ago   MB Posts=25   KB Posts=4   KB Comments=11  
Daniel Kram
-- UNK

I agree on the connection.

Aritechture? I do not mean any disrespect but this term is thrown around with many contexts. Delphi 2007 Windows Based Forms applications with a few services. Mostly btrieve DB currently, but migrating over to another (MySQL/Elevate/MS SQL Server 2008/Access - depending on the needs of the app)

I have used the TDataModule, but lean towards my own home-grown, TDBClass = class(TObject) and then put everything in it - instantiating at startup, etc. Then, I can sub-class him within himself or from an external source. This is just what I grew up on and I have a lot of solid code working in this methodology. Not anything is wrong with TDataModule...

A few XML and form scraping from web, but no web applications via Delphi - yet.

 

Nice talking/working with you.

Have a great weekend - I know it's only Wednesday, but I am off the next two days, so for me, the weekend started tonight :)

 

 

 Posted 11 years ago
Comment Quote
About Daniel Kram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #13097 (Level 1.9)  Reply to 12206
Reply Posted 10/29/2008 10:26:11 PM
Location=-- UNK 
Joined=11 years ago   MB Posts=25   KB Posts=4   KB Comments=11  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1186A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #12206 Counter
4874
Since 4/2/2008

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech