Technology Tales

Adventures & experiences in contemporary technology

Login Logger plugin

20th July 2007

The Login Logger WordPress plugin sounds a great idea and it works fine with standard situations. However, go beyond these and things start to go awry. An example is where you have to use unique database table prefixes because you use shared hosting. This is certainly something that I do and it breaks Login Logger. Thankfully, the fix for this is easy enough: just amend the database query on line 22 in the manage.php file as follows:

Before:

$query = “SELECT distinct wp_users.user_login,”.$table_name.”.username FROM wp_users LEFT OUTER JOIN “.$table_name.” ON wp_users.user_login = “.$table_name.”.username WHERE “.$table_name.”.username IS NULL”;

After:

$query = “SELECT distinct ” . $table_prefix . “users.user_login,”.$table_name.”.username FROM ” . $table_prefix . “users LEFT OUTER JOIN “.$table_name.” ON ” . $table_prefix . “users.user_login = “.$table_name.”.username WHERE “.$table_name.”.username IS NULL”;

The issue was caused by hard-coding of the table prefix for the user table and using the prefix that you yourself have set is the way out of this. What is less easy to resolve is a conflict between Login Logger and the Themed Login plugin. That will take further investigation before I come up with a fix.

Updating Oracle data tables that have associated sequence objects

3rd May 2007

Here’s something that I want to put somewhere for future reference before I forget it: keep sequences associated with Oracle data tables up to date while adding records. Given that it took me a while to find it, it might come in useful for someone else too.

The first thing is to update the sequence itself:

SELECT TABLE_SEQ.NEXTVAL FROM DUAL;

Dual is a handy one record table that you can use to update sequences. Use the actual associated table itself if you want to see that sequence number rocket…

The next thing is to use the new value to assign a table ID as part of an INSERT statement:

INSERT INTO “TABLE” VALUES (TABLE_SEQ.CURRVAL, 1, ‘Test value’);

Quoted strings in Oracle SQL

2nd May 2007

Here’s a gotcha that caught up with me on my journey into the world of Oracle SQL: string quoting. Anything enclosed in double-quotes (") is the name of an Oracle object (variable, table and so on) while values are enclosed in single quotes (‘). The reason that this one caught me out is that I have a preference for double quotes because of my SAS programming background; SAS macro variables resolve only when enclosed in double-quotes, hence the convention.

SAS/Access and Oracle Timestamp format

25th April 2007

Until SAS 9.2, SAS/Access will not support the Oracle timestamp format. There still is no word on when 9.2 might appear so it’s over to the SQL pass-through facility…

Learning about Oracle

20th April 2007

My work in the last week has put me on something of a learning about Oracle. This is down my needing to add file metadata to database as part of an application that I am developing. The application is written in SAS but I am using SAS/Access for Oracle to update the database using SQL pass-through statements written in Oracle SQL. I am used to SAS SQL and there is commonality between it and Oracle’s implementation, which is a big help. Nevertheless, there of course are things specific to the Oracle world about which I have needed to learn. My experiences have introduced me to concepts like triggers, sequences, constraints, primary keys, foreign keys and the like. In addition, I have also seen the results of database normalisation at first hand.

Using Oracle’s SQL Developer has been a great help in my endeavours thanks to its online help and the way that you can view database objects in an easy to use manner. It also runs SQL scripts, giving you a feel for how Oracle works, and anyone can download it for free upon registration on the Oracle website. Also useful is the Express edition of the Oracle 10g database that I now have at home for personal learning purposes. That is another free download from Oracle’s website.

My Safari bookshelf has been another invaluable resource, providing access to O’ Reilly’s Oracle books. Of these, Mastering Oracle SQL has proved particularly useful and I made a journey to Manchester after work this evening (Waterstone’s on Deansgate is open until 21:00 on weekdays) to see if I could acquire a copy. That quest was to prove fruitless but I now have got the doorstop that is Oracle Database 10g: The Complete Reference from The Oracle Press, an imprint of Osborne and McGraw Hill. I needed a broader grounding in all things Oracle so this should help and it also covers SQL but the aforementioned O’ Reilly volume could return to the wish list if that provision is insufficient.

SAS and Oracle

19th April 2007

It seems that SAS have put a good deal of effort into making their software work with Oracle. Admittedly, you have got to buy SAS/Access for Oracle in addition to the other components that you already have but it is worth it. The Oracle library engine makes things easy so long as there are no incompatibilities on the database side. For instance, SAS has no plans to support the Oracle timestamp format until the forthcoming SAS 9.2 and this does make things a little interesting. However, this can be resolved with the Oracle SQL pass-through facility where you pass Oracle SQL through to the database itself for processing, avoiding incompatibilities. A more pressing issue is using PROC APPEND to add records to the data tables without updating any sequences that are associated with table ID’s. The SQL pass-through facility is the best way around this so that you can update the sequence with a SELECT statement and use the current value for the ID in the following EXECUTE statement. It may sound far from ideal but you need to process your data row by row; once set up though, everything works well.

Oracle SQL Developer and MySQL

17th April 2007

Because of my work, I recently have had a bit of exposure to Oracle SQL Developer, which I have been using as part of application development and testing activities. For further investigation, I decided to have a copy at home for further perusal (it’s a free download) and it was with some interest that I found out that it could access MySQL databases. To do this, you need Connector/J for MySQL so that communication can occur between the two. Though you quickly notice the differences in feature sets between Oracle and MySQL, it seems a good tool for exploring MySQL data tables and issuing queries.

Oracle SQL Developer

» Newer Entries »

  • All the views that you find expressed on here in postings and articles are mine alone and not those of any organisation with which I have any association, through work or otherwise. As regards editorial policy, whatever appears here is entirely of my own choice and not that of any other person or organisation.

  • Please note that everything you find here is copyrighted material. The content may be available to read without charge and without advertising but it is not to be reproduced without attribution. As it happens, a number of the images are sourced from stock libraries like iStockPhoto so they certainly are not for abstraction.

  • With regards to any comments left on the site, I expect them to be civil in tone of voice and reserve the right to reject any that are either inappropriate or irrelevant. Comment review is subject to automated processing as well as manual inspection but whatever is said is the sole responsibility of the individual contributor.

Copyright © 2007-2024, TechnologyTales.com