Results 1 to 15 of 15
  1. #1
    Join Date
    May 2012
    Posts
    6

    Unanswered: Executing a query in the file.

    HI all,
    Im using ubuntu . i want to take a file as an input , and the file contains a query which is to be exected on mysql server using C language strictly . pls help me . i've tried . im able to read the contains of the file , but cannot execute it

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Just to clarify what you are looking for, you have a file which contains SQL statements which you need executed on MySQL? What does this need to be executed in C language? What error message do you get?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    May 2012
    Posts
    6
    Listen , im using UBUNTU and having mysql server installed. now i need to execute a query in it with the help of C . for example, the text file has SELECT *FROM table; now, we should open this file using C read the contents and execute this statement . I normally wrote a prog which opens the file contained this query , opened and read it . it displaying the same query as output. all i need is to get this query executed

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So is this a 'C' probelm or a 'MySQL' problem, ie what actually isn't working.
    what error message are you getting

    from your description it sounds like a C problem
    . but we can't tell

    do you mean you as a one off just manually want to examine what is in the table (ie a check by you

    do you want to use the rows returned in a program (presumably another C program)

    is it a permissions problem within MySQL, ie you cannot execute the SQL because the userid you are connecting to MySQL with has insufficient permissions

    but cannot execute it
    doesn't give any clues as to where the problem lies


    have you got other interaxctiosn with the server working elsewhere (IE I'm assuming you have already got the relevant client libraries and C Headers to talk to MySQL.. but then again thats a C questions rather than a MySQL question
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I suppose this is an exercise in dynamic content. If you have a SELECT * FROM table then you will need to know what * returns. This could be a single field or multiple fields. You will need to have a look at the MySQL Client API to see what is available. In Oracle I know there are some API calls which returns the fields, their names, their data types etc which you will most likely need to use.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    May 2012
    Posts
    6
    Yep . its purely C problem . i need to link it to server firstly and retrieve the query result from mysql table . now , the query is stored in a file named "file.txt" . ok ? . U need to use files concept and open the file , read it and the take the content of file as input to the query . got me ??



    Quote Originally Posted by healdem View Post
    So is this a 'C' probelm or a 'MySQL' problem, ie what actually isn't working.
    what error message are you getting

    from your description it sounds like a C problem
    . but we can't tell

    do you mean you as a one off just manually want to examine what is in the table (ie a check by you

    do you want to use the rows returned in a program (presumably another C program)

    is it a permissions problem within MySQL, ie you cannot execute the SQL because the userid you are connecting to MySQL with has insufficient permissions


    doesn't give any clues as to where the problem lies


    have you got other interaxctiosn with the server working elsewhere (IE I'm assuming you have already got the relevant client libraries and C Headers to talk to MySQL.. but then again thats a C questions rather than a MySQL question

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no

    so where is the problem occuring?

    what "isn't working"

    Does the C program compile with the appropriate MySQL connections:- either through the MySQL client library or through ODBC (for all I know that may be one and the same thing.

    having proven the C headers / libraries are there can you connect to MYSQL ie proven that you have a valid userid + password and appropriate permissions on that db

    or is the problem that you have retrieved the data (ie the query worked but you don't know how to process that data from the recordset)?

    in the mnean time I'd suggest you look at
    MySQL C API programming tutorial
    or possibly:-
    Google


    as its a 'C' question not a MySQL question I'll move it to the C section of this forum
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    As I said previously you may want to look closely at the function mysql_fetch_field and the returned structure because this will identify the name of the fields and the types of data being returned i.e. numeric, date etc. This way it should be able to handle something like SELECT * FROM table; where the field names are not identifiable from the query nor are the types.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    May 2012
    Posts
    6
    Yes we can connect to mysql using C . here is the code dat connects to mysql using C

    /* Simple C program that connects to MySQL Database server*/
    #include <mysql.h>
    #include <stdio.h>

    main() {
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;

    char *server = "localhost";
    char *user = "root";
    //set the password for mysql server here
    char *password = ""; /* set me first */
    char *database = "mysql";

    conn = mysql_init(NULL);

    /* Connect to database */
    if (!mysql_real_connect(conn, server,
    user, password, database, 0, NULL, 0)) {
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit(1);
    }

    /* send SQL query */
    if (mysql_query(conn, "show tables")) {
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit(1);
    }

    res = mysql_use_result(conn);

    /* output table name */
    printf("MySQL Tables in mysql database:\n");
    while ((row = mysql_fetch_row(res)) != NULL)
    printf("%s \n", row[0]);

    /* close connection */
    mysql_free_result(res);
    mysql_close(conn);
    }






    Quote Originally Posted by healdem View Post
    no

    so where is the problem occuring?

    what "isn't working"

    Does the C program compile with the appropriate MySQL connections:- either through the MySQL client library or through ODBC (for all I know that may be one and the same thing.

    having proven the C headers / libraries are there can you connect to MYSQL ie proven that you have a valid userid + password and appropriate permissions on that db

    or is the problem that you have retrieved the data (ie the query worked but you don't know how to process that data from the recordset)?

    in the mnean time I'd suggest you look at
    MySQL C API programming tutorial
    or possibly:-
    Google


    as its a 'C' question not a MySQL question I'll move it to the C section of this forum

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you state you can connect to MySQL, so form that I'm assuming its not a permissions, user account problem

    what is the problem you are experiencing?
    what error messages if any are you getting?
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2012
    Posts
    6
    No error message . I simply dont know , what to do

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know C, but I guess you are missing a step
    Im suspicious of:-
    Code:
    /* send SQL query */
    if (mysql_query(conn, "show tables")) {
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit(1);
    }
    my reading of that would suggest you are testing if the query worked, and then display an error message and exit the program
    whether that should read
    Code:
    /* send SQL query */
    if (!mysql_query(conn, "show tables")) {
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit(1);
    }
    is arguable, as I dont;' know C. but I would expect that to test for validity / truth. if there is a problem then that if statement should be executed. otherwise continue witht he proigram

    one way of being certain is to develop a better debugging strategy. Perhgaps the compiler allows stepping through the code. at the very least insert soem temporary debug meesages that tell you where the code is crapping out. eg
    after every if statement
    printf("I've just reached blah....");
    after every MySQL actgion printout the current MySQL error status (Errornumber and message)
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Actually the code is correct mysql_query returns Zero (or false) if the statement was successful. Nonzero (or true) if an error occurred. Not very intuitive. I have compiled the code on my Linux server and this appears to be working correctly. If I have put in the wrong username/password gives me an error message: Access denied for user 'root'@'localhost' (using password: NO). Running it with correct username/password gives me:

    MySQL Tables in mysql database:
    columns_priv
    db
    func
    help_category
    help_keyword
    help_relation
    help_topic
    host
    proc
    procs_priv
    tables_priv
    time_zone
    time_zone_leap_second
    time_zone_name
    time_zone_transition
    time_zone_transition_type
    user

    If there is no error message it makes it very difficult to be able to determine the source of your problem.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  14. #14
    Join Date
    May 2012
    Posts
    6
    dude, u have executed the query show tables . now the show tables is in a text file name query.txt . now we should pass this file as input to the c program , open the file , read the content and execute it . got me now ??


    Quote Originally Posted by it-iss.com View Post
    Actually the code is correct mysql_query returns Zero (or false) if the statement was successful. Nonzero (or true) if an error occurred. Not very intuitive. I have compiled the code on my Linux server and this appears to be working correctly. If I have put in the wrong username/password gives me an error message: Access denied for user 'root'@'localhost' (using password: NO). Running it with correct username/password gives me:

    MySQL Tables in mysql database:
    columns_priv
    db
    func
    help_category
    help_keyword
    help_relation
    help_topic
    host
    proc
    procs_priv
    tables_priv
    time_zone
    time_zone_leap_second
    time_zone_name
    time_zone_transition
    time_zone_transition_type
    user

    If there is no error message it makes it very difficult to be able to determine the source of your problem.

  15. #15
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    ok this now seems to be a C programming issue. To open a file you should look at using the fopen function, then to read the contents use fread. You can read line by line and pass this into the mysql_query function and then process the contents.

    Here is a sample:

    Code:
    	if((fp = fopen("query.sql", "r")) == NULL) {
    		fprintf(stderr, "ERROR: Unable to open file query.sql\n");
    		return 1;
    	}
    	len = fread(buffer, 1024, sizeof(ch), fp);
    
    	/* send SQL query */
    	if (mysql_query(conn, buffer)) {
    		fprintf(stderr, "%s\n", mysql_error(conn));
    		return 1;
    	}
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •