Generic Database Driver - Beta

[Mikla]

NOTE: THERE IS A NEW VERSION HERE (http://www.charmedquark.com/vb_forum/showthread.php?p=46397#post46397).
Attached is a generic database driver that performs SELECT, UPDATE, INSERT & DELETE database queries. The results are return as both a value and a stringlist. The way the driver is implemented it is very flexible in the results that can be returned since the parameters provided are those in the typical queries mentioned above.

I have plans to add multiple read capabilities and monitoring along with the ability to sort the SELECT (I just realized I did not include this).

I have also attached the documentation.

Any feedback would be appreciated.

1/5/06 UPDATE:
ChangeLog:
v1.0.0 Jan 5, 2007
Initial Release

/v0.1.1 Jan 5, 2007
Added OrderBy parameter
Added Ability to leave off empty parameters at end of parameter string
In StringFldChanged method, changed CommResults.ValueRejected to
CommResults.Success and added log entry to avoid Interface Viewer
popup error messages. User gets result from DBValidFlag
Changed Manifest file to allow user to select connection from a list

/ v1.3 Apr 14, 2009
Added use of escape char (\) for sql values to allow commas in the parameters

[jscheller]

Very cool... I haven't run it against anything, but took a look at the code... Lots of work... :-)

Do you have something particular planned that you're going to use this for?

You should consider using the DSN selection prompt in the manifest to let the user pick from all the existing data sources on the box.

[Mikla]

I don't have it aimed at anything particular... I just started messing around with database stuff in a macro then Dean mentioned a Driver and one thing led to another...

I have lots of DB projects that are in this category... probably 10 websites with DB backends that I have developed that I have not done anything with... I look at it as a hobby and a distraction from the stress of my real job.

Thanks for the tip on the manifest file, I will make that change!

[IVB]

I actually started reading through the docs to see if I could use this for the myhomecookingpc.com stuff. That's been back-burner'ed given the rest of the stuff, but i'm thinking that I could either use this driver or plagiarize the code to do my own driver.

Thanks for doing this.

[Mikla]

Part of what I am hoping for is that someone can use it as is or any part of it for there own puposes. Between this and what j has done, there should be enough for others to write custom drivers or macros.

[Mikla]

Made an update:

ChangeLog:

v1.0.0 Jan 5, 2007
Initial Release

/v0.1.1 Jan 5, 2007
Added OrderBy parameter
Added Ability to leave off empty parameters at end of parameter string
In StringFldChanged method, changed CommResults.ValueRejected to
CommResults.Success and added log entry to avoid Interface Viewer
popup error messages. User gets result from DBValidFlag
Changed Manifest file to allow user to select connection from a list

[Dean Roddey]

Could you e-mail me the manifest and exported CML text for this guy? Someone loaded it and it it killed CQC, so I just want to take a look at it and do some debugging.

[Mikla]

Ouch!!! I just sent the files. Please let me know what you find.

[Mikla]

Now that I think about it, I did have some odd behavior early. Before I put the m_ReadFlag in the Poll method, if I had an invalid input from the Interface View I would get CQC to reset. Meaning all the drivers would reload as viewed in the Admin Interface Server Admin window. Not sure if that helps.

[Dean Roddey]

That should be fixed in the next drop. Well, you'll still get an error if you do something that causes an ODBC error, but it shouldn't kill CQCServer. It seems that the ODBC stuff, unlike most any other Windows system call, will pretty regularly cause system exceptions if you do anything wrong. The CML wrapper classes for the ODBC stuff wasn't protecting against system exceptions, since normally CML classes don't do this because such a thing would be indicative of a serious failure that probably should cause a restart. But the ODBC stuff seems to do it a lot, just purely due to user errors.

[Mikla]

OK, that is good to know. If you see anything in my code I should do differently just let me know.

Thanks.

[Dean Roddey]

Actually, I was wrong. See my post in the Official 2.0 thread. This is bad, because of a change in how VC++ works. You can no longer recover from a system exception like a null pointer deref or divide by zero uisng C++ exception handling. So, that's why CQCServer recently has had this problem where if a driver causes a system exception, it falls over. And if that exeption is caused during startup of the driver, then CQCServer get's caught in an endless loop of starting and falling over.

I didn't realize that this change had occured, so now I have to deal with this somehow. It's not too common, since most system calls will not cause such exceptions, but these ODBC calls seem to do it at a drop of a hat, and that's causing problems.

[Squintz]

Just wanted to follow up that Dean also posted later in the 2.0 post that there might be a way to resolve the compiler issue by setting a flag. He is trying it out now.

[Mikla]

I will also try to put more error checking in my code. Since this driver is so generic, it really provides a lot of opportinutiy for ODBC errors. I can also put a caution statement in the documentation about this until a fix is in place.

[Dean Roddey]

Just wanted to follow up that Dean also posted later in the 2.0 post that there might be a way to resolve the compiler issue by setting a flag. He is trying it out now.

The won't stop whatever the error is that Fivestar was seeing. But it will prevent CQCServer from falling over, so that you see what is going on in the driver. So you'll be able to remove it if it's not working for whatever reason.

Also, Mikla, be sure to make good use of the verbose logging level stuff to help diagnose problems in the field. The rule is:

The more often it would get logged, the higher logging level required for it to be logged.

[Dean Roddey]

Oh, actually, the driver in question was the reminder driver, not this one. But I'm sure that the same thing could happen.

[Mikla]

Yeah, I got some logging in there now, enough so that I can track to what method and variable is causing a problem, but I need to add some more. Also, I need to add the Verbose level "If" statments so that it only logs when logging is turned on to a specific level (like you said, higher levels for higher frequency of logging).

I'll do that for the next drop. I just finished adding multiple Read (SELECT) statement monitoring. User selected (up to 20) when the driver is added.

[Dean Roddey]

Definitely use the verbose level stuff. It's really a problem in the field if a driver starts logging constantly and pushes any useful info out of the logs.

[Mikla]

Here is an update to the Driver. I added a good deal more conditional logging. In addition, I modified the code to allow for up to 20 unique database reads (SELECT) that are continously monitored. I have included the source and manifest files for those that are interested.

I am putting it here instead of updating the first post in case folks want the original driver.

CHANGE LOG:

// v1.1 Jan 6, 2007
// Added multiple read (SELECT) fields based on user selection during driver addition (up to 20)
// Added conditional logging for debugging
// v1.0.0 Jan 5, 2007
// Initial Release
// v0.1.1 Jan 5, 2007
// Added OrderBy parameter
// Added Ability to leave off empty parameters at end of parameter string
// In StringFldChanged method, changed CommResults.ValueRejected to CommResults.Success and added log entry
// to avoid Interface Viewer popup error messages. User gets result from DBValidFlag
// Changed Manifest file to allow user to select connection from a list
//

[IVB]

Just had a thought - I have various events that code around "bad behavior", ie turn off lights/stereo/etc.

Any issues you see with me using this driver inside those events to indicate that it found a bad behavior state and fixed it, then creating a new page where it listed "corrective actions taken"?

[Mikla]

You could use it to log the events. If the corrective actions are a limited number, you could set up a command button to write the corrective action to the DB.

I could help you thru the syntax if you need it.

[1080iAddict]

I have a question regarding the database driver and, well, databases in general.

I am contemplating the recommendation of implementing CQC to an entity that would need to, among other things, track time of personnel. That is, if there were 50 users of the system, an individual member could walk up to the touch panel, enter his ID number on a keypad template, and then a template would open for time entry. The template for time entry would be the same for everyone. However, somehow when the time entries were made, the entries would log to a database that tracks each person separately based on their ID they inputted and, also, the person that needs this data for all members collectively can pull it up at any time under a separate application like excel.

Two questions:

1. I would need the database for two things. First, we would make a list of all the personnel and their ID numbers, and then a password for each person. Each person would need to enter their ID and password not only to enter their time, but to use the other templates in general. Second, the data that gets entered would need to be stored in a database. Can both of these things be done with the driver posted in this thread?

2. If the above question is answered yes, how hard would it be to show the results of the database in a CQC template to show the user a log of their time that's entered?

Thanks!

[Mikla]

ITwo questions:

1. I would need the database for two things. First, we would make a list of all the personnel and their ID numbers, and then a password for each person. Each person would need to enter their ID and password not only to enter their time, but to use the other templates in general. Second, the data that gets entered would need to be stored in a database. Can both of these things be done with the driver posted in this thread?

2. If the above question is answered yes, how hard would it be to show the results of the database in a CQC template to show the user a log of their time that's entered?

Thanks!

It sounds like a fairly complex Interface (the IF part, not the DB part), but looks doable based on the info you supplied. It might be better to write a specialized driver, but I see now reason why you could not use this one. It really comes down to the exact information you want to display and how you want to display it.

Also, it is best if the database is setup in advance with as much data as possible.

1. Dean has a video on a method to log in to CQC. I would just capture the username in a global variable after logging in and use that to get user specific data out of the database (the username must be that same as in the database and be a unique value) and be accessible to other templates. You could also get access level info from the database to augment the access rules built into CQC.

Not sure how you would have the folks put in there time (total hours, timestamp in and timestamp out, etc), but you would just use Insert field to add the time (and date) or hours to the database.

2. Again, it comes down to what info you want displayed and how you want to display it. The DBRead field allows pretty complex SELECTs from the DB, but if you want more that one DB column you have to combine them in the select statement (see concatenate discussion in the docs) and then display them as they are or parse them in the IF. All doable.

I would really need more specific "requirements" before I could answer these questions for sure, but I think it could be done.

[bph]

It sounds like a fairly complex Interface (the IF part, not the DB part), but looks doable based on the info you supplied. It might be better to write a specialized driver, but I see now reason why you could not use this one. It really comes down to the exact information you want to display and how you want to display it.

Also, it is best if the database is setup in advance with as much data as possible.

1. Dean has a video on a method to log in to CQC. I would just capture the username in a global variable after logging in and use that to get user specific data out of the database (the username must be that same as in the database and be a unique value) and be accessible to other templates. You could also get access level info from the database to augment the access rules built into CQC.

Not sure how you would have the folks put in there time (total hours, timestamp in and timestamp out, etc), but you would just use Insert field to add the time (and date) or hours to the database.

2. Again, it comes down to what info you want displayed and how you want to display it. The DBRead field allows pretty complex SELECTs from the DB, but if you want more that one DB column you have to combine them in the select statement (see concatenate discussion in the docs) and then display them as they are or parse them in the IF. All doable.

I would really need more specific "requirements" before I could answer these questions for sure, but I think it could be done.

The database end is trivial and the reporting is fairly straightforward. I'd probably tend towards a custom driver, too. The interface end is where the work would be, but it's doable.

[1080iAddict]

How hard would it be to write a custom driver for this? I mean, not for me - that would be silly. I mean, what kind of time investment should I hire a Board member to write it?

[jscheller]

How hard would it be to write a custom driver for this? I mean, not for me - that would be silly. I mean, what kind of time investment should I hire a Board member to write it?

Have you taken a look at the ReminderDB driver? It's a little less generic then Mikla's work here, which may make it a bit of an easier starting point. The add/update/delete behavior you need for the employees table is all there. Mostly what you'd be doing would be just changing the column names and data types to store people instead of reminders.

The driver needs another function to record a time period. This is the method your template would call to store a time period for later review via Excel or whatnot. As part of this, you'd want to verify that the person's ID number was actually valid (i.e., that it's in the other table). Again, none of this is especially hard... You'd could lift most of the logic from the code that adds a new reminder, or look at Mikla's update mechanism or the polling loop in the DataLogDB driver.

One caveat... The behavior of the drivers that are around right now aren't especially multi-user saavy. For example, the ReminderDB driver uses driver fields to store copies of field data while they're being edited. If two users try to edit reminders at the same time, one of them is going to lose their changes. If your requirements are to use more then one of these stations for employee time entry, some care needs to go into this mechanism to make sure it's atomic (i.e., that everything happens in the scope of a single change to a single driver field).

I'd guess you'd be looking at someone spending a couple of hours to make most of the changes as you've described. However, the real bear in any of these kinds of projects is when your friend looks at it and goes, "oooh, um, we also need this other bit in there" or "Oh, I forgot I need to be able have employees delete times or update them if they made a mistake..." or similar mischief. Typical scope creep stuff. If you undertake this, you just need to make sure you and your friend are on the same page with what the actual requirements are.

Either way, the guts of this probably aren't nearly as bad as it seems, and much of it probably involves cutting and pasting a lot of the existing code that's already around... There's lots of folks around to help if you get stuck. Give it a whirl... :-)

[bph]

One caveat... The behavior of the drivers that are around right now aren't especially multi-user saavy. For example, the ReminderDB driver uses driver fields to store copies of field data while they're being edited. If two users try to edit reminders at the same time, one of them is going to lose their changes. If your requirements are to use more then one of these stations for employee time entry, some care needs to go into this mechanism to make sure it's atomic (i.e., that everything happens in the scope of a single change to a single driver field).

Fine for reminders, but I suggest you probably don't want workers editing their time records much -- especially when it's not their own current record.


I'd guess you'd be looking at someone spending a couple of hours to make most of the changes as you've described. However, the real bear in any of these kinds of projects is when your friend looks at it and goes, "oooh, um, we also need this other bit in there" or "Oh, I forgot I need to be able have employees delete times or update them if they made a mistake..." or similar mischief. Typical scope creep stuff. If you undertake this, you just need to make sure you and your friend are on the same page with what the actual requirements are.


I'd budget more like a couple of days than a couple of hours... but maybe I'm just not very good :)

It also depends on what the "database" is. A fully relational DB like SQL is more robust, and, in some ways, easier to work with; but it requires a server (or adds load to an existing server like the CQC MS). If it's a business, you (or your friend) should really scope out exactly what you want and, if you're paying money, get a quote/contract.

[jscheller]

Fine for reminders, but I suggest you probably don't want workers editing their time records much -- especially when it's not their own current record.

Sure, see there you go... "Add to the requirements list, workers should be able to edit entries that only THEY have made, and ONLY the very last entry they made in case they made a mistake. Attempts to edit earlier entries should be denied, unless the user name is 'admin' or something similar. If a regular user deletes their last entry, they shouldn't be allowed to delete any other entries (the only operation possible at the point should be to add a new entry). Also, changes and deletions should be recorded in a change log table with the user name, the date/time the modification was made, the old and new values. Entries stored in the change log table should be automatically exported to a text file for archive purposes and automatically deleted after 180 days..." And so the torture begins...Bwahahahah...

Seriously though, my multi-user concerns were actually meant to apply to just adding records. If the driver uses fields to form a temporary record buffer that the templates can manipulate, then just adding new records runs into trouble if more then one user is modifying the fields at the same time.

If it's a business, you (or your friend) should really scope out exactly what you want and, if you're paying money, get a quote/contract.

1080i, the above is probably the best advice you're going to get around here. :-)

[Dean Roddey]

I really don't think that a CQC user interface is an optimum way to create a client for this type of application either. It really should be a hard coded interface that is designed for data entry type stuff. The CQC IV is really too generic and too oriented towards home automation to be good for this kind of thing.

You could make it a hundred times simpler by just using a dedicated client program talking directly to a relational database.

[1080iAddict]

Ok, but here is the idea. I am contemplating the idea of 'donating' a touch computer to my volunteer fire station where i am a member. Using CQC, we can control an auxiliary radio scanner using the VLC drivers and technology I developed at home. We can also automate things at the station when an alert is triggered, such as engine bay lights, unlocking doors, etc. We can control the media system at the station (what there is of it). But an important aspect of a touch panel would also involve tracking "duty hours" of members, i.e., time spent at the station, as well as training hours, etc. As of now, its simply written into a book using the honor system. If it can be touch screen based, it would be a major advance forward.

I should add that if a prototype is built at our station, I am quite confident it can be implemented at any station (we have 40 volunteer stations in our department alone). I see a potential huge client base for CQC. True, there is software developed for duty hours, trainings, etc. but as a volunteer station, we are mostly a DIY group of members. So, if i can get a driver working that tracks this stuff, i may move forward and once other stations see it during visits, etc, i bet you it can spread like, yes, like wildfire.

[beelzerob]

i bet you it can spread like, yes, like wildfire.

:-D :-D

Bravo! :tounge

[Bodshal]

Ok, but here is the idea. I am contemplating the idea of 'donating' a touch computer to my volunteer fire station where i am a member. Using CQC, we can control an auxiliary radio scanner using the VLC drivers and technology I developed at home. We can also automate things at the station when an alert is triggered, such as engine bay lights, unlocking doors, etc. We can control the media system at the station (what there is of it). But an important aspect of a touch panel would also involve tracking "duty hours" of members, i.e., time spent at the station, as well as training hours, etc. As of now, its simply written into a book using the honor system. If it can be touch screen based, it would be a major advance forward.

I should add that if a prototype is built at our station, I am quite confident it can be implemented at any station (we have 40 volunteer stations in our department alone). I see a potential huge client base for CQC. True, there is software developed for duty hours, trainings, etc. but as a volunteer station, we are mostly a DIY group of members. So, if i can get a driver working that tracks this stuff, i may move forward and once other stations see it during visits, etc, i bet you it can spread like, yes, like wildfire.

My initial reaction: Eeek!

Whilst tracking of time is a worthy goal, and perhaps not well suited to CQC, it is at least not life-or-death stuff.

Opening the doors for fire trucks to get out, on the other hand, is. While I have every faith in CQC, is it really suited to that sort of thing?

Chris.

[Mikla]

I would think you would have to make the time entry system very simple. Somthing like a finger print reader that would recognize the person, display a simple list of tasks (duty, training, maintainence, etc.) that they would press to start/stop the task.

Having been a volunteer firefighter/EMT, I would think you might get resistance to anything that required more than that. Especially on a 2am call when you are still half asleep when you arrive at the station (I barely got on the right truck, never mind remember to log my time).

But then again, that was many years ago and we had a brand new Radio Shack TRS Model III!

I like the idea of automating the lights, door, etc. I can't tell you the number of times I got to the station just to stand outside waiting for someone else because I forgot my key. I even remember an instance of someone not putting the door up all the way.... Oops.

[1080iAddict]

Opening the doors for fire trucks to get out, on the other hand, is. While I have every faith in CQC, is it really suited to that sort of thing?

Chris.

As a credit to CQC, every - and I mean EVERY - automation task I have programmed for the past year has performed FLAWLESSLY. Certainly, there is the initial "try the new thing, gadget and driver" period, but after that happens, I have never had anything work sporatically. This is why I love CQC - it is so stable. Then again, most of what I do defaults to the simple stuff. I am not as smart as most of you guys.

Besides - we all carry key cards to open the door if there is an issue! :-)

