SQLinForm Forum

[solved] Unable to register (2 replies)

mgold — Sat, 11 May 2013 17:49:36 +0200

Unable to register the app. Removing the trailing space in the sqlinformlicense.cfg file (which solved the problem for someone else) does not solve the problem in my case.

OS: OS X 10.8.3, JRE6

Thanks,
mg

SQLinForm Release "April 2013" available (no replies)

guidomarcel — Fri, 29 Mar 2013 11:27:53 +0100

L A T E S T C H A N G E S

Desktop Release "April 2013" (Online Release 4.9.11)
For the Desktop Application:
. Added new keywords

For the Windows Hotkey Solution:
. Complete Redesign
. Possibility to define up to 5 hotkeys

Desktop Release "March 2013" (Online Release 4.9.07 - 4.9.11)
For the Desktop Application:
. Added new Option "Only Keywords Uppercase"
. Removed bug related to SQL Server Case statement formatting
. Added MSAccess formatting rules for date literals surrounded by #-characters
. Added new "Show License Info" menue point
. Highlight Profile Buttons when pressed
. Removed bug related to lost linebreaks when tabs follow a linebreak

For the Windows Hotkey Solution:
. Added Possibility to start the hotkey solution together with Windows Start
. Added Possibility to start the hotkey solution minimised
. Added Possibility to register the hotkey automatically

Desktop Release "January 2013" (Online Release 4.9.03 - 4.9.06)
. Added new "SQL" menue "Set Default SQL Directory..."
. Prepared SQLinForm for its new Windows Hotkey Function
. Changed formatting standard for INSERT statements
. Added "with (tablock)" for SQL Server
. Improved formatting for WITH keyword
. Removed bug related to free license upgrades.

Desktop Release "September 2012" (Online Release 4.9.02)
. Added new option for JOIN "ON" Keword
. Added Teradata Keywords
. Added variable type keywords
. Tabs are kept in literals
. Improved formatting of "EXEC" keyword
. Improved formatting in ORDER BY and GROUP BY
. Improved 1-line formatting rules
. Improved 1-line formatting GUI behavior
. Removed bug related to missing spaces after linebreak removal

Desktop Release "April 2012" (Online Release 4.8.49)
. Improved Layout for MAc OS X
. Added scrollable option menus for netbooks
. Improved Formatting Rules for SQL Server DECLARE TABLE and RETURNS TABLE
. Improved Output Format for Concatenated SQL
. Changed parameter bracket indention for SQL Server
. Removed Keyword "END EXEC"

Desktop Release "March 2012" (Online Release 4.8.46)
. Improved Formatting Rules for SQL Server SET statements
. Removed Keyword "END EXEC"
. Improved Output Format for Concatenated SQL
. Changed parameter bracket indention for SQL Server

Desktop Release "January 2012" (Online Relase 4.8.43)
. Added formatting rules for SQL Server Lables
. Removed trailing blanks
. Improved formatting for SQL Server "GO"
. Improved formatting rules for SQL Server "EXEC"
. Improved formatting for SQL Server configurational SET statements

Desktop Release "October 2011" (Online Relase 4.8.37)
. Impoved formatting for Oracle Named Notations
. Improved GUI colors and fonts
. New option to adapat GUI font size

Desktop Release "June 2011"
. Added formatting for Teradata "Create Set/Multiset Table
. Added "Create Proc" formatting rules for SQL Server
. Improved Formatting for SQL Server comma alignment

Desktop Release "May 2011"
. Added new feature to customize fonts
. Improved formatting of line comments

Desktop Release "February 2011"
. Add new linebreak options
. Add new options for CASE statement
. Add possibility to set own colors
. Restructure GUI

Desktop Release "January 2011"
. Prepared GUI for new options

Desktop Release "December 2010"
. Removed Bug related to DB2 EXCEPTION JOIN
. Performance Improvements
. Increased Parsing Result Limits
. Added DB2 Keywords

