Thread: How to Automate INSERT_INTO using C?

  1. #1
    Registered User
    Join Date
    Jun 2019
    Posts
    44

    How to Automate INSERT_INTO using C?

    Hello cboard,

    I have a small website for my business on DO. It has been a lonely experience, so I been wondering how to automate a few simple things that would keep a visitor attention for a minute or two. If even by accident you visit the site, most people would check the time and temperature if it were there. This idea gave me a reason for learning DBMS using C wherever possible; so I dived in headfirst. This is what I done so far, I just donÂ’t know if it possible or what makes since to SQL to makes things possible.

    Thinking outside the box, without a real clue from google in relation to my searches, I wrote a program in C that will automatically fill-in the files below and others (step1 accomplished). Please excuse my lack of knowledge but is this a form of a prepared statement without the fat? I know it is only a INSERT command but is there a way I can automatically execute something like this with cron, with no human intervention whatsoever?

    Cron would run my code daily to get the information, then the next cron would INSERT the generated weather.csv file into the database. No one would have access to the database because it would be at the backend, in a jail with no way to access or be accessed by the internet. So if there is any injecting, or other tricks possible it will be a root (owner) process. Everyday, the updated db information would go into a static webpage which is also created with the help of automated C code. ThatÂ’s the goal! Sorry for being long-winded.

    Thanks for any help.

    Also, I just found these hours ago and I bomb-rushed them trying hard to understand so that I would not have to start this thread. I fail! ..but these are the best I could find in a full week. I should have taken a course in DBMS if I thought there was life after C. Could these links help to do all of what I am after?

    MySQL :: MySQL 5.7 Reference Manual :: 27.7.10.10 mysql_stmt_execute()

    Read CSV file in C

    How do I read a CSV into a HTML Table?

    how to import csv file to embedded MySQL in C


    Code:
    INSERT INTO
    weather_am (date, day, ttime, temp, view, city, state)
    VALUES
    ('March 01 2020', 'Sunday', '6:00am', '42°F', 'Clear and Sunny', 'Houston', 'TX');

  2. #2
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,748
    Where are you getting your weather information from? If it's through an API for a weather service, then it seems to me that all this insertion into a database is rather unnecessary: just retrieve the data and regenerate the static page with it. You don't need to involve SQL or a CSV file.
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  3. #3
    Registered User
    Join Date
    Jun 2019
    Posts
    44
    Hi laserlight,

    I always wanted to learn how to automate my website using a database. There are easier ways to do it but that is not what I am after. The time of day and the temperature at dusk and dawn can be found in two sentences on google header page for any State or City. That makes it easy for me to fill-in my csv file automatically with my program. Now all I need to know is how to insert it in the database without the use of a keyboard, but 90% of all searches is about how to connect remotely to the database using a keyboard. One thing for sure, C is more difficult than SQL because SQL was written in C.
    Last edited by jc2020; 02-29-2020 at 04:45 AM.

  4. #4
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,748
    Quote Originally Posted by jc2020
    Now all I need to know is how to insert it in the database without the use of a keyboard, but 90% of all searches is about how to connect remotely to the database using a keyboard.
    Then you don't need to generate the CSV file. Have your program invoke the database API to insert the data directly to the database. Actually, if you don't need a historical record, then for mysql you should be using insert ... on duplicate key update instead.

    The "90% of searches" you're talking about probably just talk about connecting to a database to enter SQL interactively because that's a great way to learn SQL; the same SQL statements can generally be applied via a programming API for the database. You have already made some progress by checking the mysql documentation on mysql_stmt_execute().

    If you really do want to generate a CSV file, then perhaps you should just import it into the database, e.g., by invoking mysqlimport since you mentioned mysql.

    Quote Originally Posted by jc2020
    One thing for sure, C is more difficult than SQL because SQL was written in C.
    SQL was not written in C. That relational database systems tend to be written in C does not make C more difficult than SQL. They follow pretty different programming paradigms. I can show you complex use of SQL that would be terribly difficult to decipher compared to well written C but nonetheless do the job well, or I could show you plenty of C programs that are far more difficult to understand than run of the mill use of SQL.
    Last edited by laserlight; 02-29-2020 at 05:47 AM.
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  5. #5
    Registered User
    Join Date
    Jun 2019
    Posts
    44
    Thanks for the detailed information with keywords that I should been using. I knew nothing about that type of API.

    First of all I am sorry I use in the word SQL. I’m using mysql. Now I know what to ask about and what to search for. Also I see that mysql use c-libraries but was not written in c.

    Code:
    API  - # if a table is public
    duplicate key – # if it is shared or owned
    mysql_stmt_execute()  - >>  # must do
    mysqlimport  – >> # csv yes
    For now I need to stick with how to automatically import my csv file. For this question it's many hits--mostly the same:

    How to run SQL script in MySQL?
    Code:
    1) Now, File -> Open SQL Script to open the SQL script. ...
    2) After browsing .sql files, you need to select the option 
       “Reconnect to database”
    3) Now, it will ask for password to connect with MySQL. ...
    4) Note − Press OK button twice to connect with MySQL. ...
    5) After that you need to execute the script.
    Step-5 defeat the whole purpose. .. It require user interaction, somewhere, somehow..

    Automation was the wrong word to use. I should have stuck with the word that most coders can relate to:

    How can I run a mysql query as a cron job?
    How can I set a crontab to execute a mysql query?
    Not much left. It got to be a cron thing to completely eliminate user interaction. That is the question.

    Going to see what bing and yahoo say.
    Last edited by jc2020; 02-29-2020 at 05:51 PM.

  6. #6
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,748
    Quote Originally Posted by jc2020
    How to run SQL script in MySQL?
    As I suggested in my previous post, since you want to import a CSV file, use mysqlimport, which you can directly configure as a cron job (but note that you should not provide the password via cron; the docs I linked to suggests more secure alternatives).

    Alternatively, you could directly invoke mysql to execute a LOAD DATA SQL statement as mentioned in the documentation for mysqlimport, either by manually specifying the SQL statement, or by saving it in a .sql file and feeding that to mysql (the latter tends to be less error prone), but again be wary about the password where cron is concerned.

    If you really want, you can write a program that invokes the database API to run LOAD DATA, but unless you're doing it as part of a larger series of SQL statements, that seems like unnecessary work. Plus if you're going to invoke the database API, you might as well do it to insert directly into the database instead of importing a CSV file that another program you wrote generated.
    Last edited by laserlight; 02-29-2020 at 06:58 PM.
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  7. #7
    Registered User
    Join Date
    Jun 2019
    Posts
    44
    I think I best to read into the links and info that you provided before I ask anything more about it. But for now I see no reason to fear password in cron because there are no users of that jail and there is no way for it to connect to the internet and it lives inside a GELI directory (only forensics can steal it). Whatever it need I su to root and drop off the scripts or whatever work, and that would be a one time thing. Nothing serious goes into production until I am done. What you think about that? However, I REALLY like this the most:

    If you really want, you can write a program that invokes the database API to run LOAD DATA, but unless you're doing it as part of a larger series of SQL statements, that seems like unnecessary work. Plus if you're going to invoke the database API, you might as well do it to insert directly into the database instead of importing a CSV file that another program you wrote generated.
    I have no problem with additional work. At least the functions will be there when it’s time to use them, and you can never test what you don't have. I code with the future in mind.

  8. #8
    Registered User
    Join Date
    Jun 2019
    Posts
    44
    There is just too much out there for so many difference flavours. Just for the record, everything here work for FreeBSD-10, 11 and 12 running mysql56 at minumum and should be the closest for all of Linux. I believe Security has been well addressed. If not please let me know. For nowe it's one down and two to go.

    First we must create a database and a table before moving on:

    OK let's go:

    weather_am.csv
    Code:
    March 01 2020,Sunday,6:00am,42 F,Clear and Sunny,Houston,TX,0001
    Run it from the command line for testing:
    Code:
    # mysql -e "LOAD DATA INFILE '/var/db/mysql_secure/weather_am.csv' INTO TABLE weather_am FIELDS TERMINATED BY ','" \
     -u root -p weather;
    Enter password:
    ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
    So the battle begin!
    Inside mysql Do This:
    Code:
    root@localhost [mysql]> SHOW VARIABLES LIKE "secure_file_priv";
    +------------------+-----------------------+
    | Variable_name    | Value                 |
    +------------------+-----------------------+
    | secure_file_priv | /var/db/mysql_secure/ |
    +------------------+-----------------------+
    .
    Code:
    # mv weather_am.csv /var/db/mysql_secure/weather_am.csv
    Code:
    # chmod 700 /var/db/mysql_secure/weather_am.csv
    .
    In our my.cnf write these:
    Code:
    [client]
    password="pass39485"
        
    [mysql]
    local-infile=1
    
    [mysqld]
    local-infile=1
    .
    Code:
    chmod 600 my.cnf
    or
    Code:
    chmod 400 my.cnf
    .
    Then run from the command-line again:
    Empty return means success!
    Code:
    # mysql -e "LOAD DATA INFILE '/var/db/mysql_secure/weather_am.csv' INTO TABLE weather_am FIELDS TERMINATED BY ','" \
     -u root weather;
    #
    \quit
    # ####################
    # ####################

    or execute it on command-line:
    Code:
    # ./weather.sh
    #
    \quit
    See Result:
    Code:
    mysql -u root -p
    Enter password: 
    root@localhost [(none)]> use mysql;
    Database changed
    root@localhost [mysql]> use weather;
    Database changed
    root@localhost [weather]> SELECT * FROM weather_am WHERE 1=1;
    +---------------+--------+--------+------+-----------------+---------+-------+----+
    | date          | day    | ttime  | temp | view            | City    | state | id |
    +---------------+--------+--------+------+-----------------+---------+-------+----+
    | March 01 2020 | Sunday | 6:00am | 42 F | Clear and Sunny | Houston | TX    | 12 |
    +---------------+--------+--------+------+-----------------+---------+-------+----+
    1 row in set (0.00 sec)
    root@localhost [weather]>
    To automate, I would use cron or whatever possible.

    Well it's curtains for my original program. I really feel like @laserlight have made a real programmer out of me overnight. I never read so much in my life. Now it all seem so easy.

    Also I realize stripping info the way I was doing it is not good. I seriously did not take into account that the owner of that page might change his format at any time.

    Where are you getting your weather information from? If it's through an API for a weather service, …
    .
    .
    .
    Then you don't need to generate the CSV file. Have your program invoke the database API to insert the data directly to the database. Actually, if you don't need a historical record, then for mysql you should be using insert ... on duplicate key update instead.
    I have not completely figured out what most of this mean. I searched the keywords but found only two links about API other then what is said in the MySQL docs.One talk about the API and certificates and the other was some kind of service or program. But I did not see none of that in the doc's. Maybe I missed something.

    I’m guessing the way to do this would be to contact weather.com and apply for an subscription. Would that be where the API and certificates for database access comes in? Sound like common since but sometimes I think i’m missing some screws because I'm still not sure.

    I'm beginning to feel like c++

    How could I ever Thank you laserlight

    Last edited by jc2020; 03-03-2020 at 04:14 AM.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. How do I automate this in bash?
    By cpjust in forum Tech Board
    Replies: 4
    Last Post: 03-21-2011, 03:54 PM
  2. How to automate IE?
    By John_L in forum C# Programming
    Replies: 4
    Last Post: 03-03-2008, 08:46 AM
  3. Automate Fast User Switching
    By haku_nin in forum Windows Programming
    Replies: 2
    Last Post: 07-29-2005, 12:44 AM
  4. automate bios
    By yinhowe in forum C Programming
    Replies: 2
    Last Post: 01-03-2003, 06:32 PM
  5. automate this??
    By Goof Program in forum A Brief History of Cprogramming.com
    Replies: 2
    Last Post: 01-31-2002, 06:02 AM

Tags for this Thread