C and Mysql

[akand]

I have some data in a table in mysql.
I have written a program which can connect to the database and access all tables and data of mysql.
I have a variable in the C program. I want a particular data in one of the tables in the database to be stored in this variable...
can someone tell me how to retrieve this data and store it into the variable for some processing???

[Ancient Dragon]

Do you know SQL? If not then you will have to read an SQL tutorial There is no one-line C solution for your question.

[lionaneesh]

rewrite your question in detail... Which data....etc etc..

[akand]

let ther be a table named "Books" in d database calld "Library".. it has columns like Number,Book Name
I want to access this data using a C program.
If, I want to check whether a given book is in the table or not, how should I assign the variable in the program so that I can compare the book in the database to the given book.

[lionaneesh]

Sorry dude! but i don't know anything about SQL....

[Ancient Dragon]

First write the select statememnt

SELECT * FROM Books
WHERE Number = 123 // or whatever number you want

Put that into a string and pass the string to the query statement function. When that function returns your program will have to loop through the MySQL's result set -- there may be 0 or more rows.

Read through some of those tutorials I gave you and you will find out how to do that.

[Ancient Dragon]

Sorry dude! but i don't know anything about SQL....

Well, see that's the problem. If you want to work with MySQL then you will have to learn SQL, or at least the basics.

[nbaztec]

Quote originally posted by lionaneesh ...
Sorry dude! but i don't know anything about SQL....
Well, see that's the problem. If you want to work with MySQL then you will have to learn SQL, or at least the basics.

He is not the OP.

akand, Ancient Dragon is right you need to learn SQL.
You said:

I have written a program which can connect to the database and access all tables and data of mysql.

Then did you make that program to accept command line arguments? If you, in fact, have made this program, then you should not be having any troubles at all.
You just need to parse the SELECT statement ( as Ancient Dragon has suggested)

SELECT <column_name> FROM <table>
WHERE <condition>

This should return the data, which would be free of all headers.
In case you plan to go the VCL way, VCL has a ADO Component which can cater to SQL queries, as well as use the MS ODBC interface (I found it easier for debugging).

[tesuji]

I have some data in a table in mysql.
I have written a program which can connect to the database and access all tables and data of mysql.
I have a variable in the C program. I want a particular data in one of the tables in the database to be stored in this variable...
can someone tell me how to retrieve this data and store it into the variable for some processing???

>>> I have written a program which can connect to the database and access all tables and data of mysql.

Which interface do you actually use, odbc, ado, ole db ?

If it is odbc within plain C (not c++) i can send you some examples how to get data from a sql table and store it back. This are examples once i wrote for MS SQL server 2008 and Sybase database. Because ODBC interface (use Version 3) is strictly standardized the call level interface is always the same no matter which database the c program is working with.

Btw, as you stated your program is already able to connect to database and obtain some data from a table, what means that you already know how to manage simple sql select statements, and the update statement is almost always simpler than select statement.

-- tesu

[nbaztec]

update statement is almost always simpler than select statement.

Correct me if I'm wrong please, but isn't UPDATE used to update records from the table and not fetch records them.

[Ancient Dragon]

>>Correct me if I'm wrong please, but isn't UPDATE used to update records from the table and not fetch records them.

You are not wrong.

[nbaztec]

>>Correct me if I'm wrong please, but isn't UPDATE used to update records from the table and not fetch records them.

You are not wrong.

Then I think I entirely miss the point tesuji is trying to make:

update statement is almost always simpler than select statement.

Why compare the 2?

[tesuji]

Then I think I entirely miss the point tesuji is trying to make:

Why compare the 2?

>>> Why compare the 2 ?

akand said he would be able to read all data from the database in his program but he would not know how to store data from his program back to database. If so, he must know the select statement because reading data from database within a c program is done by select statement. To write data back to datbase needs UPDATE statement. So he just need to become acquainted with unknown UPDATE.

I hope these clears up my comparison. And indeed UPDATE statement is really really much more easier than SELECT statement.

-- tesu

[nbaztec]

OK, I get now. You are trying to say that UPDATE is easier to learn than SELECT.
Though I beg to differ.

UPDATE Foo
SET Roll_No=5
WHERE Index=1

SELECT * FROM Foo

I think SELECT is simpler. But that would be just my opinion. :)

P.S. I know SELECT can be real dirty with ORDER BY, GROUP BY, AS but in it's simplest form I think it is easier than UPDATE.

[akand]

oh god....i hav learnt SQL very well.....
ok..ill tell d exact doubt in d project im working on....
ther r some coordinates given from a GPS module(latitudes and longitudes)....
There are some standard coordinates (for a region) inside a table in d database...
I have to say in which region the given coordinates are located....
I thought of doin this by taking the minimum distance...
so for this i hav to use every data in d standard table with d given one and do a mathematical operation and get d minimum distance....
For doing the mathematical operation I need to extract d data from d table and STORE it in another variable in d C program....

[akand]

and guys i kno every command in SQL...i hav learnt it for 4 months.....n i find SELECT better than UPDATE.....
my doubt was how to initialise a variable in a C program wid a data from a table...thts all...

sorry for complicating it...

[akand]

and people...if u just use "SELECT * FROM table" it will just print everything in d terminal...
i want the value to be STORED in a variable....

