Accessing Ubercart's database externally

14 replies [Last post]

Thu, 05/29/2008 - 09:14

Offline

Joined: 05/29/2008

Juice: 28

I have a stand-alone reporting tool that needs to import Invoice and LineItem data, and I'm looking for clues as to
(a) what dbms Ubercart uses (I'm guessing SQL; probably MySQL?)
(b) the schema (I'm guessing there are Invoices and LineItems related tables)
(c) whether the database can be queried externally, say via ODBC.

Certainly the answer to (c) will in part depend on who's hosting the site....

If anyone can help me out here, increase my understanding, provide some links, etc., I'd appreciate it.

Thanks,
Chap

Thu, 05/29/2008 - 09:46

#1 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

Re: Accessing Ubercart's database externally

Hey Chap, Ubercart runs best in MySQL and has limited but buggy Postgres support. Regarding invoice and line item data, your best bet is going to be to bootstrap Drupal and simply load the order you need. It will have all the invoice info and line item info for you to poll through as necessary.

Blimey if I can't find the documentation on bootstrapping Drupal from external apps. I know there used to be some handbook for this on Drupal.org, but you may have to ask around in IRC.

http://api.drupal.org/api/function/drupal_bootstrap/5

Thu, 05/29/2008 - 10:17

#2 View page

Offline

Joined: 11/05/2007

Juice: 3424

Re: Re: Accessing Ubercart's database externally

Here's an example of how to access the DB from a standalone PHP script:

<?php
  // Include Drupal bootstrap functions and bootstrap the DB
  include_once('includes/bootstrap.inc');
  drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);
  // Can now access DB with Drupal functions
  $result = db_query('SELECT * from {variable}');
  while ($data = db_fetch_object($result)) {
    print_r($data);
  }
?>

<tr>.

Thu, 05/29/2008 - 11:49

#3 View page

Chap Harrison

Offline

Joined: 05/29/2008

Juice: 28

Thanks, Ryan and TR, Ok -

Thanks, Ryan and TR,

Ok - although I'm not sure what Drupal is, or what it means to "bootstrap" it, I did see some familiar SQL there. (SELECT * FROM table) So that's a start.

Assuming that my client is going to deploy her ubercart/drupal store using a MySQL database, is there any reason not to use a more direct approach, like via ODBC connection or phpMyAdmin, to access and query the Invoice and LineItems tables? Or, to put it differently, are there advantages to accessing the DB data via Drupal?

Thanks again!
Chap

Thu, 05/29/2008 - 12:14

#4 View page

Offline

Joined: 11/05/2007

Juice: 3424

Chap Harrison wrote:Assuming

Chap Harrison wrote:

You can access it with ODBC if you want. The advantage of doing it as I show above is that you gain the use of all the Drupal utility functions for dealing with data. For example, a node is not just a row in a table - it's an object built by joining a bunch of tables. And it's an extensible object, so add-on modules can add new data to nodes. (An Ubercart Product, for instance, is a type derived from the basic node type.) If you use the Drupal functions to retrieve a node from the DB it's just one function call where you don't have to know or understand the underlying DB structure - you just get the results. If you want to pull the node data yourself, you have to know the DB structure and write your own complicated SQL queries to grab what you want. Then if a new module that adds data fields to the node is turned on, you would have to go in and modify your queries, whereas with the Drupal method, no change is required on your part! Also, Drupal has a DB abstraction layer, so in principle the DB type (MySQL, PostgreSQL, ...) doesn't matter. All this makes it pretty easy to get what you need through Drupal and makes it a lot of work going in through ODBC. In the end, I think it boils down to what tools you're more comfortable with.

<tr>.

Thu, 05/29/2008 - 13:06

#5 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

+1 I'm not very familiar w/

I'm not very familiar w/ ODBC myself, but a huge advantage of bootstrapping Drupal in your app is you get an API that will load all the data for you. For example, if you do a full bootstrap on the installation of Drupal w/ Ubercart installed, you can simply use this Ubercart function to load the order:

<?php
  $order = uc_order_load(100);
?>

This will load an entire order object w/ the order's information, products, and line items... data which is stored across three different tables in the DB. You can then just loop through the $order->line_items array to act on the data there.

Doing it this way is also useful since various installed modules may modify or add information to the order object during load time. It would be a headache to try and accommodate all this w/ direct queries.

Thu, 05/29/2008 - 15:41

#6 View page

Chap Harrison

Offline

Joined: 05/29/2008

Juice: 28

Re: +1 I'm not very familiar w/

Okay, thanks fellers - I'm beginning to see the wisdom of using the Drupal API instead of raw SQL.

I don't know what it means to "have my app bootstrap Drupal". Here's what's more or less set in stone at this point:

The app in question is a Filemaker database that's being hosted on a different server. When my client logs into the Filemaker app, I want for her to be able to press a button and have the app retrieve all new orders since "the last time", and load them into its own Invoice/LineItems tables.

This will almost certainly require establishing some sort of connection to the site hosting the uberCart/Drupal store, passing in some sort of identifier for the last order that was previously retrieved, and receiving back all orders since then.

Beyond that, I'm at a loss. Where would the "bootstrapped Drupal" run - on Filemaker's server, or on uberCart? What exactly could my app connect to on the uberCart server? (originally I figured on this part being handled by an ODBC connection.)

I realize this may be becoming more of a Drupal-specific kind of question, so if you think I should be posting to another forum, please advise.

Thanks!
Chap

Thu, 05/29/2008 - 15:57

#7 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

Re: Re: +1 I'm not very familiar w/

I think the discussion is fine here... the main question is are the two on the same server? If you can't include Drupal's bootstrap.inc from your custom app, then it all falls apart and you'll have to figure something else out.

Bootstrapping Drupal is essentially just starting it up... loading the Drupal app inside your app so you can use any function of your Drupal install in a third party application. Think "picking someone up by their bootstraps." It's kind of like getting it all going... back to work. Or something like that. Maybe it's better just to call it "booting" up a single instance of Drupal.

Thu, 05/29/2008 - 16:43

#8 View page

Andy

Offline

Joined: 08/07/2007

Juice: 1076

Accessing Ubercart's database externally

Chap,
Think of Drupal as a set of functions used to manage / display / edit web pages. "bootstrapping Drupal" is just loading a bunch of PHP functions which are VERY helpful in modifying Drupal based content. The first step is to load (bootstrap) the functions. The second is to use the specific function you want to get the data you want.

Loading is done in the following lines:
// Include Drupal bootstrap functions and bootstrap the DB
include_once('includes/bootstrap.inc');
drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);