Desktop Release "August 2010"
. Added Oracle's RETURNING keyword
. Improved case handling for tokens inside double quotes
. Added "->" as own separator.
. Removed case handling bug from 4.8.20
. Activated SP options for SQL Server

Desktop Release "July 2010"
. Rearranged comments options
. Added option to re-indent block comments
. Added option to keep initial spaces in block comments
. Reactivated "Indent Sub-Select" feature

Desktop Release "June 2010"
. Added User Defined Keyword List
. Added User Defined Attribute Type List
. Added Options for INSERT statement
. Respect max line length when aligning tokens
. Improved linebreak formatting for conditions

Desktop Release "May 2010"
. Improved linebreak rules for Oracle's DECODE function
. Added VB output format (5)
. Added CROSS APPLY for SQL Server
. Improve formatting for CASE in scalar functions when no linebreak chosen
. Improve formatting for "Create [or Replace] Table"
. Improve formatting for "Create [or Replace] View"
. Improve linebreak for DB2 "CREATE PROCEDURE"
. Removed bug related to brackets in comments

Using Hotkeys in SQL Server 2008 (4 replies)

Jason — Tue, 26 Mar 2013 18:24:57 +0100

This does not seem to work.

I have done everything correctly it seems. No matter what hotkey I select it does not format the SQL in the pane.

It comments out the line I am highlighting and that is all.

So I need to do anything in particular?

Thanks

Jason

[solved] spaces are being inserted inside fieldnames? (3 replies)

hsm — Sat, 23 Mar 2013 20:51:15 +0100

When I copy some sql from my database manager (which delimits fields using [ ] ) so that I can format it as a pascal string I am getting extra spaces inserted between the field name delimiters which causes the pascal sql to fail with unknown column name errors. I have to manually remove all the spaces after SQLInForm formats my sql.

Is there a better way?

I have the options set for no spaces around operators, commas or brackets.

For example, this is what I might paste into SQLInForm

SELECT [TblCustomers].[CustName],  [TblCustomers].[PostCode],  [TblCustomers].[Phone],
  [TblOrders].[OrderID],   [TblOrders].[OrderDate],   [TblOrders].[CustomerOrderNumber]
FROM [TblOrders]
  INNER JOIN [TblCustomers] ON [TblCustomers].[CustomerID] =   [TblOrders].[CustomerID]

This is the pascal string it makes

SQL := '' 
+'SELECT [TblCustomers].[CustName        ], ' 
+'       [TblCustomers].[PostCode        ], ' 
+'       [TblCustomers].[Phone           ], ' 
+'       [TblOrders].[OrderID            ], ' 
+'       [TblOrders].[OrderDate          ], ' 
+'       [TblOrders].[CustomerOrderNumber] ' 
+'FROM   [TblOrders] ' 
+'       INNER JOIN [TblCustomers] ' 
+'       ON     [TblCustomers].[CustomerID]=[TblOrders].[CustomerID]';

[solved] MySQL "IF EXISTS" construct formatted as the beginning of an IF block (2 replies)

Clay B. — Fri, 29 Mar 2013 23:20:37 +0100

Trying to format some MySQL code with IF EXISTS syntax (e.g. CREATE TABLE or DROP TABLE) causes the formatter to break the statement on the IF and indent opposed to treating the entire CREATE or DROP as a single block.

E.g. what I get:

DROP TEMPORARY TABLE
    IF EXISTS my_table;
        DROP TEMPORARY TABLE
            IF EXISTS other_table;

What I'd expect:

DROP TEMPORARY TABLE IF EXISTS my_table;
DROP TEMPORARY TABLE IF EXISTS other_table;

[solved] Replace Host Variable (4 replies)

SamBaugh — Wed, 03 Apr 2013 20:40:55 +0200

Under the Display/Edit tab, Edit SQL statements, Replace Host Variables by "?", I would like to have the option to not include the comment for the host variable that was replaced. I use SQLinForm with dynamic and static queries that are captured in a CA Detector tool, and it lists all host variables as :H, so seeing the original host variable name is of no benefit in this case. I use SQLinForm on these daily, just to get the SQL in a readable form.
.
Thanks!