N i want this in Linux...
Thanks for ur help....

[nbaztec]

I recommend you use ADO. Here's a Link.

and people...if u just use "SELECT * FROM table" it will just print everything in d terminal...

In case you do not know, the same command is executed to fetch records which are then read inside a Loop.

[akand]

"same command is executed to fetch records which are then read inside a Loop."

This I dint understand...can you explain in detail, please....what kind of a loop is it??

[akand]

The ADO thing is very useful and is perfect..... But is ther anything which i can work on Linux???? Ubuntu....

[tesuji]

The ADO thing is very useful and is perfect..... But is ther anything which i can work on Linux???? Ubuntu....

On Linux there are two very simple methods: ESQL und ODBC. There is also a source forge project to develope ADO for Linux, though.

If one is using PostgreSQL on Linux (what seems to be obvious) there is no simpler way than ESQL, you might check the PostgreSQL's ESQL manual. However, I guess more than 80% of programmers are using ODBC on Linux.

Happy esqling!

-- tesu

[nbaztec]

guess more than 80% of programmers are using ODBC on Linux.

Can't complain. ODBC has only but a few flaws, it provides a great interface. You may need to use the ExecSQL() query. Another Link.

[tesuji]

Can't complain. ODBC has only but a few flaws, it provides a great interface. You may need to use the ExecSQL() query. Another Link.

Ah, gracious nbaztec are you talking about something like that:

...
  rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt);
  SQLCHAR sqlStatement[] = "SELECT pName, convert(char(20), pDate, 104), quantity, salesprice " 
    "FROM products WHERE PID = ?;";
...
  prodID = prod_ID;
  rc = [B][U]SQLExecDirect( hStmt, sqlStatement, SQL_NTS )[/U][/B];
...
rc=SQLGetData(hStmt, 4, SQL_C_FLOAT, &oldSalesPrice, 0, 0); printf("\nSales price    %8.2f", oldsalesprice);
...

Interested in getting to know what the ... stand for? btw, with minor changes (e.g. date conversion) complete code runs on MS SQL server 2008, Sybase, Oracle, DD2, SQL Anywhere, PostgreSQL ...

-- tesu

[nbaztec]

ExecSQL()

Ah, my bad. Sorry for that. Yes you are right tesu I meant the SQLExecDirect() & SQLExecute() functions. Guess I was tired at night(My Timezone). Thanks for correcting me.

[tesuji]

...For doing the mathematical operation I need to extract d data from d table and STORE it in another variable in d C program....
my doubt was how to initialise a variable in a C program wid a data from a table...thts all...

Sorry, I didn't notice those questions earlier. Here is some C code showing you how to fetch specific rows from a table. I used prepare statement, binding methods and SQLExecute this time. You might do wee changes to fit this code to your current database.

void SlcProductTable(SQLHENV hEnv, SQLHDBC hDbc, SQLINTEGER quantity )
{
  SQLHSTMT hStmt;
  SQLRETURN	rc;
  SQLINTEGER pID, pQuantity; 
  SQLLEN c_len=SQL_NTS;
  SQLCHAR pName[80], pDate[20];
  SQLREAL SalesPrice; 

  printf("Rows of products having quantity < %d\n", quantity);

  // Statement handle 
  rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt);

  // Get pID, pName, date, quantity and salesPrice from product table
	SQLCHAR sqlStatement[] =
	  "SELECT pID, pName, convert(char(20), pDate, 104), quantity, salesprice FROM products "  
             "WHERE quantity  < ?;";

  // Prepare SQL statement 
  rc = SQLPrepare(hStmt, sqlStatement, SQL_NTS );

  // Bind host variables on columns coresponding to sql statement
  rc=SQLBindCol(hStmt, 1, SQL_C_SLONG, &pID, 0, NULL);
  rc=SQLBindCol(hStmt, 2, SQL_C_CHAR, pName, sizeof(pName), &c_len);
  rc=SQLBindCol(hStmt, 3, SQL_C_CHAR, pDate, sizeof(pDate), &c_len);
  rc=SQLBindCol(hStmt, 4, SQL_C_SLONG, &pQuantity, 0, NULL);
  rc=SQLBindCol(hStmt, 5, SQL_C_FLOAT, &SalesPrice, 0, NULL);

  // Bind host variable quantity on parameter (?) of where clause
  rc = SQLBindParameter( hStmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
    SQL_INTEGER, 0, 0, &quantity, 0, NULL);

  // execute prepared statement
  rc = SQLExecute(hStmt);
 
  printf("PID   Product                            Date          Quantity  Salesprice\n"	     "---------------------------------------------------------------------------\n");

  // Fetch rows from resultset consecutively
  while((rc = SQLFetch(hStmt)) != SQL_NO_DATA)
    printf("%4d   %-30s    %-11s %4d     %8.2f\n", pID, pName, pDate, pQuantity, SalesPrice);
}
/*
Rows of products having quantity < 50
PID   Product                            Date          Quantity  Salesprice
---------------------------------------------------------------------------
1010   MacDB, true Oracle Clone          18.06.2010    30         1.99
1030   Mikes Bureau                      18.06.2010    25         1.99
1050   Mikes Alta Server                 18.06.2010    25        19.00
*/

Some effort is necessary to get a coupla data from SQL database into C program.

-- tesu