Today I was trying to debug what my OrmLite queries were doing, but didn't know how to print out the equivalent SQL being generated.
I needed to find a way to see this in real time as the queries were coming through.
After some quick consultation with a colleague, I needed to go into a little more depth on the research, so I turned to Google.
The recommendation I got was to tcpdump to postgresql, but there wasn't time to elaborate further as they were busy with some other pressing issues. I decided to go out and search myself, armed with the basic knowledge of the basics of what to look for.
My first search was
This was sort of helpful, but I don't know how to read HEX/Ascii. I needed a little bit more textual output. The search turned into
which seemed so much more helpful. I had to read some of the comments, but eventually came to the conclusion to use the following:
sudo tcpdump -i any -s 0 -l -w - dst port postgresql | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
{
if (defined $q) { print "$q\n"; }
$q=$_;
} else {
$_ =~ s/^[ \t]+//; $q.=" $_";
}
}'
which produced:
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL
SELECT "bucket_id", "stream_id", "stream_revision" FROM "todo_projection" WHERE (1=0) OR ((("bucket_id" = $1) AND ("stream_id" = $2)) AND (("stream_revision" >= $3) OR ("stream_revision" < $4))) OR ((("bucket_id" = $5) AND ("stream_id" = $6)) AND (("stream_revision" >= $7) OR ("stream_revision" < $8))) OR ((("bucket_id" = $9) AND ("stream_id" = $10)) AND (("stream_revision" >= $11) OR ("stream_revision" < $12))) OR ((("bucket_id" = $13) AND ("stream_id" = $14)) AND (("stream_revision" >= $15) OR ("stream_revision" < $16))) OR ((("bucket_id" = $17) AND ("stream_id" = $18)) AND (("stream_revision" >= $19) OR ("stream_revision" < $20))) OR ((("bucket_id" = $21) AND ("stream_id" = $22)) AND (("stream_revision" >= $23) OR ("stream_revision" < $24))) OR ((("bucket_id" = $25) AND ("stream_id" = $26)) AND (("stream_revision" >= $27) OR ("stream_revision" < $28))) DISCARD ALL
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL
SELECT "bucket_id", "stream_id", "stream_revision" FROM "rdp" WHERE (1=0) OR ((("bucket_id" = $1) AND ("stream_id" = $2)) AND (("stream_revision" >= $3) OR ("stream_revision" < $4))) DISCARD ALL
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL
SELECT min(checkpointnumber) from commits
Success! I had never imagined to use tcpdump to get this data. I just assumed I would have to use a SQL profiler or some other obnoxious application.Â