[1080iAddict]

I would think you would have to make the time entry system very simple. Somthing like a finger print reader that would recognize the person, display a simple list of tasks (duty, training, maintainence, etc.) that they would press to start/stop the task.

Having been a volunteer firefighter/EMT, I would think you might get resistance to anything that required more than that. Especially on a 2am call when you are still half asleep when you arrive at the station (I barely got on the right truck, never mind remember to log my time).

But then again, that was many years ago and we had a brand new Radio Shack TRS Model III!

I like the idea of automating the lights, door, etc. I can't tell you the number of times I got to the station just to stand outside waiting for someone else because I forgot my key. I even remember an instance of someone not putting the door up all the way.... Oops.

Anything that goes beyond pulling out the looseleaf binder to log a call, duty hour, or training hour is an advance for a volunteer station. I am lucky enough as a lawyer and a fire fighter that I can afford to donate, er, earmark, a touchscreen "donation" to my station if I embark on this project. I want to automate our station, and then use it as a showpiece for other stations. Dean can make a bundle if this works. All I need is a simple way for data entry and recall. No finger print readers, etc. We have most of it in CQC already. I build an IV page, a pop-up comes up to enter the volunteer's ID number, and then a template comes up with the ID number as the global variable. When that volunteer selects either duty hour, training hour, etc., pre-set template stuff comes up, they enter the hours, and its logged. This is what I thought the driver in this thread would accomplish.

