database/LabVIEW naming-dependancy blues

[Dynamik]

Hi All,

      I'm trying to implement a database for test-data.  The schema will include 15 to 20 tables and, possibly, many queries (to be called from LabVIEW.)  That there will be dependancies between LabVIEW and the DB seems unavoidable, but minimization of dependancies is desireable.  For instance, embedding 40-50 field and table names, as literals, in LabVIEW VIs would be suicidal.  If one coughed on the DB schema, the LabVIEW side would self-destruct.  Making variables of the names is not much better - table-field-counts are still fixed at compile-time, and there's an "INI" somewhere that must duplicate char-for-char names in the Schema.

      If, as it appears, dependancies are unavoidable, what approaches have you guys adopted to reduce dependancy headaches.

This has become a nightmare.

Many Thanks in advance

[bdougr]

Bad news I think,

most database stuff is highly dependant on the table structure

SQL code requires everything spelled out except the SELECT * statement

the insert row will stick a cluster into the database but the cluster has to match field for field

the fetch VI will get data based on column number

the worst is getting data from the database - you need to convert the variants to real world data and guessing most likely will not cut it. (Use the dbtools.listcolumns.vi to get the field info)

not sure why you will be using 40-50 tables (I would try to reduce the number if possible)

[Dynamik]

Hi BdougR,

      Thanks for the tips.  Actually it's a help just to have suspicions confirmed.  It sounds to me like you're using ADO(?) I used DAO (first ODBC, then Jet.) 'cus I like pain (and thought the resulting code would be more portable.)  We're just getting started and have 12-13 tables => 40 to 50 fields @ 5fields/table avg.  Most table-names and field-names will be needed somewhere on the LabVIEW side.

      One of the things this DB will need is a maintenance utility for dummies.  It could be done in Access (the present DB.)  But, besides the [not] joy of learning to construct Forms and Reports in Access, what if we migrate to another DB?  If the GUI is done in LabVIEW, Oh the dependency requirements!  I'm just whining.

Cheers!!!

[Dynamik]

HOLY S**t - I mean, Sacre Blue!

This JPG makes me want to work on problems of my own.

I would debug this problem by simplifying the DLL until the problem is gone, then focus on the specific parameter(s) causing the problem.  You have many chances for incompatible data-types there!

Regards 

[tst]

Dynamik, are you sure you didn't mean to post that here?

[Dynamik]

Dear tst,

      Mais oui!  Thank you for catching this.

Cheers

[bdougr]

No - don't get me wrong it can be done in labview. It is just that you must be very aware of your structure and design style.
let me make a disclaimer : I am not a database designer. I am not a programmer. I am self taught. Following my advice may cause premature baldness.
OK that is out of the way. I suggest using the labview database tools (pro development package). It will not matter which database you use - some are much better than others. Access has file size limits (1gig) When you design your table layout you would keep things consistant and predictable so your labview code will work if you make table changes.

In your labview code you would open the database and
- read the table names
- read the field names and datatype for each table
- now build the sql queries to store data into the proper fields
    this is where the proper planning comes in
    you would have case structures to funnel the proper data into the proper portion of the sql string (the field names have already been read in)

later for your reports you would open the database and
- read the table names
- read the field names and datatype for each table
- execute the query to get the data you want
- convert each variant to the datatype that it is supposed to be

Truthfully I would use either VB or excel to make the reports since the VI will mostlikely output something other engineers can read (you might as well throw two stones at the same bird.) and excel always returns real values for each datatype without needing conversion.

[mikeporter]

Funny how threads can sometimes parallel one another, this is very similar to another one...

Mike...

[Dynamik]

Thanks dDougR,

      I just printed the "don't get me wrong" post - good stuff, there.  The DB field-def could become a cluster with name and data-type - and maybe some other important attributes like is field "auto-number"ed?).  A table-def, could be a cluster with table name and an array of field-defs.  And the DB-def a cluster...

      ... Been on this reply for an hour - considering the possibilities.  Definately food for thought...

Much Obliged!

[bdougr]

you are most welcome
This is the reason I like this forum - straight answers when possible and compassion when your code doesn't work

When I use the access database I always stick an autonumber field in the structure (handy for getting things like the last 100 records)
Also watch which fields become indexes. Queries that read data based on nonindexed fields can become dreadfully slow.

I tried using MySQL a couple of years ago but could never get datebased queries to work correctly. Oracle, foxpro, access and sqlserver work very well.