0

I am looking for a way to implement MySQL and Perl to make a program. Where I'm lost is that I have a .sql file, that creates 3 tables for the Perl program to use. How do you:

1) Execute the file fileName.sql in Perl to create the tables

2) Link those created tables into manipulable variables in Perl Program (like an example being able to add a user to one of the tables)

Improve this question

2 Answers 2

Reset to default
3

Execute the file fileName.sql in Perl to create the tables

Usually you would set up the database in advance and use the command line mysql client or a GUI such as PHPMyAdmin to load the .sql file.

You could use a call to system to do the former though.

Link those created tables into manipulable variables in Perl Program

Low level access to databases in Perl is usually handled via the DBI module.

Getting something along the lines of a variable per table calls for an ORM. DBIx::Class is a popular choice for this.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

In Perl you use the DBI database interface. In your case, you will also be using something like the DBD::MySQL driver.

There is lots of help available on this topic (including lots of questions on this site).

As for the specific question of your .sql file, there are a few approaches you could take, depending on how fancy you want to get:

  • You could just copy and paste the commands into your program as you write it.
  • You could execute an external program that will run the .sql file (for example, by using system()).
  • You could programmatically read in the .sql file and send the commands from within your program. A module could help you with this (I found SQL::Script on CPAN, which looks useful, though I don't have any experience with it).

I suggest you pick an approach, try it, and ask if you have any specific problems.

Improve this answer

3 Comments

I've managed to connect my program to MySQL now, but I'm still struggling getting my Perl program to run the .sql file to make the tables. Is there a DBI command like the DBI->*** that would do the trick?
Would it be simpler to just open the .sql file and read into an array and then form that array into a variable to send to DBI->prepare(***)?
@JamesBrown, if your file includes a single statement on each line, you could go through the file and execute each line. Here is an example answer that does that: stackoverflow.com/a/4220824/1584772. Or you could split the file based on the SQL statement delimiter (e.g. semicolon). Neither of these approaches is very robust: they could be broken by comments, a semicolon within a string, etc. However, they would be ok if you know that the contents of the file won't break them. If you want a more general solution, I suggest trying the module above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.