OK dudes. What if, instead, I research software that does all this already. What should I look for in that software to allow you guys to write a driver to interface it with CQC?

Remember, its a tough sell. I am looking for simple. It costs $1,700 for one set of fire turn-out gear - ONE SET. No way I will be able to get these guys to buy software. Sure, I can sell them CQC and a touch computer if it does all I envision. Certainly the other stations will buy it if i donate the templates. I got no problem with that. But where all this started is that I need to log simple stuff like duty hours, etc. The rest i got handled already, like radio controlling the scanner - - - - see my post on that issue:

http://208.101.20.242/vb_forum/showthread.php?t=3002

So, what i am thinking here is.... a little love... a little donated time.... a little work.... all for a good cause and we make a prototype. Then we kick arse and sell it to every other volunteer station.

[beelzerob]

Besides - we all carry key cards to open the door if there is an issue! :-)

Same as has been discussed before elsewhere....as long as everything will still work manually, then nothing is really risked...the system won't work any less than it does now. Just avoid the dreaded "the server is down so I can't turn on the lights", and everything will be ok. ;-)

[jscheller]

Okay.

Take a look at the ReminderDB app and the templates I wrote. If you think the way that driver works conceptually makes sense for this, I'd be willing to donate some time to get the basics going. I like the idea of helping the volunteer fire department and all, but really I'm just doing my part to hope that some future CQC sales will keep Dean stocked with spare power supplies. :-)

