Member Avatar for chuckc

What is best way to get one row from a mysql table (with many columns) so I can use several several fields from that row for calculations in a C program?
After executing a succesful SELECT command using mysql_query, is there an alternative to using mysql_store_result?

Edited by pritaeas because: Fixed topic title as mentioned in post
  • 2 Contributors
  • 3 Replies
  • 149 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

Quick summary tied to the thread: wants the Access-style convenience of reading one row and using several fields in C. As noted, you need a C client library first — the official MySQL C API (Connector/C) gives two main paths: the simple client API (mysql_query + mysql_store_result / mysql_use_result) and the prepared-statement API. For a single, typed row the prepared-statement API is usually the cleanest: bind columns directly into C variables, get proper types, and handle NULLs without manual string parsing ().

Example (high-level flow using prepared statements):

/* pseudocode */
stmt = mysql_stmt_init(conn);
mysql_stmt_prepare(stmt, "SELECT symbol, numshares, price, commission FROM Trades WHERE id = ?");
mysql_stmt_bind_param(stmt, in_bind);   /* bind input id */
mysql_stmt_execute(stmt);
mysql_stmt_bind_result(stmt, out_bind); /* bind output buffers */
mysql_stmt_fetch(stmt);                 /* values now in C variables */
mysql_stmt_close(stmt);

If you prefer the simpler API, you can run mysql_query, then either mysql_store_result (client buffers entire result) or mysql_use_result (stream rows from server). For tiny single-row queries mysql_store_result + mysql_fetch_row is straightforward; streaming (mysql_use_result) is for very large results or low-memory clients. See the manual for details and lifetime/locking implications ().

Practical tips: SELECT only the needed columns and use LIMIT 1 or a primary-key WHERE clause. Consider computing derived values (e.g., numshares*price+commission AS basis) in SQL to avoid precision/NULL handling in C. Always check for NULLs when binding and free/close results and statements to avoid leaks.

Recommended Answers

All 3 Replies

Member Avatar for rproffitt

So much left unspecified. Did you want to use MySQL from a C app? What's wrong with mysql_store_result?

Member Avatar for chuckc

First, in posting this on Daniweb, I made a mistake in the title. I meant to write “Reading Data from MYSQL using Linux C “. I tried to change that after the fact, but was unsuccessful.

Thank you rproffitt for you comment. It sent me to the documentation where I learned about the multiple statement capability of mysql_query. That helps a lot, but my question remains. . .

Using MS Access, I have a table of stock purchases. Each trade (or row) in that tables has data like stock symbol, number of shares, price, commission, etc. Now with Access BASIC I can select a trade as follows, once I have opened the database as “db”, omitting checks for errors:

Set rs = db.OpenRecordset(“Select * from Trades where . . .”)

Basis = rs!numshares * rs!price + rs!commission

Then I can display:
rs!trdate
rs!symbol
Basis

So my question really is what is the best way to do the same thing with MYSQL and C? I admit to being a newbie with MYSQL, so you won’t hurt my feelings if this is a dumb question. THANKS!

Member Avatar for rproffitt

Thanks for clarification. Before we can proceed, there is no builtin API to C to get at MySQL directly so the First Order Of Business would be to find a library to help us connect and use MySQL from C.

From there we might make some progress.

How about https://zetcode.com/db/mysqlc/ ? First hit on Google and seems to be a good enough tutorial.

However this won't be all we need to know because we'll have to get MySQL installed, running and maybe some MySQL management tool. This is a personal choice so I'd start with https://www.tecmint.com/mysql-gui-tools-for-linux/

commented: Good ideas for me to try. Thanks again.” +3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.