Saturday, September 1, 2018

Worked Over by MySql Workbench?

Just went through angina after installing the latest version of MySql Workbench 8.0. Kept getting bad handshake errors connecting to a remote MySql database. Turns out that data base still runs on an antiquated version of MySql 5 and Workbench 8 is a typical liberal nanny state interface program that doesn't like conservative older database servers and feels it knows better than everyone else and that anyone who wants the benefits of its use better provide newer databases to use with it. Welllllll, upgrading that particular database just isn't in the cards this minute. Connecting remotely with it is. End shot bottom line, went back to Workbench 6.3.10 and SCREW Workbench 8.

Sunday, August 26, 2018

OhMySQL! Ya gonna Be the Death of Me!

Finally returned to my hobby website, www.StreetlightSite.com, after like years of neglect. That site is totally driven by data stored on a MySQL database server and my extremely budget hosting plan hasn't had an update done to that server since around when I last updated any data on it. Suffice to say when I renewed my own localhost virtual server to do update development work on, I needed to start downloading regular database back ups again. Well, I'd been so out of it with MySQL I kind of forgot what I needed to do. Thought it would be as easy as exporting the whole shebang from one and importing into the other. WRONG!
Kept getting the wonderful MySQL error 1024 syntax error where TYPE=InnoDB. Well, I was just about to stick a TYPE up MySQL's InnoDB when I thankfully discovered that my virtual server's MySQL 5.6 is virulently WORDIST! It is prejudiced against the word TYPE. Only native born, full blooded ENGINEs are allowed. Bottom line? Just replaced TYPE= with ENGINE= and order was restored to the universe.
Follow @StreetlightSite on Twitter

Thursday, May 11, 2017

Don't Cast Numerics to Varchars In SQL Joins

Had a performance issue with a stored procedure recently and discovered why the thing was choking, taking minutes to run. It had a most unfortunate join factor among the several joins in a complex query. The join was a varchar field matched to an integer field. For whatever reason long lost in time, the writer decided to cast the integer as a varchar to map it against the crazy varchar field, whose values could be anything from numbers to symbols. That field's data content was out of our control, coming from an outside source and possibly needing to be preserved as is for auditing. The only values in that field however that meant anything to us were the numeric ones. What fixed the ridiculous runtime of this proc was to reverse the casting. I changed the join to instead cast the varchar as an int to match against the real int. To do that I had to make sure the varchar was numeric inside the cast. join dbo.StupidTableWithIdioticVarcharField s on cast(case when isnumeric(s.StupidField) = 1 then s.StupidField else 0 end as int) = NormalTable.NormalIntField The speedup was breathtaking. It would've been more breathtaking if the original data was designed, or at least redesigned differently, but sometimes you just gotta work with what you got.

Naming Temp Tables in Stored Procedures called by Others

Beware the temp table names you use when naming such tables in a stored procedure that in turn gets executed inside other stored procedures. If both the executing and executed procedures have temp tables with the same name, you might be in for a nightmarish episode of tearing your hair roots out. If using the same #TableName in both procedures, they must have the same exact column structure. You later add a column to the executor and don't do the same to the executed, you will end up tempted to execute yourself before you figure out why you keep getting insert errors in the child proc. Bottom line, keep temp table names different in procs called by other procs.

Saturday, September 19, 2015

Very happy with reconditioned Dewalt drill set from CPO


Originally submitted at CPO Dewalt

20V MAX Cordless Lithium-Ion Premium 3-Speed Drill Driver - DCD980M2R. (2) 20V MAX 4.0 Ah Lithium-Ion Batteries. Charger. Side Handle. Carrying Case

Very happy & surprised. Didn't expect new XR batteries
By Jeff from Charlotte on 9/19/2015

5out of 5
Pros: Powerful, Easy To Use, Good Battery Power
Bought kit basically for the 4ah batteries and charger to use with my Dewalt saws. The drill was like a freebie for the price and is a great drill, more powerful that the other one we own and that stabilizer handle came in handy very fast as I drilled into tree roots I'm trying to destroy. I didn't expect to get the new XR batteries with this and that made me very happy! They shipped very fast too. I'm usually loath to order refurbished items, but this one turned out great!

Friday, January 23, 2015

Selecting Recordset from SQL Server with Same Name Columns

