Tuesday, October 28, 2008

Updating Multiple Social Networking Sites

Social Networking? Can be a life saver or a pain in the never region. Can take up a large portion of your day, and life. Can be a great business tool or a production black hole. Like most things, SN (Social Networks) can be good or bad. Depending how you use them.

I know of some people who spend up to three hours a day, updating their SN sites. Things like blogging, Facebook, twitter, plurk, flickr. This seems to escalate depending on how many SN sites you keep active.

When I first started out with my new Internet Business, website design and development, I added SEO to my stable. One of the things I learned quickly was that SN could drive traffic to your website. Help with building back links and help promote your website.

So what did I do? I started to join multiple Social Networks, including Facebook, twitter, plurk, linkedin etc. I also started blogging setting up multiple blogs. Some have similar content some are vastly different.

I soon found out that this took up a large amount of time. Having a new business I could not justify all the time spent on SN. I needed to bring in paying clients, I found it more profitable to spend time coding than Social Networking. But still realised the importance of SN.

I was in a bit of a dilemma. I knew that Social Networking could help me, but had no time to dedicate to it. As a result a lot of the SN sites were neglected. I only focused on the ones I thought might benefit me.

Enter in ping.fm. Ping.fm is a service that helps with updating multiple Social Network sites. By writing, or blogging in one place you can update everything. Their motto - "Ping.fm is a simple service that makes updating your social networks a snap".

Once you sign up, you add various services to your account that you want to update. They have many services in their stable and are adding more. Things like Blogger, LiveJournal, Wordpress, Facebook, Twitter, Plurk, LinkedIn. etc.

By writing in one place, ping.fm, you can post (ping) to all your SN sites. Updating your status, ,micro-blogging, full blogging. I am doing this right now. This blog was sent to multiple blog sites via ping.fm.

Whether it is advisable to send the same thing to multiple blogs is for another discussion.

With ping.fm you can ping (post) to all your Social Networks at once, or to a selected group, or to individual ones. This is done by triggers. These triggers help ping.fm to know what you are trying to do. For instance, you could just make status updates, or post to your micro-blogging sites like twitter and plurk, or even post to you full blogging sites like Blogger and Wordpress. You can ping them individually or as a group.

You can even create your own triggers, so that you can post to your own combination of SN sites. For example, you might want to only post an update to Twitter and plurk. Create a trigger, write your update, and ping using that trigger.

Ping.fm makes it even easier to update your sites by providing other services that you can use to update them. Services like Instant Messaging. Do you have AOL IM, or Live Messenger, or Yahoo Messenger. Set up a ping.fm contact using their application key, and then you can update your Social Networks through your IM client.

If that does not work for you, you can set up an e-mail to update you Social Networks. This I find will work great for those who are behind a company firewall and are prohibited to go to certain sites. With an e-mail you could possible circumvent this and update your Facebook or twitter via e-mail. Although I haven't tested this out, I see no reason why it should not work.

If you have tried this out. Leave a comment and let me know how it went.

What if you have been banned from the internet and e-mail. Well ping.fm have provided another way for you to update your SN sites. Through the use of your Mobile phone or iPhone, you can update all your SN sites. You can even SMS (text) your updates in, or send e-mails from your Mobile and iPhone.

All in all I am impressed with this service. It certainly has taken out the drag of updating SN sites. I recommend you take a look and let me know.

Leave me a comment, tell me what you think.

Sunday, October 26, 2008

Liverpool on top of the log

I know this has nothing to do with dbase. But it is worth the shout. Liverpool won today 1-0. Beating Chelsea. Moving to the top of the log.

I think they have a good chance to take the title this year. Best chance they have had since the last time they won, 19 years ago.

Liverpool, you will never walk alone.

Using an older version of dBASE

It surprises me still when I hear about some folk who are still using older versions of dBASE. I mean DOS versions, like dBASEIV or dBASEIII or even dBASE 5.0 for dos. There seem to be a few that are still using or supporting dbase 5.7.

I know for myself I am supporting a 5.7 installation in Cape Town. Been meaning to write an upgrade and get the client out of 5.7. But time and cost have prohibited me.

I wonder how many are still using or supporting older versions of dBASE or any software. Remember a few years ago when the Y2K scare was what every one was talking about. Well that has come and gone, yet many of those older programs who were said not to run, are still working.

Seems to me that there are many clients out there who are not too worried about getting the latest and greatest. All they want is something that works and fits their needs. Even if it does mean that the software they have is a few years old.

What versions of dBASE or any other software are you supporting? Particularly older versions. Leave a comment and let us know.

Sunday, October 5, 2008