[solved] is "pseudo code" still available? (5 replies)

maxad01 — Tue, 23 Apr 2013 14:00:59 +0200

Hi, There used to be an ability to "summarise" a sql query in a "pseudo code" type output (quite some time ago).

Is that capability still available? (currently using 4.8.49) [I haven't been able to find it]

If not, what is the latest version that carried that capability (and is it still available to download)?

Thanks

[solved] color for keywords (1 reply)

PANDARIKKAL@GMAIL.COM — Wed, 13 Feb 2013 23:01:24 +0100

I purchased sqlinform today. but I am not getting colors for keywords. I chose the options. but to no avail. please help

Not able to unzip at the office (4 replies)

danny presse — Sat, 09 Feb 2013 02:01:14 +0100

Hi,
SQLinForm work perfectly at home. But I am unable to unzip at the office.
At ther office, when I unzip the .zip file, I obtain a "IZARC JAR Archive" (not a .jar file) .
When I clic ont the "IZARC JAR Archive", I am asked to extract again.
At the office we have java 7
Any Idea ?
thank you
Danny

[solved] Fantastic ! I love it ! (no replies)

danny presse — Sun, 03 Feb 2013 01:12:25 +0100

HI,
Very user frendly. Do exactly what I was loking for. ;-)

[solved] Not able to register (2 replies)

danny presse — Sun, 03 Feb 2013 09:27:02 +0100

Hi,
I am not able to register.
I enter my registration key (without double quote) and clik "REGISTER"
After I execute again the .JAR file
I clic on "close windows" and I see "Unregistered version - Unregistered version - Unregistered version- Unregistered version"
Any idea ?
Thank you
Danny

don't realy need to use a USB devices ? (1 reply)

danny presse — Mon, 28 Jan 2013 08:20:22 +0100

Hi,
I just wantto be sure:
I can INSTAL SQLinForm your my personnal Desktop at home, my personnal desktop in my office or at your client's site. I don't realy need to use a USB devices ?
Thank You
danny

ERROR CODE:PE101 (1 reply)

danny presse — Sun, 27 Jan 2013 17:00:17 +0100

HI
during my purchase I receive this message : ERROR CODE:PE101
I receive a confirmation email that seem OK
Can you check if something wrong with my purchase
thank you
Danny

[solved] Feature Request: Formatting Profile indicator (2 replies)

haylo75 — Fri, 01 Feb 2013 21:16:40 +0100

Guido,

Currently, it is difficult to tell which of the four Formatting Profiles is in use. For example, when I click 1,2,3 or 4, there is no visual indicator that any button has been clicked. Can the current profile's button be set to a pressed state, or perhaps the colors reversed?

Thanks!

[solved] Block comment formatting bug (2 replies)

haylo75 — Fri, 01 Feb 2013 21:18:41 +0100

Guido,

I am having trouble with line breaks in block comments being honored after formatting. Example query:

SELECT
	COL1
	,COL2
FROM
	TABLE_NAME
	/*
	This is a block comment.
	It should honor line breaks.
	So that formatting looks good.
	*/
WHERE
	1 = 1

After formatting, I want the line breaks between /* and */ to be left in place. Here is the SQLinForm result:

SELECT
	COL1
	,COL2
FROM
	TABLE_NAME
	/* This is a block comment. It should honor line breaks. So that formatting looks good. */
WHERE
	1 = 1

It appears that line breaks are translated into spaces. I see no options to change this behavior. This presents a lot of problems for us. We use block comments for both notes, and of course to comment portions of a query. Can an option be added to retain formatting of text inside block comments?

[solved] sqlinformlicense.cfg (5 replies)

boerkees — Tue, 08 Jan 2013 12:41:54 +0100

Hi,

