Igor's Blog

Binding extra variables with PHP PDO returns no results
As I expand the data binding layer for my travelblog.ws project, I keep coming across different ways of breaking the PHP PDO statement class. The recent peculiarity that I've come across was to do with binding more variables than there were bind parameters in the SQL statement. In this situation, PDO will not return any data. In fact this is documented in the API...
PHP PDO doesn't work binding multiple IN statement values
PHP's PDO is a great way to access databases and works as expected on the most part, that is until you come to the IN() operator. Some peculiarities begin to emerge there as I've discovered recently when updating code for my travelblog.ws project.
Automatically insert a UUID value into a table with Oracle
There are different approaches to inserting a UUID into an Oracle Database table, ranging from Java to various ways of doing it in PL/SQL (as described here). Not many of these seemed appealing to me so I came up with my own approach using a trigger.
Using Maven to create a super SQL file with the merge-maven-plugin
If you've ever worked on a PL/SQL project or any project with a good amount of database related work you probably have had to deal with multiple SQL files and their deployment across systems. This is exactly what I've come across recently. Even in the early stages of my PL/SQL project I had 11 separate SQL files that would need to be deployed to system and performance test environments, then propagated to production, production support, etc. To help with managing all this I decided that building a single 'super SQL' file would work best and I ended up using Maven to do it.
Getting the total number of rows with MySQL LIMIT clause
The MySQL SELECT statement syntax includes a LIMIT clause that can be used to restrict how much data is returned from the database. This is fantastic for pagination but in most cases you also want to know how many rows there are in total, which typically requires a second query. With MySQL there is a special option that can be passed to SELECT to do this calculation for you automatically.
How to set MySQL connection timezone with PDO
For my travelblog.ws project I store all dates as UTC in columns using the DATETIME data type i.e. my code converts dates to UTC before storing them. Because DATETIME is time zone agnostic this worked great, that is until I needed to use the TIMESTAMP data type on a number of new columns that I've added.
Howto: Reset PrestaShop admin password in four easy steps
Getting locked out of your PrestaShop admin account can be a real pain, luckily there is a straight forward way to reset your password as long as you have access to the server that the shop is running on. For this you will need access to the server configuration file and the PrestaShop database via phpMyAdmin.
Selecting child nodes of a particular type from a parent node with PHPCR and JCR-SQL2
Transitioning from standard SQL to JCR-SQL2 is not straight forward and presents some new challenges. One of the problems I faced when I started to use JCR-SQL2 was how do I select children nodes that have a particular type under a parent node whose ID I know.
Getting around the 'ORA-01840: input value not long enough for date format' error
An issue has been brought to my attention recently that was to do with the to_date function returning an ORA-01840 error for certain values. This prompted an investigation which ultimately proved that it was a data issue, however I decided to put together a workaround for future reference anyway.
Oracle Database XMLTable vs ExtractValue performance
I've been doing SQL optimisation for some of the services at work recently and saw that we've been using EXTRACTVALUE in a number of places. This is not bad in itself, however the Oracle 12c documentation states that EXTRACTVALUE is deprecated and should be replaced with XMLTABLE.
Specifying a default namespace with XMLTABLE
I really like the Oracle XMLTABLE feature, which allows you to treat an XML string as if it were a real table. I've been using this feature quite a bit lately and had some difficulties initially around the default namespace so I thought I'd share my findings.
Howto: Force null values to be highlighted in SQLDeveloper query results
I've written previously about a neat highlighting feature that SQL Developer has in a post about navigational filtering. There is another feature that is just as handy that allows you to automatically highlight all (null) values in the query results. This of course makes it very easy to see nulls at a glance and definitely eases the task of examining results for specific patterns.
When null == null isn't true and how to get around it
I've been writing some tricky SQL recently and came across an odd behaviour, at least I though it was odd at the time because I didn't read the documentation. I had two columns that represented a composite key, the first column was guaranteed to have a value and the second column could be null. Now I had to join this table to itself to get the results I needed and that required comparing this composite key. When I compared just the first column it worked fine, but as soon as I added the second column, the one that could have null values, nothing matched.
The correct way to bind variables with a LIKE clause in SQL and Java
I was a bit stumped by this one not long ago so here is an example of correctly binding a value to a LIKE clause inside a SQL statement with Java.
Visualising Oracle database partition data distribution with gnuplot
We use partitioning in at least one of the databases at work, changing to partitioned tables has allowed us to keep the system running within our imposed SLAs. Recently I've started to wonder however, what happens when we start hammering individual partitions more than others. The data distribution would be skewed and the benefits of partitioning lost. I decided to put together a bit of SQL and then use gnuplot to show me how well our data is distributed.
SQLDeveloper bug not showing PL/SQL procedures that have no input parameters
We've updated some of our databases to 12c recently, at the same time I was working on an issue in some of the PL/SQL packages that I've built and running some tests on these. To my surprise when I tried to run my test PL/SQL procedure, SQL Developer was not showing in its run dialog.
Using Excel to generate SQL statements from raw data
One of my tasks recently has been to analyse erroneous duplicate data in our production system and to come up with a number of data fixes that could be executed using simple SQL update statements. The sanitised data was provided to me as an Excel spreadsheet containing just enough information to work out what should be done with the duplicate, but not enough to identify who the data belongs to. This meant that I could work on it outside of the production environment, but it also meant that I needed to provide a way for the production operations team to reproduce what I did on real data and with relative ease.
error: ORA-29494: invalid state for run task when calling dbms_parallel_execute create_chunks_by_number_col()
I've been updating some of my previous PL/SQL scripts at work only to notice during my testing that I was getting the error: ORA-29494: invalid state for run task error when I tried to run certain tests. This had me stumped for a while because none of the functionality that was to do with chunking was changed. Turns out I was just doing something stupid, here's what happened.
Use hightlights in SQLDeveloper to instantly see patterns in data
So I've been using SQLDeveloper like mad lately and have been finding some really neat features that it provides. One of the great time savers is its ability to create persistent highlights that you can use to instantly see the patterns in data. Great for debugging that ETL!
The dbms_parallel_execute.create_chunks_by_rowid not creating enough chunks, bound by extents
This post is about an Oracle 11gR2 feature. I've been using the dbms_parallel_execute PL/SQL package to parallelise processing of large amounts of data and ran into some issues with chunking of my processing table. What I've noticed is that the number of chunks created seemed to be bound by the number of extents that the table was using, this was not ideal in my case, luckily there is a different solution to create as many chunks as you need.
SQLDeveloper 4.0.2 on OS X with the Mac L&F rendering issues
I've updated my SQLDeveloper to 4.0.2 and to my surprise found a major rendering issue with this version. The whole window is using black as the background, including the toolbar. This means none of the text can be read and the whole application is essentially useless then.
Tuning the SQL*Plus fetch size for better performance
I've been doing a fair amount of work with data extraction from a database to a flat file recently using SQL*Plus and have been looking at ways to speed up the process. This is where the arraysize setting comes in handy. This setting controls how many rows are fetched from the database in a single round trip. I did some testing with various sizes and found that tweaking this value can have huge performance improvements. This is what I found with the data I was extracting.
Killing runaway SQL sessions in Oracle
I've been running a lot of SQL recently that would sometimes get 'stuck' due to the optimiser picking a plan with a full table scan. On a small database this may not be a problem, but when the database has billions of rows and a full table scan that pulls in 100Gb+ of data, the wait is long.