Accessing your dbf tables via a linked server in MSSQL

In my previous post I wrote about accessing dBASE tables through MSSQL. Primarily I discussed accessing dBASE data using the OpenRowset function.

If you tried this, you would have seen that it works pretty well. But as I mentioned, this is used as an adhoc procedure. Only when you need to access dBASE data a few times, would OpenRowset be a good choice. You must admit that the syntax is long. I mean its not just a select * from mytable, but you have to include the driver, and the data path etc etc. with the select statement.

This can get very frustrating if you have to do lot of dbf access. Enter in the linked server.

“A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.”

So in a nutshell, when you create a linked server to your dbf files, it will act as a normal server object in your applications and Enterprise Manager. You can easily use the select statement with a fully qualified table address, this will still be much shorter than using OpenRowset.

Setting up a Linked Server

First things first. Set up the linked server. I am going to be discussing how to do this through enterprise manager. As it is easier to understand.

Note: You need to have the correct version of MDAC installed. At least MDAC 2.6 I believe.

Open EM, and navigate to Server Objects in the Object Browser. If your Object Browser is not active you can activate it via the view menu or just press F8. Expand the Server Objects node, then navigate to the Linked Servers node. Right click on the Linked Servers node and select “Create New Linked Server” form the pop-up menu. A New Linked server form will appear.

Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognise to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. The rest of the form below will then become active to fill in.

Expand the drop down box under “Provider” and select “Microsoft Jet 4.0 OLE DB Provider”. You might be tempted to select the driver for OLE DB ODBC Drivers, but there are some restrictions. See "Books Online”.

For “Product Name” enter “Microsoft Jet

In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\dBASE Work\R3\Santam\Data”.

For the the “Provider String” Enter the dbf table level. Because MDAC cannot understand level 7 dbf tables, entering level 7 for table version will not help. But if you enter “dBASE 5.0” this works. Don’t ask me why but it does. So for the “Provider string” enter “dBASE 5.0”.

Then go to the security page. Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.

In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. I have tried to search the Microsoft site and the rest of cyberspace, using Google why this is needed like this, but as of yet have not found an answer. Anyway, it works as is.

You can cow click the “OK” button. Your linked server is now created as an object under linked servers node. Expand your linked server node. Then expand the “Default” Node. You will see two nodes, a Table and a View node. Expand the Tables node to see a list of table objects for the folder you stipulated.

Selecting Data

So now that you have created a linked server to your dbf files, and have seen that you can actually view the table objects. You have to conclude that it is working, right. Well in order for this to actually work you have to retrieve data. So lets go ahead and try and retrieve data.

In EM, open up a query window from within a valid MSSQL database, and type in a select statement. Now there is a slight difference to the select statement. In fact, it would be to the actual table name. You have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName]. Notice the three (3) periods between the linked server and the table name.

So type in a select statement like this, obviously substituting your Linked Server name and Table name and Column names.

select * from Santam…gl.

Now you can use almost any select statement. Things like joins, sub-queries, order by, where, cast, case etc. Lets try a order by and a where clause. Type in your query, I tried:

SELECT * FROM Santam...gl where Glcode > 30000 order by Date desc

I retrieved over 10000 rows in 2 seconds.

Joins are just as easy. You can join your dbf tables as well as other SQL tables as well. Lets try a simple dbf Join. To join two dbf tables I typed in:

SELECT a.account,a.date, a.amount, b.glm_stdesc FROM Santam...gl a
inner join Santam...glacc b on a.glcode = b.glm_accountglacc

Do sub-queries work? Well lets try it out. I typed in:

SELECT a.account,a.date, a.amount, b.glm_stdesc FROM Santam...gl a
inner join (select glm_accountglacc,glm_stdesc from Santam...glacc where glm_type = 'Capital') b on a.glcode = b.glm_accountglacc

And retrieved 5 rows.

So the next question that one asks, is can we join current SQL tables with linked dBASE tables. After all, you wouldn’t want to go through a whole import procedure every time you want relevant data from your dbf tables in your MSQL app. Especially if your dbf table and data is still live.

Sure you can. Test it out. I typed in:

SELECT a.account,a.date, a.amount, b.glm_stdesc, b.glm_type, c.description FROM Santam...gl a
inner join Santam...glacc b on a.glcode = b.glm_accountglacc
inner join Insyear c on a.period = c.period

So as you can see, we can use almost any select statement to retrieve our dbf data. I have not tested every SQL statement, but the most common ones work. Also note, that you can view dBASE memo fields, but you cannot see or view binary, blob data. This is because this type of data is generally stored in a separate file (DBT) and I don’t think MDAC understands it or the link to the external DBT file.