I think you could do the basic data entry without a lot of gruesome work. Pulling reports out is another game, and you'll probably want to look into another outside application to do that part of things (a little Access DB or something would work fine.)

[bph]

Okay.

I think you could do the basic data entry without a lot of gruesome work. Pulling reports out is another game, and you'll probably want to look into another outside application to do that part of things (a little Access DB or something would work fine.)

I would definately keep the reporting outside of CQC, except maybe for a "quick check" function where a volunteer could maybe see his/her total hours when punching out or something.

Question, though: is security an issue? Are you considered that a volunteer might input too many hours (or to few) or that one would "punch in" for another? In a business time-card you would want to prevent that, and use personal pins or something. Is that the case here?

[1080iAddict]

Security is not an issue. It is based on the honor system. Everyone has access to the looseleaf binder now and nobody would ever know if someone cheats. With that said, security would certainly be an improvement if it is possible without too much work.

[klindy]

What if you use RF tags to 'clock-in'? Of course someone could leave it at home or wherever but it's fast, requires no time by the user and unique to that user. Since everyone needs to get into a vehicle it would seem locating a reader wouldn't be too difficult.

[jscheller]

A rough cut at a driver for 1080i, and probably a better place to discuss this further rather then continuing to hijack Mikla's thread... :-)

http://www.charmedquark.com/vb_forum/showthread.php?t=3654

[1080iAddict]

Thanks! Hijacker now surrenders. :-)

[bph]

Security is not an issue. It is based on the honor system. Everyone has access to the looseleaf binder now and nobody would ever know if someone cheats. With that said, security would certainly be an improvement if it is possible without too much work.
I ask for usability resons... clicking "joe" from a list and then "clock in" is less work than entering your ID code and your password.

[IVB]

What's the difference between this driver and the DataLogDB driver?

Starting to make some headway on db's inside CQC, trying to understand which tool to use for which task.

[Mikla]

This driver is a very generic driver with no application in mind. With some limitations on what you can return (only a one column resultset), you can query the database with almost any SQL query.

Therefore, there are fields that allow you to SELECT, UPDATE, INSERT or DELETE data from the database.

I created it more for learning purposes than anything else, but it is pretty powerful. It does expect the user to have some basic knowledge of how to structure a SQL query.

The DataLogDB driver is a more application specific with the expectaction that certain tables and columns will be available.

