Articles - Joel Lipman .Com

Fun with DllCall in AutoHotkey

: Joel Lipman; AutoHotkey; 27 September 2011; Hits: 13899

Ok so there are more fun things to do out there. If you're the type of AutoHotkey programmer that doesn't like having to use the command prompt to write to an extra text file which your program has to read, then this is for you. DllCall will usually run contained in the program and can be hidden from the end-user.

The one to rule them all:
This is a function I pulled from the Autohotkey forums submitted by SKAN which lists all the functions for a specified Dynamic Link Library (DLL) along with an inputbox for convenience:

: Joel Lipman; AutoHotkey; 27 September 2011; Hits: 10426

These are for autohotkey and in AHK code. Taken from Autohotkey forums topic 1878:

From Hexadecimal to RGB
From RGB to Hexadecimal
Check for a valid hexadecimal value

: Joel Lipman; Personal Home Page; 26 September 2011; Hits: 6920

So I know it's quite a long title but I'm not sure what I'll be searching next time. It has taken me a lot longer than I thought it would mostly out of frustration and the inability to know exactly what I'm looking for... always difficult. After a cup of tea the solution was glaringly obvious, just do a primary school mathematics table and it all makes sense (see my 10 mod table below).

This article is a quick note (so I never spend as long again) in PHP on how to determine when looping through a loop, which entry was first and which was last. This is incredibly useful for pagination.

: Joel Lipman; MediaWiki; 22 September 2011; Hits: 5008

What do I want?
Page ID, Title, Content, Category, AccessLevel (note that AccessLevel is extra to your standard MediaWiki setup and one that was customized for my day job).

Thought I already had this somewhere on my site, so it took a while again but I've posted my finished query here:

The base query to list mediawiki articles
Page ID, Title, Content, Category

copy raw