Updating Data.

What about updating data. Well this can also be achieved. I would be cautious though, as I haven't tested every possibility, I therefore do not know if it is totally compatible. But I have been able to update data.

I tried this statement:

Update Santam...claims set notes = 'This is a note update from MSSQL' where policyno >0

A note: “notes” is a memo field in my dbf table and it updated fine, with no problems. I viewed the table in dBASE, and it was perfect. Next I tried to update an indexed field, but not a keyfield though.

Update Santam...claims set policyno = 770 where claimkey = 63

Another note: I had the dbf table open in the IDE, and my row pointer was sitting on the intended row. It update fine with no problems.

Importing Data

If the above worked flawlessly, then importing data is a breeze. To import data you can then make use of one of two SQL statements. Namely the select into and the insert into. Both will work perfectly.

Conclusion.

If you want to include you dBASE data in your new MSSQL app, but still want to keep the dBASE data alive. If want the ability to update you dbf data through a MSSQL query, the the good news is, you can, with a linked server to you dbf data folder.

This means that you can create views, write SP’s and use you dbf tables in any MS product, like C#, that can access MSSQL.

Your dBASE dbf data is not dead yet.

Note:Make sure that the 'Distributed Transaction Coordinator' service is running
on your server.

Saturday, October 4, 2008

Accessing dBASE tables with MS SQL

For years the dBASE (dbf) table format had been very popular to store data in. Many third party applications included ways to import and export to the dBASE (dbf) file type.

This stopped at dBASE 5.0. Since then the dBASE table format has evolved and is now at level 7. The sad news is that third party application developers have not included the ability to import and export to this format.

This might be as a result of third party vendors not considering dBASE as a viable alternative or that the authors of dBASE have just not seen a need to write a driver to access their data.

Either way the need to access dBASE data has dwindled over the years. Never the less, there are still large amounts of data still stored in this format. So, if you have a place where data is stored, then you should be able to retrieve that data.

With many moving over to the more robust, faster, secure database servers like MSSQL, accessing this dBASE data becomes increasingly difficult.

How then do you access your data in dbf format from MSSQL, having the ability to join that table to your current data? How can one import dbf data?

There might be many ways to achieve this, one of which you can export your data to a format that can easily be imported into MSSQL. This can be a labour intensive operation, especially if your dbf data is still live and changing continuously.

Fortunately, MSSQL has a few methods of accessing other data. Two of which are:

1. Using OpenRowset.
2. Creating a linked server.


Both methods can be used to access live data, negating the need for an import facility, and also allowing you to do normal joins and other SQL stuff.

OpenRowset

The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet or dBASE data, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from your other data sources into a table in SQL Server or MSDE. You may even want to just use the data in a view or join.

Openrowset should be used in cases when access in not required on a regular basis, ie. as in adhoc queries. When data is accessed several times, a linked server should then be used.

The OpenRowset function in its simplest form carries the following syntax.

OPENROWSET (provider_name,provider_string,query_syntax)

So to access dBASE we would use the following syntax:

OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=[Path to data];
SourceType=DBF',
SQL Query)

This assuming you have at least mdac2.6 installed. So we could issue the following:

OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT top 1000 * FROM gl')


This returned over 14000 rows without a query plan (first run) in 2 seconds. Once the query plan is stored it is a second or less.

Now you can use any SQL query, (Note: I haven't tested every single query), as in:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl where GLcode >30000 order by date desc')


How about a join within the openrowset query:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
')

How about a join of two openrowset queries. Remember that the data source does not have to be dBASE. You can access Excel (tested up to 2003), text files etc. But we are using dBASE here just to show you the ease of it:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
') a
inner join
OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM glstaccount') b
on a.glcode = b.glkey

Now what about adding your own newly created data in MSSQL to the pot. So you can now join dBASE data, what about joining that to a SQL table or two.

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
') a
inner join
OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM glstaccount') b
on a.glcode = b.glkey
inner join dbo.Insyear c on a.Prop = c.Period

So as you can see, your imagination can run wild. No longer does your dBASE data have to be side-lined. No longer do you have to pull your hair out trying to figure out how to access your dBASE data.

But what if you just want to import the data and start over. Well we make use of a very nice statement, select into … Which will create a brand new table for us.

SELECT Glcode, Account, ContAccountgl, Amount, DC, Glm_stDesc into openrowsettable
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
')

So there you have it. Working with dBASE data in MS SQL.

Next we will look at how you can use a linked server to dBASE dbf tables to view and read your dBASE data.
Website design, web development, web hosting and Search Engine Optimization