When i start SQLInform (the version of Januari 2013) the only thing i get to see, is the licensingscreen with al the licensing data allready visible.
Clicking on 'Registrate' tells me to restart, but then i get the same. The program itself will not start.

I had the same on my Linux-machine but i solved that by making a shell script that first changes directory to the folder waar SQLInform and the licensefile sit and next starts up the SQLInform-jar. That works like a charm. (though i had to rename it as the Java runtime module of linux doesn't like more than one dot in the filename...)

Now on my workstation with WIndows XP i get this.
I allready made en link to the jarfile and in the propertysection of that link i tell Windows to startup from the folder where both SQLInform and the licensefile sit. It sees and reads the licensefile as that data is allready visible, but somehow the program itsel just wouldn't start.

With kind regards,

Kees de Boer
Beverwijk, the Netherlands

New Hotkey formatting problem (1 reply)

wrmosca — Sat, 05 Jan 2013 00:16:17 +0100

Hi Guido

I just downloaded the new version and am not clear on how the new hot key should be used. I registered Ctl+M as the hot key (note the Formatting Profile indicates the config file is corrupt but it seems to be working okay.

I opened NotePad and put in a simple statement like:
select *
from myTable1
where x = y

My profile changes all keywords to upper case. The hot key worked but put everything on one line so it formatted as this:
SELECT * FROM myTable1 WHERE x = y

Formatting directly in SQLinForn works correctly as does the feature to format the clipboard. Any idea as to why this is happening?

My system is Windows XP Pro SP3.

[solved] Problems formatting MS Access SQL (7 replies)

bbhulsey — Fri, 08 Feb 2013 18:56:05 +0100

Hello,
I work primarily in MS Access and have noticed a couple of issues with formatting Access SQL with the January 2013 release. For example if I enter:

SELECT tblSubjects.SubjectID, tblSubjects.FamMbr, tblSubjects.IdentifiedDate
FROM tblSubjects
WHERE tblSubjects.FamMbr="a" AND tblSubjects.IdentifiedDate Between #1/1/2012# And #12/1/2012#

and format this as VBString(2) I get the following:

strSQL= "" & _
"SELECT tblSubjects.SubjectID, " & _
"tblSubjects.FamMbr, " & _
"tblSubjects.IdentifiedDate " & _
"FROM tblSubjects " & _
"WHERE tblSubjects.FamMbr = "a" " & _
"AND tblSubjects.IdentifiedDate BETWEEN #1 / 1 / 2012# AND #12 / 1 / 2012#"

The quotes on "a" are incorrect. The "a" in this line should be enclosed in pairs of double quotes as follows:

"WHERE tblSubjects.FamMbr = ""a"" " & _

Alternatively, single quotes would work:

"WHERE tblSubjects.FamMbr = 'a' " & _

Also, is there no way to get rid of the null string on the first line and the spaces added to the dates in the BETWEEN clause? Ideally I would like to be able to format my SQL as

strSQL="SELECT tblSubjects.SubjectID, " & _
"tblSubjects.FamMbr, " & _
"tblSubjects.IdentifiedDate " & _
"FROM tblSubjects " & _
"WHERE tblSubjects.FamMbr = ""a"" " & _
"AND tblSubjects.IdentifiedDate BETWEEN #1/1/2012# AND #12/1/2012#"

Thanks!
Bruce

[solved] SQL in Form Not working (7 replies)

Saurabh — Fri, 04 Jan 2013 23:13:02 +0100

Hi,

I have been using this online tool for some time now and it has always been a boon. However since today morning this online formatter is not working and I am forced to use other online formatters which I am hating to use.

Could this be please looked into?

Thanks in advance for the wonderful tool.

Regards,

Saurabh.

[open] Support for CTE (5 replies)

Martin — Wed, 07 Nov 2012 13:47:48 +0100

I often use CTEs in sql server

WITH xyz AS (
SELECT a,b,c
FROM y
)
, uvw AS (
SELECT d,e,f
FROM z
)
SELECT xya.a, uvw.d
FROM xyz
INNER JOIN uvw
...

Currently CTEs are reformatted like
WITH xyz AS
(SELECT a,b,c
FROM y
)

Neither of the options i Linebreaks/Indention seem to affect the bracket placements for CTEs.

SQLinForm via werbserver Copy & Paste problem (5 replies)

JohnC — Sun, 09 Dec 2012 17:02:07 +0100

Hi, I have downloaded the latest version. This is the Realease of September 2012, together with the system tray. An HTML file put this .jar file online on our intranet via a web server. Unfortunately, there is a problem with copy and paste, because it doesn´t work. It isn´t possible to insert the syntax in the Java applet or copy out of it. In the forum I found the following page:

[www.sqlinform.com]

The work around actually helps, but it is advanced to make changes to each computer in our network. The computers have different requirements, so it isn´t possible to make the changes via GPO. So this solution isn´t possible in our case. To use your online version, it isn´t necessary to make changes in the local java.policy file. So I think there is certainly a different solution.

Can you help me?

Thanks,
John

[solved] ON keyword in relation to JOIN keyword (6 replies)

haylo75 — Wed, 05 Sep 2012 16:28:41 +0200

Hello, Guido! I presented SQLinForm to my new team yesterday, and they are very happy we purchased a site license. :) A question regarding JOINs came up as part of the discussion. Take this query for example:

SELECT COL1
	, COL2
FROM THIS_TABLE
	INNER JOIN THAT_TABLE
	ON THIS_TABLE.COL1 = THAT_TABLE.COL1
		AND THIS_TABLE.COL2 = THAT_TABLE.COL2
	INNER JOIN THIRD_TABLE
	ON THIRD_TABLE.COL1 = THAT_TABLE.COL1
		AND THIRD_TABLE.COL2 = THAT_TABLE.COL2
WHERE THIS_TABLE.COL1 = 'XXX'
	AND THAT_TABLE.COL2 = 'YYY'
GROUP BY THIS_TABLE.COL1
	, THIS_TABLE.COL2

The desired output is a bit different, where ON is subordinate to the JOIN clause, and at the same tab level as any subsequent AND join conditions. Is this possible using the current version of SQLinForm? If not, I would humbly like to request this option as a feature. For your convenience, I've attached the standard options file used to achieve the above format.

SELECT	COL1
	, COL2
FROM THIS_TABLE
	INNER JOIN THAT_TABLE
		ON THIS_TABLE.COL1 = THAT_TABLE.COL1
		AND THIS_TABLE.COL2 = THAT_TABLE.COL2
	INNER JOIN  THIRD_TABLE
		ON THIRD_TABLE.COL1 = THAT_TABLE.COL1
		AND THIRD_TABLE.COL2 = THAT_TABLE.COL2
WHERE THIS_TABLE.COL1 = 'XXX'
	AND THAT_TABLE.COL2 = 'YYY'
GROUP BY THIS_TABLE.COL1
	, THIS_TABLE.COL2

[closed] Trial version for CLI (1 reply)

Sebastien — Sun, 05 Aug 2012 15:37:21 +0200

Hi

Do you have a trial version or a limited version (like the 100 lines version) for the command line version to see if it acts like I want and fit my needs?

Thanks

[solved] Upper/Lower Case Manipulation (4 replies)

jcrane — Mon, 11 Mar 2013 22:03:33 +0100

I've noticed that the initial formatting of a SQL statement does not follow the case setting on the "Upper & Lower" tab. For instance, with the setting set to "Keyword Uppercase", the following statement:

SELECT COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4 FROM TABLE WHERE NAME = 'TEST';

is formatted as such:

SELECT COLUMN_1
 , COLUMN_2
 , COLUMN_3
 , COLUMN_4
FROM TABLE
WHERE NAME = 'TEST';

It clearly ignores the case assignment of "Keyword Upper" and leaves the entire statement in all caps. It should be formatted as the following:

SELECT column_1
 , column_2
 , column_3
 , column_4
FROM table
WHERE name = 'TEST';

This is not a major bug but does become a nuisance when constantly formatting statements. How difficult is this to correct? Curently the work around requires numerous extra clicks when formatting statements.

Please ask if more questions are presented during this analysis.

[solved] Line breaks for lists and numeric column reference identifiers (4 replies)

haylo75 — Tue, 21 Aug 2012 22:10:18 +0200

Hi Guido,

I have recently changed companies, and my new colleagues like SQLInform so much that we purchased a new Lifetime Site License yesterday. :)-D