SELECT
		p.page_id AS PageID,
		CONVERT(p.page_title USING latin1) AS PageTitle,
		CONVERT(t.old_text USING latin1) AS PageContent,
		(SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory
	FROM
		wikimedia_page p
	LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id
	LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id
	WHERE
		p.page_namespace=0
		AND t.old_text=''
		AND p.page_is_redirect=0

SELECT
p.page_id AS PageID,
CONVERT(p.page_title USING latin1) AS PageTitle,
CONVERT(t.old_text USING latin1) AS PageContent,
(SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory
FROM
wikimedia_page p
LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id
LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id
WHERE
p.page_namespace=0
AND t.old_text=''
AND p.page_is_redirect=0

Category: MediaWiki :: Article: 385

: Joel Lipman; AutoHotkey; 20 September 2011; Hits: 13359

As you can tell these are my messed up functions that convert dates into seconds and vice-versa. They're a little disorganised but they're the ones I copy and paste to my scripts then modify.

In it's straightforward form

copy raw

FormatTime( TimeString, Format )
{
     FormatTime, FormattedTime , TimeString, %Format% 
     return Formattedtime
}

FormatTime( TimeString, Format )
{
FormatTime, FormattedTime , TimeString, %Format%
return Formattedtime
}

Category: AutoHotkey :: Article: 384

: Joel Lipman; AutoHotkey; 20 September 2011; Hits: 14050

Just a quick note as I use this function in various scripts. This adds the 1000th separator comma:

    	FormatAddCommas(val) {
    		Result:=val
    		StringLen, OutputVar, Result
    		NumLoop := (OutputVar // 3)
    		DNum = 3
    		Loop, % (NumLoop+1)
    		{
    		   StringRight,Digit,Result,%DNum%
    		   StringReplace, Result, Result, %Digit%,`,%Digit%
    		   DNum += 4
    		}
    		StringLen, OutputVar, Result
    		Loop, %OutputVar%
    		{
    			FirstChar:=Substr(Result, 1, 1)
    			IfEqual, FirstChar, `,
    			{
    				Result:=Substr(Result, 2)
    			} else {
    				break
    			}
    		}
    		Return Result
    	}

SSRS External Images don't display

: Joel Lipman; SQL Server Reporting Services; 19 September 2011; Hits: 53201

The Situation
We want some photos taken from another website of ours to display in our report. These are not stored locally on the Reporting Server because other services use these photos on our Intranet and we only want one location to upload the photos. These images are provided via a website address (http) or network share (file).

Before you ask, we have several MS Sharepoint sites but we didn't go down the route of uploading 10000+ images into a Sharepoint database though we are still considering it; so this article is more about just getting external images to display when they are provided over an authenticated URL (You shouldn't be having any issues with this if the image is available to anonymous users - eg. Google Logo).

Our Setup

Windows XP Workstation
Business Intelligence Development Studio 2008 (BIDS)
SQL Server 2008 R2 Reporting Server (SSRS) running on Windows Server 2003
Team Foundation Server 2010 (TFS)

Problem #1: Image does not appear within SSRS
When previewing the report in BIDS, the photo appears fine. If we deploy the report to our Reporting Server and attempt to view it using a Web-Browser, the image does not display:

Get report to see images hosted on an external URL
Check if image exists to display alternative placeholding image.

: Joel Lipman; Excel; 05 September 2011; Hits: 17632

So I googled this for a while and there are a lot of solutions out there, none of which applied to what we meant and lots of people in the same boat.

The Situation
We have an Excel report which summarizes for our guys at the top, all the activities and time spent by staff. There are several filters available on the report (only a few to keep it simple silly). When you click on the filter, a dropdown appears with all available values listed.

The Problem
The values are listed in alphabetical order at first. If any new values come along then they get added to the bottom of the list... This is the problem. For example, if the year dropdown has a list of 2010, 2011, 2013; then if you add an entry which has year 2012, then the dropdown list will be in the following order: 2010, 2011, 2013, 2012.

The Solution

: Joel Lipman; SQL Server Reporting Services; 02 September 2011; Hits: 16711

I'm storing a note here because it took a while to figure out and googling other solutions did not answer our questions but did lead us to a workaround.

So this is regarding an error when trying to add parameters to a Stored Procedure of an Oracle database from within Microsoft SQL Server Reporting Services.

The Error

copy raw

ORA-00911: invalid character 
ORA-06512: at "SYS.DBMS_UTILITY", line 114 
ORA-06512: at line 1 (System.Data.OracleClient)

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1 (System.Data.OracleClient)

The Situation
I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to a SQL Server 2008 R2 Reporting Service (SSRS) with Team Foundation Server 2010. The database with our stored procedure is Oracle 10g.

The Stored Procedure
Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a JobID (request reference) number.

Category: SQL Server Reporting Services :: Article: 380

: Joel Lipman; Oracle PL/SQL; 30 August 2011; Hits: 7952

Just a quick note as to something that worked for me. Taken from the Oracle forums @ https://forums.oracle.com/forums/thread.jspa?messageID=6861756

Situation
I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.

: Joel Lipman; MySQL; 24 August 2011; Hits: 7969

Hopefully the title doesn't put you off but after much Googling and Bing-ing, I still couldn't figure out how to do this. Hopefully this article will help you more than my search engine skills do.

My Setup
Windows XP Workstation
...needs to open...
Excel 2007 SP2
...with ODBC to...
MySQL v5+
...hosting database...
ActivityLog
...contains activity, staffID, resourceID, start time, end time...

Report Specification
PivotTable Report
...resources in row (along the side)...
...staff in columns (along the top)...
...persondays in values (the number my bosses want - 7h 24m or 26640s is 1 person day)...
With date range as parameters
...ouch...
...and it was so easy up to here...

: Joel Lipman; Wordpress; 16 August 2011; Hits: 14922

Migrating the articles:

Change database name my_joomla_db to your joomla database and my_wordpress_db to your Wordpress database
Change http://demo.joellipman.com/wordpress/ to the full URL of your WordPress site.
If post_type is to be post then append with ?p= otherwise use ?page_id=.

Articles:

------ MIGRATING JOOMLA v1.5.# CONTENT TO WORDPRESS v3.2.# ------
-----------------------------------------------------------------

INSERT INTO my_wordpress_db.wp_posts
SELECT
	id 'ID',
	1 'post_author',
	created 'post_date',
	created 'post_date_gmt',
	CONCAT(introtext, ' ', `fulltext`) 'post_content',
	title 'post_title',
	'' post_excerpt,
	CASE state WHEN '1' THEN 'publish' ELSE 'draft' END 'post_status',
	'open' comment_status,
	'open' ping_status,
	'' post_password,
	alias 'post_name',
	'' to_ping,
	'' pinged,
	modified 'post_modified',
	modified 'post_modified_gmt',
	'' post_content_filtered,
	'0' post_parent,
	CONCAT('http://demo.joellipman.com/wordpress/', '?p=', id) AS guid,
	'0' menu_order,
	'post' AS 'post_type',
	'' post_mime_type,
	0 comment_count
FROM
	my_joomla_db.jos_content
ORDER BY
	id

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support