Previous Post

Next Post

Programming, DIY, Games, Hacks, Tech and more.
Follow me on...
Current and Past Projects
See my Resume


RSS Feed

My Other Web Sites

Igor and Elise's Travels
Riverside Expressway Cam
300 George St Blogumentary

My Online Tools

UUID to OID Converter
Guru JSON-RPC Tester
Extrudifier Object Designer

Recent Blog Posts

Using Jersey 2.x as a shared library on WebLogic 12.1.2

Google DFP with AdSense fallback is causing infinite scroll pages to go haywire

Workaround for Mobile Safari scrollTop() not updating during scroll

Skipping execution of Maven plugins that do not have a native skip option

Unmarshalling an XML fragment representing a JAXB object without XmlRootElement

Injecting a ContainerRequestContext into a Jersey entity provider class

Obfuscating PHP source code with Maven and YAK Pro PO

Making use of corner shelves with easy to make slide out draws

Octotree breaks GitLab Issue Boards

Compiling the FHIR schema with XJC

Recent Galleries

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

DIY spare parts computer build with a RAIDMAX Anura case

Fake 'Lepin' brand Lego packaging

Hardwood garden bench with clear resin void filler

Fixing a 3D printer extruder that stopped heating up

Easily increase disk space in a Lenovo Ideapad 100S 14" laptop with an M.2 SSD

Making a multi-piece 3D printed solder spool holder stand

DIY indoor apartment grow light wiring

Good Friday Electronics fun Easter Bunny LED PCB Kit IBEABU-01.0

Top Categories

Blogs I follow

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth


RAWS Parts Online
Alpha Dimensions Hosting
Kristensen Photography
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity
Follow me on... 
...or subscribe for updates!

Don't show this again