The nice thing is that either can be easily modified to fit a specific purpose, though it might be easier to modify the DataLogDB driver to an application than this driver.

Check out the docs in the first post and if you have specific questions, let me know.

[IVB]

Question: I was trying to create a "Find Name" button on my callerid history screen, couldn't figure out how to do that. What type of widget would I put the dataread into?

Request: Any way to implement a like clause? That way I could either type in a name (no idea how), or at least pick a first letter, and have the "where name like 'H%'" populate (what type of widget, no idea).

Thanks again for doing this.

[Mikla]

You would just use a command button to generate an action, something like:

Devices::FieldWrite(DBConn.DBRead1, TableName, ColName, ColType, NewValue,WhereCol,WhereVal,WhereType, OrderBy)

This will determine what is in the DBRtnList1 for you to read.

As far as a LIKE clause....

Hmmm... I am not some place I can test it. But you might be able to trick the driver by doing something like:

TableName, ColName, ColType, NewValue,1,=1 AND column LIKE '%somevalue%',Card1, OrderBy

I used "Card1" so that the driver does not try to format the value as a string. I think this should give you:

SELECT ColName FROM TableName WHERE 1=1 AND column LIKE '%somevalue%' ORDER BY ColName OrderBy

Which is a legal SQL query that is no different than a single LIKE clause.

[jscheller]

That way I could either type in a name (no idea how)...

You can put a text widget on a template, and in the properties there's a check box for "Allow direct field writes" or something similar. If this box is checked, when the widget is open in the viewer you can double-click on it and the IV will pop open a little dialog to let you pound a value in. It's crude, but it works. That's how I'm getting data into the ReminderDB database through the IV.

There's a plea to add support for actual data entry widgets here...

http://www.charmedquark.com/vb_forum/showthread.php?t=3407

...or at least pick a first letter, and have the "where name like 'H%'" populate (what type of widget, no idea).

I kinda think I see where you're headed with this. There might be some clever way to do some of this directly in the DataLogDB driver where we can rely on a fixed table structure... Let me look into it a little there when I'm in that code later tonight/tomorrow...

[IVB]

I'm trying to use this to read a table and getting an error. What does the following mean?


ERROR: In ColBinding Method. cName: Numb can not be bound


BTW, Numb is the name of the field. Here's the call i'm making:

[CQC].[dbo].[contacts_list], Numb, Card1, '',1,=1,Card1, OrderBy

[Mikla]

I will have to look at the code tonight, but that indicates it did not find the correct data type to bind the database field with. That is odd, because Card1 is one of the data types allowed in the driver so it should never reach that error.

Let me look at it tonight and I will get back with you.

Sorry not to get back sooner, but I was in Vegas.

Hmmm... just thought of something. Try eliminating any spaces before and after the commas... [CQC].[dbo].[contacts_list],Numb,Card1,'',1,=1,Card1,OrderBy

I may not be cleaning those up when I parse the string.

[IVB]

Well, a different error now anyhow. I'll check on my end, perhaps i'm doing something dumb.

After stripping out the spaces, I get this as soon as I hit the button:


Socket write operation failed

The operation would block

[Dean Roddey]

Assuming that's not a bogus error, it means that the other side is backed up and cannot accept any more data.

[Mikla]

I confirmed that you can not have spaces. I will have to either make a change to allow spaces (in case you want them, in which case I will have to all quoted strings between the commas for certain fields) or update the docs to reflect no spaces.

On your second error, was there anything else in the logs? I have some pretty good error checking in the code so it should say something if it was trapped in anyway.

[IVB]

I still can't get this to work but i'm 99% sure i'm goobering the DBRead1 command. Can you give me an example of something that works? Specifically i'm getting an m_OrderBy:(whatever parameter I put last) failed.

[Mikla]

Try:

[CQC].[dbo].[contacts_list],Numb, Card1,,1,=1,Card1,ASC

[IVB]

Thanks. I'll keep playing around with quotes, after removing the space before the Card1 above I now get:

(CQCKit,MEng.System.CQC.Runtime.CQCLogger.431,Stat us-App Status,0-0-0)
ERROR: In ReadDB Method. Execute: SQL:SELECT DISTINCT Numb FROM [CQC].[dbo].[contacts_list] WHERE 1 = =1 ORDER BY Numb ASC failed.

I tried removing the =, that didn't work either.

What db are you using?

[Mikla]

Hmmm... it looks ok. I tried it against a database I have here and just replaced the table and column name and it worked. Try...

contacts_list,Numb, Card1,,1,1,Card1,ASC

I am wondering if it has something to do with the way you are referencing the table.

I am using SQO 2000.

[IVB]

I noticed you removed the = sign. Doing that, with or without the [cqc].[dbo], i get a fetch error in GetResults Method.

Putting the = sign back in returns the prior error.

here's a thought: In high log mode, can you always dump the exact sql statement you're about to run in the logs? That'll help me determine if I have a quoting issue or not.

[IVB]

Has anyone gotten this working with SQLServerExpress?

I really have no idea why I can't connect to it with this driver, but the DataLogDB driver seems to work fine.

[Mikla]

Sorry folks for being MIA for awhile. I had some things to take care of (and still do). I'll try to take a look at the driver this week (and make the change to log the SQL statement). I'll also try to run it with SQL2000, SQL2005 Express and Access.

If anyone can write their Database structure to a SQL query file, that might help as well since I can rebuild the database (tables/columns) you are having problems with.

[zaccari]

Sorry folks for being MIA for awhile. I had some things to take care of (and still do). I'll try to take a look at the driver this week (and make the change to log the SQL statement). I'll also try to run it with SQL2000, SQL2005 Express and Access.

If anyone can write their Database structure to a SQL query file, that might help as well since I can rebuild the database (tables/columns) you are having problems with.Is there a version of this driver for the current betas? I'd like to log some simple data to read elsewhere.

Thanks,
Russ...

[Mikla]

I'll try to do it this week.

[robolo]

Yes, I would be looking for that too. Thanks in advance