This applies to SQL Server 2008 R2 and VB.Net.
Thought I'd share what I discovered inside a VB.Net project converted from old VB6 code. We have a recordset that used to be brought in from SQL Server 2008 R2 via ADODB that had 2 fields from different tables with same name.
The old VB6 project apparently ignored the one from the first table and used the second, as the program was designed to do.
I wasn't aware of this right away. Following the .Net conversion I changed this database call to use System.Data.SqlClient.SQlCommand, replacing the original recordset with a datatable fill.
Suddenly, I wasn't getting the value I expected when this field was read for each row.
After going back and seeing what the stored procedure was set up to do (SIGH!!!) I learned that the insert into my datatable appended a "1" to the name of the second of the same-named fields.
The first SAMEFIELDNAME was left as is, the second became SAMEFIELDNAME1.
Not able for various reasons to alias the column names in the proc, which needed to remain as is for other users, I just recoded the program to read field SAMEFIELDNAME1.

I also posted this on VB Forum at:
http://www.vbforums.com/showthread.php?627485-How-to-distinguish-between-2-fields-with-the-same-name-(after-a-table-join)-in-vb6

Friday, November 28, 2014

What Garmin, or Should I Call Them GRRRRRRRman Doesn't Tell You

We have a Nuvi 40LM Garmin GPS that's supposed to have lifetime map replacement. Well, that ain't worth to much if you can't get their Garmin Express, or should I call it Garmin Exasperating, device syncing software to recognize your device.
I tried plugging the GPS into 3 different PCs, with Windows 8.1, 7 and XP, using 3 different USB cables, including a brand new official Garmin cable out of desperation.
I was always able to get both the device drive and MicroSD card inside of it recognized as drives in Windows Explorer, but the Garmin Express would never open up as it was supposed to, the moment we plugged the device in, nor would it ever find the device when we opened it manually.
We tried everything, including older Garmin Map Updaters and the MyGarmin interface on their site.
It wasn't until I wrote their customer service for help (Being they don't deign to offer after hours phone support).
I was shocked to get an email back a few hours later and DAD GUM if they didn't actually give me workable advice, so since it worked for us, here it be for you.

Personally, I think you can get it to work just by removing that GarminDevice xml file in the Garmin folder. I'd try that first.
I also first backed up all the files I removed from the device folders. It was about 1.7GB.

The following is quote/unquote verbatim from their email to me:


When the device is connected to the computer what do you see on the screen of your device? If the device displays the Garmin logo with a picture of the device connected to the computer tower, you should be able to go into the nuvi drive and remove unnecessary files from the device. I have included these steps below. 
1. Connect your GPS to the computer and allow the computer time to find the device
2. Open the Garmin drive, (PC: under "Computer" or "My Computer" Mac: On the Garmin Drive mounted on the desktop)
3. Inside the Garmin drive you will find several folders, you will want to go into each and remove any files you will not be using.
          Help - Remove any language that you will not be using - You will want to keep American_English file
          Keyboards - Remove any keyboards other then keyboards you will be using as well as the EN_US file
          Text - Remove any text files that you will not be using, please keep any that begin with EN_GB
          Voice - Remove any voice files that you will not be using, please keep any American English Voices
          Garmin - inside of the Garmin Folder, delete the GarminDevice or GarminDevice.XML Folder
4. Once the files have been removed, disconnect the GPS from the computer and allow it to power on.
5. When the device is loaded to the main 'Where To/View Map" screen allow it to wait there for appx. 30 seconds 
6. After 30 seconds, reconnect the unit to the computer and Garmin Express should find the device. 

If your device is not displaying the Garmin logo with the picture of the device connected to the computer tower, please connect the device to a different USB port on the computer. If you are using a desktop, please connect the device to the USB port on the back of the computer. If the device is still not connecting to the computer please check the cable to make sure it is the Garmin branded cable. If you look at the end that connects to the computer, below the USB plug the Garmin logo should be embossed on the casing below. If you do not see the Garmin logo on this cable, please try to use a different USB cable. Although these cables look similar and fit they can often transfer data differently. 
If you are still having trouble with your device connecting to the computer, please follow the steps below to perform a soft reset. 
1. Connect the device to the computer or vehicle power cable
2. Press and hold on the power button for 10-15 seconds


3. Release the power button and the device should power back on.