I have a question / potential feature request regarding line break options.

Take this SQL for example:

SELECT COL1,COL2,COL3 FROM MY_TABLE WHERE COL1 = COL2 GROUP BY 1,2 ORDER BY 1,2

In SQLInform, I have the following options selected:

Linebreaks / Indention (1) -> For Lists -> Before Comma = checked
Identifier(s) per line = 1

SQLInform formatted result:

SELECT
	COL1
	,COL2
	,COL3
FROM
	MY_TABLE
WHERE
	COL1 = COL2
GROUP BY
	1,2
ORDER BY
	1,2

The columns selected are each formatted correctly, but notice that the numeric "1,2" column reference identifiers in the GROUP BY and ORDER BY clauses are not being interpreted as Identifiers. Instead, they are left on a single line. Using the above options, here is the formatted SQL I would expect to see:

SELECT
	COL1
	,COL2
	,COL3
FROM
	MY_TABLE
WHERE
	COL1 = COL2
GROUP BY
	1
	,2
ORDER BY
	1
	,2

The numeric column identifier references are common in many SQL dialects, and I believe should be treated as Identifiers. Is it possible to enhance SQLInform in this manner?

Thank you for such an outstanding product!

[solved] License Key for SqlinForm Desktop Version (2 replies)

EMann — Tue, 17 Jul 2012 17:19:27 +0200

