January 26, 2010

Get Auto-Generated ID Created By INSERT SQL

Filed under: Scripting — Marcus Tettmar @ 2:32 pm

If you have a database table with an auto-generated numerical index field, you may need to retrieve the value of this field after an INSERT statement.

In Microsoft Access such a field uses an “AutoNumber” data type. In MySQL it’s known as an “auto_increment” field.

We often see these types of fields where a relationship exists between two tables, for example between a “customers” table and “customer_orders” table. The “customers” table may have an AutoNumber field which the system automatically increments when a record is inserted, and in the “customer_orders” table you’ll see a “CustomerID” field which uses this same value to map one or more orders to one customer.

It’s a convenient way to have the database itself automatically create a unique identifier.

If you ever need to use Macro Scheduler to transfer data into such a database you can use the built-in database functions to handle the INSERTS. But when it comes to inserting the data into the second table you’re going to need a way to get the ID that was automatically generated for the first.

Some database technologies give you a function to do just that. MySQL has a function called LAST_INSERT_ID() which you can call with a SELECT statement:

DBExec>dbID,INSERT into customers (name,address1,address2) VALUES ("Mr Customer","..",".."),res 
DBQuery>dbID,SELECT LAST_INSERT_ID(),last_id,nr,nf,0

This will return the ID in LAST_ID_1_1 and you can then use it in your next INSERT for the detail records.

See: MySQL: last_insert_id

Microsoft Access does the same with a function called @@identity:

DBExec>dbID,INSERT into customers (name,address1,address2) VALUES ("Mr Customer","..",".."),res 
DBQuery>dbID,SELECT @@identity,last_id,nr,nf,0

See: Retrieving Identity or Autonumber Values

Your particular flavour of database might have something similar. If not you could take a risk and do this:

DBQuery>mdbH,SELECT TOP 1 ID FROM customers ORDER BY ID DESC,last_id,nr,nf,0

This will return the largest ID – the latest one. But if you’re working on a database that has many users updating it simultaneously there’s a danger you could retrieve the ID generated by someone else’s INSERT. If it’s just you, then it’s probably OK, but you’ve been warned.

For more information on Macro Scheduler’s database functions see:
Using Macro Scheduler’s Database Functions

January 16, 2010

Haiti Disaster Relief – Donate and Get Macro Scheduler Lite Free

Filed under: Announcements — Marcus Tettmar @ 10:27 pm

Over the last few days, like many I’m sure, I’ve been wondering what else I can do to help the people of Haiti beyond sending a donation to one of the many charities.

Then today, I saw this from my friend Andy Brice of PerfectTablePlan. He’s giving licenses away to people who donate money to Haiti.

I think this is a great idea and I’m going to try the same thing.

If you donate at least $30 to Haiti disaster relief via the American Red Cross, the UK Disasters Emergency Committee or similar, send us an email and we’ll send you a free license for Macro Scheduler Lite.

Just send us an email and tell us what you donated.

For now the offer is available until end of Tuesday 19th Jan 2010 Sunday 31st January 2010. I may extend it. Please tell your friends, post on facebook, tweet it, email a link to this blog … whatever. Just make sure you donate.

January 15, 2010

January 14, 2010

Amazon Kindle T-Shirt Competition – Zazzle Problems and Instructions

Filed under: Announcements, General — Marcus Tettmar @ 2:19 pm

As you probably know, we’re running a T-Shirt design contest where you could win an Amazon Kindle worth $259.

Some people have had problems sending us a link to their T-Shirt design so I checked it out and found that the process for getting a public link to your shirt is not particularly obvious. So I thought I’d post instructions for the easiest way to do it:

  • Customize the design. Click here to view the T-shirt and then click the Customize button.
  • When you’re done click on “Email” which is beneath the right-hand box, underneath the Add to Cart section.
  • You’ll be prompted to log in or create an account (just a username and email address is required).
  • You’ll then be presented with an email form. Enter your email address as both the sender and recipient so that you can send your design to yourself. If you like add [email protected] to the To list, so that we also get a copy.
  • Make a cup of coffee.
  • Check your email, grab the link and go paste it in a comment here.