[Mikla]

I uploaded a new driver package to the first post for v2.4.26

[robolo]

Thanks for working late on that one (3 AM I noticed!!). The new version loads without complaining.

[zaccari]

Can I get an example of how this gets configured? I installed the MySQL ODBC driver on my server and created a DSN but I don't see how I can get from there to having a place to store data.

Russ...

[Mikla]

First, the driver is meant for someone with some understanding of SQL queries since all you are doing is building a query that the driver continues to poll.

Beyond that there is capability to add, update or delete data, again using SQL queries.

When you install the driver it will prompt you to select a DSN based on the DSNs it finds available. You then enter the number of datasets you would like to work with and the driver creates that number of Read, Count, Value and Stringlist fields.

For instance. Let's say you have a Table that contains data like:

Table Name: MyTable
Column1 Name: MyColumn
Column1 Type: String
Column1 contains the strings "Snail", "Ant", "Snake"
Column2 Name: NewColumn
Column2 Type: String
Column2 contains the strings "S","A","S"
So, now you have a table called "MyTable" that looks like:

MyColumn NewColumn
---------- -----------
Snail S
Ant S
Snake S

In the IV, you might have a Command Button. In the OnClick Action you could do a FieldWrite to the Field Moniker:DBRead1 with a Value of:

MyTable,MyColumn,String,,,,,ASC

When you press the button in the IV, the driver would translate this into a SQL query:

SELECT MyColumn FROM MyTable ORDER BY MyColumn ASC

Then the driver would execute the query on the database defined by the DSN and continue to poll that data base executing the same query each time.

When each query is executed, the driver would fill the DBValue1, DBCount1 and DBRtnList1 with the results of the query. In this case they would be:

DBValue1 = "Ant" (since it would be the first row based on the ASC order)
DBCount1 = 3 (there are a total of 3 rows returned by the query)
DBRtnList1 = "Ant" (a StringList of the all the values in Alpha order)
"Snail"
"Snake"You could then display these in the appropriate widget types.

Anther example... say you have the same button, but this time the value for the field is:

MyTable,MyColumn,String,,NewColumn,S,String,ASC

The driver would then create the Query:

SELECT MyColumn FROM MyTable WHERE NewColumn = 'S' ORDER BY MyColumn ASC

The results would return:

DBValue1 = "Snail" (since it would be the first row based on the "Where" clause and in ASC order)
DBCount1 = 2 (there are a total of 2 rows returned by the query)
DBRtnList1 = "Snail" ("Ant" is not included since it does not meet
"Snake" the "Where" clause)
Now, let's say you had pressed the command button and later new data was added to the database (from whatever source):

MyColumn: "Duck"
NewColumn: "S"
The IV would update automatically update (on the next polling cycle of the driver) to:

DBValue1 = "Duck" (since it would be the first row based on the "Where" clause and in ASC order)
DBCount1 = 3 (there are a total of 3 rows returned by the query, though there are 4 rows in the table)
DBRtnList1 = "Duck" ("Ant" is still not included since it does not meet
"Snail" the "Where" clause)
"Snake"

The DBUdate, DBInsert and DBDelete fields work using the same input value format but perform those particular functions... E.g., to add "Duck" in the example above you would do two FieldWrites. The first one to the field Moniker:DBInsert1 with a values of:

MyTable,MyColumn,String,Duck

And the second one to the field Moniker:DBUpdate with the value of:

MyTable,NewColumn,String,S,MyColumn,Duck,String

The driver translates this into two SQL queries of:

INSERT INTO MyTable (MyColumn) VALUES ('Duck')
UPDATE MyTable SET NewColumn= "S" WHERE MyColumn = "Duck"
The insert query creates the row and adds the value "Duck". The update query that adds the value "S" to that same row.

The trick here is that NewColumn must accept "Null" values. Hmmm... now that I look at it, I should modify the driver to allow multiple inserts in one pass so that "Duck" and "S" can be inserted at the same time.

CAUTION:

Becareful with DBUpdate and DBDelete. If you don't get the "Where" clause right, you could end up modifying lots of data you don't want changed!

Anyway, hopefully this is either enough to get you going... or to complicated and you'll throw your hands in the air. Like I said, it does count on you having a basic understanding of SQL queries.

[zaccari]

When you install the driver it will prompt you to select a DSN based on the DSNs it finds available. You then enter the number of datasets you would like to work with and the driver creates that number of Read, Count, Value and Stringlist fields.
Ahh, herein lies my problem. I was going to put the driver on a box OTHER than the one I'm running admin on. It doesn't see the dsn, hence it won't work. Now I understand the problem.

Russ...

[zaccari]

Ok, I added the driver from the machine where the ODBC driver is installed and it showed up. I can test the connection inside the ODBC configuration but the driver doesn't connect.

Russ...

[zaccari]

If I were to do a CID database, with multiple fields, how would I do this? Lets say my fields are the following strings: name, number and time.

Russ...

[Mikla]

The way I would do it is...

As a prereq, in your DB, create a table called "tblCID" (using whatever tool you use to admin the MySQL DB)

Then create columns:
CIDName (nvarchar(50))
CIDNumber (nvarchar(50))
CIDDateTime (Date) w/ default of GetDate()

The SQL syntax to do this is:
CREATE TABLE tblCID
(
id_num int IDENTITY(1,1),
CIDName nvarchar(50),
CIDNumber nvarchar(50),
CIDDateTime date DEFAULT GETDATE()
)
Now the database/table is ready to use.

Whenever you want to add a caller to the table, you would do the following 3 actions:

Field: Moniker:DBInsert1
Value: tblCID,CIDName,String,NameValue

Field: Moniker:DBUpdate1
Value: tblCID,CIDNumber,String,PhoneNumber,id_num,SELECT @@IDENTITY,C4

This will create the SQL queries:
INSERT INTO tblCID (CIDName) VALUES ('NameValue')
UPDATE tblCID SET CIDNumber = 'PhoneNumber' WHERE id_num = SELECT @@IDENTITY