Hi,

I just purchased your SQLinForm Desktop version and I was wondering when I will receive my desktop license key. I played around with the free on-line version for a bit and I like how this product works. Please let me know how to access my license key so I can get my desktop version up and running soon.

Thanks

[solved] Upgrade to JRE 1.7.0_05 Breaks Online Free Version (4 replies)

Aaron — Tue, 03 Jul 2012 18:42:26 +0200

After upgrading to 1.7.0_05 the online free version no longer formats.

[solved] Format does not occur. (3 replies)

Tarun — Mon, 25 Jun 2012 23:44:35 +0200

Hello,

I have been been testing this tool for a week now and everything was fine till Friday, Today the format option does not work. The code simply does not get formattted. Please advise as my team and i need to give a demo on this tool today.

System Tray option (2 replies)

wrmosca — Sat, 16 Jun 2012 06:20:26 +0200

Guido

Sorry for the duplicate posting. I didn't realize the one I posted to was marked [closed].

I really like the system tray feature, but for one thing. I can't just minimize to the system tray. I still have an icon on the taskbar. It would be nice to be able to set an option to either minimize to one or the other but not both.

[solved] LOCKING Keyword (3 replies)

Munro Budd — Sun, 26 Aug 2012 00:09:40 +0200

Hi,
We use the following pattern for our SQL. The formatter returns all the LOCKING statements as one string. The Select format is fine.
Input SQL:
LOCKING TABLE EDW_IDS_DEV_DB.ADDRESS_LOCATION FOR ACCESS
LOCKING TABLE EDW_IDS_DEV_DB.ADJUSTMENT FOR ACCESS
SELECT whatever
FROM EDW_IDS_DEV_DB.ADDRESS_LOCATION AS LOC
,EDW_IDS_DEV_DB.ADJUSTMENT AS ADJ
WHERE LOC.col1 = ADJ.col7

Can we have each LOCKING statement output with its own linebreak please?

M. Budd

PS: This is Teradata SQL