If you’ve already created a design, do not fear, it is not lost. Log into Zazzle (click on My Account at top right). Click on the “Products” tab. On the left under “My Products” you will see “In Progress”, “Private” and “Public”. Your T-Shirt will be under one of these – probably the “In Progress” one. Click on it and then do the Email thing as in the above instructions.

I hope that clears up any confusion. It’s worth the effort – you could win a $259 Kindle!

January 11, 2010

Remove Tags From HTML with RegEx

Filed under: Scripting — Marcus Tettmar @ 10:09 am

A quick and easy way to remove HTML tags from a string:

Let>Html=
Hello World
Test link

Let>pattern=<(.|\n)*?>
RegEx>pattern,Html,0,matches,numMatches,1,,plainText

MessageModal>plainText

January 6, 2010

Amazon Kindle Competion Update

Filed under: Announcements, General — Marcus Tettmar @ 11:22 am

Happy New Year everyone!

What do you do when you’re snowed in and can’t get to work? Get creative that’s what! Enter our T-Shirt design contest and win an Amazon Kindle!

Yes, just in case you missed it, we’re running a T-Shirt design contest. Design a new MJT Net T-Shirt and you could win an Amazon Kindle. The competition closes 31st January. Take a look at the comments to see some of the entries so far, and then post your own.

December 29, 2009

Macro Scheduler 11.1.19 Update

Filed under: Announcements, Macro Recorder — Marcus Tettmar @ 11:17 am

Macro Scheduler 11.1.19 is now available with the following changes:

  • Fixed: “Not a valid date/time” error if regional short date format has a month name in it
  • Fixed: Variable Explorer reporting incorrect variable if parameters have spaces
  • Fixed: Macro Recorder causing hang or doing nothing or failing to stop on a minority of systems

Registered Downloads/Upgrades | Evaluation Downloads | New License Sales

December 18, 2009

The Gift of Time; 20% Discount

Filed under: Announcements, General — Marcus Tettmar @ 7:47 pm

What would be the perfect gift?  What is the one thing in such short supply?  You might say it’s money.  You could be right, but more precious than that in my opinion is time.  And of course many will say that in business time is money anyway.  Wouldn’t it be great if someone could give you more time?  Another hour a day, an extra day a week.

I believe that what we do here at MJT Net is exactly that.  We make software that gives you time.  Time to do what you really want, time to invest in the things that matter, to concentrate on the important things in your business or home life.

That’s what we love about our products. Our customers are able to cut down the amount of time they spend on repetitive, cumbersome jobs. With our software they can make themselves and/or their employees more productive.  Rid themselves of wasteful, mind numbing routines, boost productivity, and spend their new found time on what really matters to them.

With 2009 almost at an end, it’s a time to reflect and plan ahead for the New Year.  I know we’ll be spending the quiet period between Christmas and New Year looking at improving our systems and automating some more of our processes.

If you have Macro Scheduler Pro you could give the gift of time to someone this Christmas by creating a macro for them.  Take a look at what a friend or colleague does on their PC and see if you could improve it for them by writing a macro.

If you don’t yet have Macro Scheduler or would like to buy a copy for someone else, here’s a 20% discount coupon to give you even more time for your money:

Coupon Code: TIME2009

The coupon is valid until the end of 2009.

Give yourself, or someone else, the gift of time this Christmas and put it to good effect in 2010.

Happy Christmas!

December 15, 2009

MacroScript SDK Trial Download/Pricing

Filed under: Announcements — Marcus Tettmar @ 4:12 pm

Just a quick note to say we’ve now made a trial version of the MacroScript SDK available for download. We’ve also now published pricing and licensing details. For details please see:

http://www.mjtnet.com/macroscript_sdk.htm

You’ll notice there are two versions of the SDK. “Tethered” and “Untethered”. Tethered is linked to Macro Scheduler and requires Macro Scheduler to run. Tethered is available for a one-off low fee and allows you to use the SDK on any PC which has Macro Scheduler 11 installed. The Untethered version is unrestricted, for internal use, and does not require Macro Scheduler to be installed. Tethered would suit existing customers who already have Macro Scheduler or only need to use the SDK on one or two PCs. Untethered would be more suitable for larger roll-outs where having Macro Scheduler on every PC is overkill.

The SDK evaluation now includes Delphi, VB6, C#, C++ and VBScript examples.

« Newer Posts — Older Posts »