Note: This is a work around until I change the driver to allow you to insert all the values at once. @@IDENTITY gets the id_num value of the last INSERT. (Not tested)

The CIDDateTime column will automatically be filled in with the current DateTime. The id_num column will automatically get filled with the next increment(1) from the last id_num value.

Now, you have values in the table. To view them with the latest call first, you would the action:

Field: Moniker:DBRead1
Value: tblCID,CONVERT(nvarchar(50), CIDDateTime, 120)&'|'&CIDName&'|'&CIDNumber ,St,,,,,DESC

Then, you could use a Field List Browser widget and set the field to RtnLst1 to return the list to the IV. The output on the IV would be something like:

2009-03-26 14:12:32|Joe Smoe|555-555-1212
2009-03-26 14:22:12|Mary Smoe|555-555-2121

The "|" is just a delimiter I choose in case you would rather parse the string in the interface editor.

DBValue1 would hold the last number called in the same format.

I think this should help... if not, let me know.

You did highlight some things I need to look at for the next version:
1. Add ability to Insert multiple values
2. Allow sorting by a different column (default to selected column)
3. Look at a way to return a specfic number of rows, e.g., TOP 10

[zaccari]

I still believe that my driver is "right". I set it up the driver and it came up with the ODBC source that I previously set up (and tested properly). The driver sits with Wait for Comm Resource. I only have DBDelete, DBInsert, DBRead1 and DBUpdate available, all the rest of the fields (including the $ fields), including DBCount1, DBValidFlag1, etc, are red. Trying to put a command in DBInsert returns an error about the driver not being connected to the device.

Russ...

[Dean Roddey]

That means it can't open the database. This might be a permissions issue, I dunno. Try setting up the CQC service to run under a regular administrative account, if you haven't already. Also make sure you create a SYSTEM DNS, not a USER one (can't remember if you mentioned which you did, so just in case...)

[zaccari]

It was a user DNS instead of a system DNS. It is connecting. Onward. Thanks Dean.

Russ...

[zaccari]

Ok, I believe when I do the insert, the row gets added. The log shows an error on the InsertDB Method though with the SQL statement: INSERT INTO CID (name) VALUES ('-UNKNOWN CALLER-').

Russ...

[Mikla]

Ok, I believe when I do the insert, the row gets added. The log shows an error on the InsertDB Method though with the SQL statement: INSERT INTO CID (name) VALUES ('-UNKNOWN CALLER-')

Hmmm.... that's odd. The "Execute" statement that does the insert and the error should be mutually exclusive... they are in a Try...Catch block. Is this the complete error statement?
ERROR: In InsertDB Method. Execute: SQL:INSERT INTO CID (name) VALUES ('-UNKNOWN CALLER-') failed.

By chance, did you try a "DeleteDB"? I noticed, in the code, I used the "Update" DB log message for that by mistake.

If I get some time today, I'll setup a simple CID DB and try it out. I'll also go back through the code to make sure everything is working as it should... and maybe add the additional features.

[zaccari]

Yes, that is the exact error. I'm definitely using an insert followed by an update. My sole purpose is to have quick throw things into a database and use php to get data out for web formatting. I have little interest in putting CQC in charge of reporting type things.

Russ...

[robolo]

Experiencing a strange behavior when trying to run DBUpdate

If I have a single command:
Devices::FieldWrite(SQL_DBConn.DBUpdate,Irrigation Settings,OnOff,Int1,%(GVar:OnOff))
It runs fine. But if I add a second DBUpdate action immediately after

Devices::FieldWrite(SQL_DBConn.DBUpdate,Irrigation Settings,OnOff,Int1,%(GVar:OnOff))
Devices::FieldWrite(SQL_DBConn.DBUpdate,Irrigation Settings,Sunday,Int1,%(GVar:Sunday))

It kicks the Generic Database Driver Offline, the driver state becomes "Wait for Comm Resource" and the second action does not process. I get an error message that the driver is not connected.

[Mikla]

That's and interesting one. I'll have to look at the code to see where that can happen. Is there anything in the log? Make sure you put verbose on high, the driver has quite a bit of logging in it and that will help pin down the problem.

[robolo]

Thanks for reminiding me about setting the log to high. Previously I never saw anything in the log. Now with log on High I get:

03/28 20:17:27-cqcmaster, CQCServer, CIDOrbSrvWorkThread_1
{
CIDMacroEng, CIDMacroEng_Engine.cpp.1276, Status/App Status
Exception 'CollectErrors.BadIndex' was thrown from line 827
MEng.User.CQC.Drivers.DatabaseGeneric.DriverImpl.C ard4Array
}
03/28 20:17:28-cqcmaster, CQCServer, CQCDrv_SQL_DBConn_Thread4
{
CQCKit, CQCKit_DriverBase.cpp.2471, Status/App Status
Driver 'SQL_DBConn' is trying to get its comm resource
}

}

[robolo]

Actually it does not have to do with the combination of the two lines together. When I run the second line alone :

Devices::FieldWrite(SQL_DBConn.DBUpdate,Irrigation Settings,Sunday,Int1,%(GVar:Sunday))

the log shows this:

Exception 'CollectErrors.BadIndex' was thrown from line 827

[Mikla]

Oh... that's not good.... it looks like I am trying to find an index (based on the number of DBReads to monitor when the driver is installed) on a field that does not use an index. Ok, I'll try to look at this today... I need my second cup of coffee before I can get my head around this (especially since everything works fine on my test IF screens).

[robolo]

Mikla,
Have you had a chance to look at this yet?

[Mikla]

Sorry, not yet, I got caught up in my kitchen remodel and working on improving the Leviton driver. I'll try to look at it today.

[Mikla]

FYI... I have been working on this. For some reason, I didn't document this driver very well and now I am having a good deal of difficulty trying to figure out what in the heck I was trying to do. This question has come up several times:

What the $#*^# did I do that for?

[robolo]

What the $#*^# did I do that for?

Ahhh yes. I am very familiar with that expression. Used it a lot when I was updating a VB app that I had written a few years ago. Turns out there was always a good reason I HAD done it that way, I just didn't document it and had to learn the hard way each time.