These lines of code need to be in a PHP script running on the same server as Ubercart. Then you have to know which Drupal / Ubercart functions you need to use. TR gave one example.

You are getting into some pretty complex communication between Filemaker and Drupal/Ubercart. You probably are going to need some serious (read paid) help to get this working. From what I know of filemaker, it has the ability to execute SQL on a remote database via ODBC. The problem with using an SQL query (as Ryan and TR said) is that the queries in Drupal / Ubercart can be very complex.

I have two suggestions:

Create a test install of Ubercart. Access the MySQL database via phpMyAdmin (or whatever makes you happy). See if you can create the query you want to access the data you want. If this works, use Filemaker and ODBC to directly query the Ubercart database.

If that doesn't work, you might be able to write a script on the Ubercart server which uses the Drupal Bootstrap method above to collect the data and display it via HTML, XML, CSV, etc. . in a form you can import into Filemaker.

Hope this helps.
Andy

Thu, 05/29/2008 - 20:43

#9 View page

Chap Harrison

Offline

Joined: 05/29/2008

Juice: 28

Andy wrote:Create a test

Andy wrote:

Yep, I follow you.

Currently my client's store is in Miva, which has a utility to export orders as a flat, CSV-formatted file. It's a three-step operation: first, she commands Miva to create the export file; second, she FTPs the file onto her laptop; third, she commands the Filemaker app to run a script that imports the CSV file. Sounds like we could use the same procedure with UberCart if necessary (the Filemaker app is served by a different hosting company), accomplishing that first step using the Drupal Bootstrap method.

It would be nice to collapse it into a one-step operation using Filemaker 9's ODBC functionality; once the UberCart store is in place I'll try your suggestion. If I can get what I need with a simple two-table join, fine - otherwise we'll fall back to writing out a CSV file.

Thanks for everybody's help - seems like a terrific community here.

Chap

Thu, 05/29/2008 - 22:08

#10 View page

Offline

Joined: 11/05/2007

Juice: 3424

Re: Andy wrote:Create a test

What might work is to write a small and simple Drupal module which uses the powerful Drupal API to suck the data you need out of the DB and place it into a table of your own within MySQL - this table can then be read via ODBC directly from your application. This small module would run periodically via cron, so your table would always be up to date, and since you're building the table yourself you will have complete control over what goes in it - your ODBC query will be extremely simple as a result! This way, you get the simplicity of accessing the Drupal DB using the Drupal API, and you get the 1-step ease of use when importing the data into your application.

<tr>.

Fri, 05/30/2008 - 10:39

#11 View page

Chap Harrison

Offline

Joined: 05/29/2008

Juice: 28

Re: Re: Andy wrote:Create a test

True, as well. Being somewhat inexperienced in this area, I wonder whether the eventual hosting service might balk at having me create my own MySQL table, schedule my own cron jobs, etc. But we'll see. A one-click solution is vastly more desirable to the way we have been doing it!

Chap

Fri, 05/30/2008 - 10:58

#12 View page

Offline

Joined: 11/05/2007

Juice: 3424

Re: Re: Re: Andy wrote:Create a test

No, that won't be a problem. The whole point of having a database is to create tables and do queries. Drupal/Ubercart creates *lots* of tables - over 100 easily, and just viewing one page of a Drupal site is likely to generate as many or more DB queries than your one cron job run twice a day. This will add no appreciable load to your server.

<tr>.

Wed, 09/09/2009 - 23:57

#13 View page

dontpanic

Offline

Joined: 04/05/2009

Juice: 40

Re: Re: +1 I'm not very familiar w/

I'm attempting to do the same thing, get all the new orders from ubercart into a filemaker database.
(Drupal is hosted on a separate server from filemaker)

php is not one of my strong skills, I've been using filemaker for several years, but am new to drupal...

I was hoping to use the MySQL tables as external data sources in filemaker via ODBC.

Did you ever come up with a functional solution to getting the data from ubercart into filemaker?

Mon, 07/13/2009 - 05:07

#14 View page

spacemoose

Offline

Joined: 07/13/2009

Juice: 2

I need to access the DB from a non php application

Specifically, I want to write a small java application that looks for a particular order number, and checks whether payment has been received. If so, it sends off a license file. I assume the drupal api is only implemented in php?

I can figure out how to access the information using ODBC, but I could use a pointer to how the tables are organized. Specifically, I'm going to need to get all orders for which payment has been received. Can someone point me to the documentation for how the database info is stored? What are the field names for order numbers and payment received, and where are those fields located?

Cheers,

	User login
	Log in using OpenID: What is OpenID? Username or e-mail: * Password: * Remember me Log in using OpenID Cancel OpenID login Create new account Request new password

Ubercart

Navigation

User login