3

I realize that using the database abstraction layer comes with a few caveats such as not being able to run queries that are not available in one way or another across all versions of the databases supported by Drupal7. However, I need to load data from a CSV file into the database from a user uploaded CSV file via a form built with the form API. So in the hook_submit function, I am going to do just that. Obviously I can write my own MySQL dependent implementation, however, I was hoping there was an abstracted "Drupal way" to accomplish the same task. I have not been able to find any such method.

Typically here is how I would do this after sanitizing the data, as an example:

LOAD DATA INFILE 'components.csv' 
INTO TABLE components
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(componentId,componentName,componentDescription,componentAverageCost);

However, obviously this is a MySQL dependent query and even further it is dependent upon MySQL version 5 and later if my memory serves me correctly.

  • Interesting question, but is there a reason you can't just use the CSV importer that is part of Feeds? As long as everything is UTF-8 clean, it works well. – mpdonadio Jul 11 '12 at 19:25
  • Well, it all comes down to maintainability. This custom module already has a slew of requirements and adding yet another, especially something as powerful as feeds to replace 15 lines of code, (with sanitization), seems overkill. Also, I have then maintain the relationship between the feeds module and my module as well as keep Feeds up to date. Good point though, and who knows I may end up just using it. However, I should look at their implementation and perhaps suck that part of their code into my module. – Brandon Bearden Jul 11 '12 at 19:41
  • That's fair. I have been shying away from custom tables, and mapping to a Drupal construct instead, because I always seem to get requests later down the road to make the data work with Views or some other standard module, or people want to tweak the data post import. Using nodes or other type of entity has been saving work in the long run. – mpdonadio Jul 11 '12 at 20:42
  • Hmmm... interesting. Wouldn't I just be able to write my own views implementation though by creating exposed Drupal fields out of my custom database table? I have done that before extending Ubercart database data that did not have any views handlers for the database field data. I mean, this is how many modules work. What do you mean "drupal construct"? – Brandon Bearden Jul 11 '12 at 21:35
  • 1
    drupal construct == node, term, entity, or other built-in feature. Yeah, you can do the views data thing. Then someone will want to reference the data from a node edit form, and you have to wire that up. Then someone else will want to do some custom theming, so you need to do that. The someone will need to do some versioning, and you have to do that. You get the picture. Or, you can save your labor for other endeavors, and just import to nodes. It can be a tough call. – mpdonadio Jul 12 '12 at 12:36
1

You can execute any query you want through db_query(), it just isn't going to work on a different database. db_query() doesn't care, nor does PDO. They basically just send the string passed to it to the server and allow the server to process it.*

So, you could probably get that working through db_query(), not sure if they LOCAL keyword would work in case your web and database server are not on the same server.

That said, I wouldn't recommend doing it. For example, you might end up having to do some processing on the data, e.g. verify that it's valid. No idea what's going to happen if your user is uploading a file that is not valid CSV.

What the DB abstraction layer of Drupal 7 does support are INSERT queries with multiple rows, including a fallback to doing multiple queries if the database doesn't support it. See http://drupal.org/node/310079. That's what I would recommend doing.

If the data should actually end up in Nodes or something similar, then I would recommend looking into the Feeds or Migrate modules. They're not going to be that fast but provide a nice abstraction of the actual storage.

*There are exceptions of course, but if it's possible in PHP, then it's possible with db_query(), pretty much. And a quick search indicated that you can do LOAD DATA INFILE from PHP.

  • So, to be clear, you would recommend I load and parse through the CSV file and do a multi-row insert instead? – Brandon Bearden Jul 11 '12 at 19:50
  • That is the way to make it work across all supported databases, yes, it is impossible to simulate this query for a database that does not support it. As opposed to multi-row inserts, where it's easy to fall back to separate queries. – Berdir Jul 11 '12 at 20:51
2

Your question is a little vague. I assume this isn't for a Content Type you created or an Entity in D7 ?!

As you pointed out this is DBMS specific. The drupal db api basically adheres to an ANSI SQL '97 (i think) specification. So you can do basic queries, updates, etc using the Drupal API. When you start talking about DBMS specific stuff you're outta luck.

The drupal API sits atop the PHP PDO library. This is an abstraction on top of many DBMSs as well -- you can do anything dbms specific with a bare PDO_Connection object ... called a stored procedure, or do your nifty INFILE usage above.

So you'd probably have to make your custom MYMODULE_form_submit() and do your custom query there from the raw pdo db connection. You can call Database::getConnection() in Drupal 7 to get the default db connection directly. Here is similar concept in calling a MySQL stored procedure from D7 asked here before on stackexchange.

Lastly, this isn't really drupal api related because you not seeking to call node_save() with your CSV data ... node_save() lives in the universe of the drupal API and has a succesion of function calls around it in the Drupal-verse, you're clearing asking for MySQL-verse stuff :)

  • Thank you for your response. I am completely aware of doing this with PDO. (Which is exactly what I would use if I bailed completely from the drupal db api.) I use PDO all the time on non-drupal sites. However, my question was more to figure out if it is possible to do it with the Drupal db api and make it work cross database. I don't want it to be mysql specific. Also, this is not for a content type. – Brandon Bearden Jul 11 '12 at 19:48
  • 1
    The answer is no, the drupal db api isnt designed to do this -- its designed to be cross database compatible; not vendor specific. – tenken Jul 11 '12 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.