[beelzerob]

Heh..ya, that's a pretty familiar feeling. My new years resolution for this year was to always add some simple comments in the code whenever I modified something or wrote something atypical. So far so good.

[brotsten]

Sounds like a club, I'm in.

Brian

[robolo]

I have continued to work through this a little more. It seems that after each database write the ODBC connection goes offline for a few seconds and then reconnects. Initially I was collecting all the data changes made on a template and then sending a series of write commands when the template was closed. That would always cause an error. Now I just update the database with each template change. It works fine now. The only residual problem is that I have to include a WaitForDriver command after each button press so that another command is not sent too soon.

[robolo]

Driver is working well except for trying to use Convert with time expressions. I have a Sql Server 2008 database table "General" with a column "Starttime" with datatype "Time" (a new data type in sql2008). It is stored in the database as 04:20:00. I would like to return the value as 4:20AM.

From within the database I can write a SQL query like this:
SELECT Convert(nvarchar,StartTime,0) from General
and the returned value is "4:20AM" - which is the format I want

In CQC I can write
Devices::FieldWrite(IrrigationData.DBRead9, General, StartTime, String)
and I get the return value of "04:20:00" as expected.

However if I try to run the command
Devices::FieldWrite(IrrigationData.DBRead9, General, Convert(nvarchar,StartTime,0), String)
The command fails as evidence by the DBValidFlag9 being False. According to the driver notes the Convert should be valid. Why is it not working?

[Mikla]

Hmmm... that should work OK. Is there anything in the Logs? It builds the SQL query:


SELECT DISTINCT Convert(nvarchar,StartTime,0) FROM General

Which should work. You could try (note the "AS MyTime"):


Devices::FieldWrite(IrrigationData.DBRead9, General, Convert(nvarchar,StartTime,0) AS MyTime, String)

Just to make sure there is a column name.

I'll look at this some more. Otherwise, I am glad to hear it is, somewhat, working. I am still looking at the code, but the Leviton stuff (and my Kitchen remodel) has a bit more priority at the moment.

Thanks for the excellent debug input!

[robolo]

Mikla,
Tried using the As Mytime but still no good. When I turn on logging I see this error:

ERROR:In ColBinding Method. cName:Convert(time can not be bound

and that is it. Seems like there should be more on that error message (the parenthesis is not closed). Does this make sense to you?

[Mikla]

OMG, I am such a friggen hack!

The code is parsing the parameters by splitting the input string at every comma. So it thinks you have four parameters versus three... it is splitting the Convert function in half.

I'll put out a quick fix today. It'll probably be the use of an escape char (like \) before commas you don't want to split at.

[Mikla]

OK, there is a new version up (1.3) in the first post that implements the comma fix. Somehow I skipped 1.2, but worked off the 1.2 code (I think it just had some additional logging stuff). Hopefully, i did not break anything.

Just put a backslash in front of any commas that are used in a parameter. Your code would look like this:


Devices::FieldWrite(IrrigationData.DBRead9, General, Convert(nvarchar\,StartTime\,0), String)


Let me know if there are any problems.

I still need to spend some real time on this code to fix other things and implement some other features.

[robolo]

OK so after replacing the driver with 1.3 ver and using backslashes I am getting this in the log file:

ERROR: In ColBinding Method. cname: Convert(nvarchar,StartTime,0) can not be bound

Looks like we are getting closer

[Mikla]

Ugh. Ok, let me look at it. I tried () around concatenated fields and it worked...let my try your code.

But first, I have a bigger problem. A storm just passed through here and we lost power for the last hour... the Automation Server UPS failed and now everything is amuck. When I got the server back up, I got all kinds of HD errors and the CPU pegged at 90%... driven by the CQCserver.exe... just shoot me.

[Dean Roddey]

The best way to deal with parameter is the standard command line parsing stuff, which will do all the work for you. It's space separated values which have to be quoted if they have any spaces inside them. That's a pretty standard parameter string format and the code is already there to break that out for you.

[Mikla]

I think I looked at ParseCmdLine before. But because there can be optional parameters like:

parm1,param2,param3,,param5

I went with comma seperated parameters so I could tell when an optional parameter was left out.

I suppose I could change it to:
param1=value1 param2=value2 param3=value3 param5=value5

But then I would worry about folks putting spaces around the equals sign.

Now if ParseCmdLine could use commas and I could put params in quotes that contained commas like

value1,value2,"and, this is value 3",,"then, value5" ==> yields 4 parameters

that would be nice. I guess it comes down to what is the best method for using optional parameters?

[Dean Roddey]

There's also methods for parsing CSV type lines, which will allow you to selectively quote parameters that have commas in them, and not otherwise, and it'll deal with empty values.

[Mikla]

Ah... that's right... the "ParseQuotedCommaList" method. I don't think you have updated the docs for that yet? I assume it would be in the StringTokenizer class.
Thanks.

[Dean Roddey]

Yeh, it's on the tokenizer. I think it's documented in the 3.0 beta docs. Actually, there's a quoted comma list and a CSV. If you don't want all of them to have to be quoted, then CSV would be better probably.

[pseigler]

Not sure if anyone's interested, but for those db guru's out there the ListBuilder driver http://www.charmedquark.com/vb_forum/showthread.php?p=68844#post68844 supports the idea of using multiple custom queries to access db's. it stores it's configuration into a database so everything is completely stateful.

i originally created it to support pulling data out of db's into stringlists, but have since used it to keep CQC Var states in a db when CQC resets, pull data out of the DataLogDB driver db, manage a couple MyMovie interfaces by talking to the db directly, etc.

[personalt]

Is there a place to set the refresh time of this driver? I didnt see a place to set that. i seem to be getting a 7 to 10 second refresh time but cpu usage is almost none so wanted to see if i could up the refresh time a little.

[Mikla]

I think the poll time is hardcoded to one second for every "Select" statement that you want to monitor.

[pseigler]

Currently, the driver auto updates every 5 seconds. i guess i could add the ability to configure the refresh rate dynamically if its critical.