-
1 Attachment(s)
VB - SQL 'Select' statement formatter/checker
The attached module contains a function called FormatSelect, which formats an SQL Select statement string so that it is easier to read.
This should hopefully help you to make changes more easiy, or (if you have errors) enable you to see any mistakes much more quickly.
As a little bonus, the function will also notify you if there are obvious errors, such as the wrong number of quotes to mark the start/end of strings.
Example usage:
VB Code:
Dim sSQL as String
sSQL = "SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3"
sSQL = FormatSelect(sSQL)
MsgBox sSQL
A couple of examples of the inputs/outputs of the function:
1) if the following Select statement is provided as input:
Code:
SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3
...it gets converted into this:
Code:
SELECT Table1.Field1
FROM Table1
WHERE Field2 = 3
2) if this Select statement is provided as input:
Code:
SELECT Table1.Field1 AS Field1, Table1.Field2 AS Field2, CASE WHEN Table2.Field3 = 0 THEN 'NO'
WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3, IIf(Field4=1,1,0)
From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
Where Table1.Field5 Between '1/1/2005' AND '1/20/2005' AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
OR Table1.Field7 = '1/20/2005'
AND Table1.Field8 IN(SELECT Field1 FROM table4 WHERE field2 IN(SELECT Field1 WHERE a=b FROM table2))
...it gets converted into this:
Code:
SELECT Table1.Field1 AS Field1,
Table1.Field2 AS Field2,
CASE WHEN Table2.Field3 = 0 THEN 'NO' WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3,
IIf ( Field4 = 1, 1, 0 )
FROM Table1
LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
WHERE Table1.Field5 Between '1/1/2005' AND '1/20/2005'
AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
OR Table1.Field7 = '1/20/2005'
AND Table1.Field8 IN (
SELECT Field1
FROM table4
WHERE field2 IN (
SELECT Field1
WHERE a = b
FROM table2
)
...with the following warning message:
Code:
(in sub-query:
(in sub-query:
Incorrect section ordering: FROM should be before WHERE
)
)
Note that the blank lines are optional, and can be disabled like this:
VB Code:
sSQL = FormatSelect(sSQL, False)
The number of spaces to use as an indent (for 'normal' text, and for subqueries) can also be specified, using two further optional parameters. eg:
VB Code:
sSQL = FormatSelect(sSQL, , 4, 2)
An alternative version by CVMichael can be found here: VB - SQL Select Formatter.
Please let me know if there are any parts of SQL syntax which are not handled appropriately. :)
-
Re: VB - SQL Statement formatter/checker
Hi si_the_geek,
First off... I've been looking for something like this for a long time because in my application I made for work I have over 100 SQL statements that are like reports.
All the SQL statements are very complex, so I guess it's perfect for testing your code.
First off, I tried with 3 SQL statements until now, and 2 of them I get an infinite loop in this function: FormatSelect_Worker
In this code:
VB Code:
Select Case aKeywords(lThisSectionType)
Case "SELECT", "GROUP BY", "ORDER BY"
lTempPos = lCurrPos
Do
Do
lTempPos = InStr(lTempPos, sText, ",")
lBracketStart = InStr(lCurrPos, sText, "(")
If (lBracketStart > 0) And (lBracketStart < lTempPos) Then
lBracketEnd = FindCloseBracket(sText, lBracketStart)
If lBracketEnd < lNextSectionStart Then
lTempPos = lBracketEnd
Else
lBracketStart = 0
End If
Else
lBracketStart = 0
End If
Loop While (lBracketStart > 0) And (lTempPos > 0)
This is one of the simple SQL statements it's doing this:
Code:
SELECT FORMAT(abt.DateChanged, "MM/DD/YYYY") AS "Date", aa.Action, Count(*) AS "Total"
FROM tblAbnormalTran abt, tblAbnormalTranActions aa
WHERE abt.Action = aa.[ID]
AND CDATE(FORMAT(abt.DateChanged, "MM/DD/YYYY"))
BETWEEN a AND b
GROUP BY FORMAT(abt.DateChanged, "MM/DD/YYYY"), aa.Action
ORDER BY FORMAT(abt.DateChanged, "MM/DD/YYYY") DESC
-
Re: VB - SQL Statement formatter/checker
Ok, I've fixed that - it was due to a 'last minute' change I made, that obviously wasn't quite right!
I also spotted a couple of things raised by your SQL, and a couple of code issues/improvements.
Updates:
- Allowed strings to be marked by ' or " (both can be used in the same SQL statement)
- Changed formatting of brackets (they now have a space before and after them)
- Fixed a couple of potenial issues where part of the next 'section' could be added to the same section
- Fixed issue when strings start/end with doubled-up quote characters (they were previously removed from the string)
-
Re: VB - SQL Statement formatter/checker
Si, we've had a major server/network meltdown - and I was visiting a client for a couple ofday - nothing ever goes smooth...
I've got lots and lots of stored procedures - would you like me to send you some or should I just run the function on them and report back to you?
-
Re: VB - SQL Statement formatter/checker
I read about the meltdown in another thread, sounds very very painful - hope you manage to get it all sorted quickly.
Either way is fine by me - although I'd like to see any SQL statements that show up issues with my code!
Note tho that the formatter only works for Select statements (for the moment at least!).
-
Re: VB - SQL Statement formatter/checker
Quote:
Originally Posted by si_the_geek
I read about the meltdown in another thread, sounds very very painful - hope you manage to get it all sorted quickly.
Either way is fine by me - although I'd like to see any SQL statements that show up issues with my code!
Note tho that the formatter only works for Select statements (for the moment at least!).
I'm waiting for your version on those other sql's specially for action queries... :)
-
Re: VB - SQL 'Select' statement formatter/checker
That could be quite awkward, as different DBMS's have much more variation in formatting for those, so don't hold your breath!
I wont attempt that until I'm sure it works for Select's tho, as most action queries can include them!
-
Re: VB - SQL 'Select' statement formatter/checker
OK... weird things are hapening.... I will highlight in bold those weird things:
Original SQL
Code:
SELECT FORMAT(fo.FileDate, "MM/DD/YYYY") AS "File Date",
Count(*) AS KITs, pa.Parts
FROM (tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID) LEFT JOIN
(
SELECT CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) AS FileDate,
OrderInstructionCode AS "Type",
Count(*) AS Parts
FROM tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
WHERE fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN a AND b
AND OrderInstructionCode = "PART"
GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode
) AS pa ON pa.FileDate = CDate(FORMAT(fo.FileDate, "MM/DD/YYYY"))
WHERE fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
AND OrderInstructionCode = "KIT"
GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode, pa.Parts
ORDER BY CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) DESC
Formated SQL:
Code:
SELECT
FORMAT ( fo.FileDate, "MM/DD/YYYY" ) AS "File Date",
Count ( * ) AS KITs,
pa.Parts
FROM
( tblOrderFileList ofl INNER
JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID )
LEFT JOIN (
SELECT
CDate ( FORMAT ( fo.FileDate, "" ) ) AS FileDate,
OrderInstructionCode AS "",
Count ( * ) AS Parts
FROM
tblOrderFileList ofl INNER
JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
WHERE
fo.CompanyName = ''
AND fo.FileDate BETWEEN a AND b
AND OrderInstructionCode = ""
GROUP BY
FORMAT ( fo.FileDate, "" ),
OrderInstructionCode
) AS pa ON pa.FileDate = CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) )
WHERE
fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
AND OrderInstructionCode = "Type"
GROUP BY
FORMAT ( fo.FileDate, "PART" ),
OrderInstructionCode,
pa.Parts
ORDER BY
CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) ) DESC
It's a problem with the quotes, it's removing stuff in the quotes, or replacing with wrong things...
-
Re: VB - SQL 'Select' statement formatter/checker
Ah yes, thats a perfect example to test my flaw!
I remove the strings & subqueries at the start of the routine, but my code to add them back to the final string was in the wrong order. :blush:
I've uploaded the corrected version.
-
Re: VB - SQL 'Select' statement formatter/checker
Found more errors...
Original SQL:
Code:
SELECT FORMAT(ShipDate, "YYYY MM") AS [Month], Count(*) AS Total
FROM tblInvoiceOrderFileList
WHERE OrderClaimStatusCode = 4 AND ShipDate
BETWEEN a AND b
GROUP BY FORMAT(ShipDate, "YYYY MM")
ORDER BY FORMAT(ShipDate, "YYYY MM") DESC
Formated SQL:
Code:
SELECT
FORMAT ( ShipDate, "YYYY MM" ) AS [Month],
Count ( * ) AS Total
FROM
tblInvoiceOrderFileList
WHERE
OR derClaimStatusCode = 4
AND ShipDate BETWEEN a AND b
GROUP BY
FORMAT ( ShipDate, "YYYY MM" )
ORDER BY
FORMAT ( ShipDate, "YYYY MM" ) DESC
I think you have to make it search for "OR " (with space after) because some words (like the previous) might contain or...
[Edit]
PS, I made my own SQL formater, should I post it here, or should I make a new thread ?
-
Re: VB - SQL 'Select' statement formatter/checker
Fixed, I had a ">" where I should have had a ">="!
it checks for any of these characters: " '""#()=<>[]+-/*" (or end of string) before & after strings to find, but missed the 'after' check when the match was the first character.
Quote:
PS, I made my own SQL formater, should I post it here, or should I make a new thread ?
It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.
-
Re: VB - SQL 'Select' statement formatter/checker
Quote:
Originally Posted by si_the_geek
It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.
OK, I'm gonna post it soon, I just want to do a little more testing...
I found another problem with your SQL formatter:
I get an error at this line (in FindFirstByArray function):
VB Code:
Const csPunctuation = " '""#()=<>[]+-/*"
Dim lFirstPos As Long
Dim lArrayIndex As Long
Dim lTempPos As Long
Dim lCount As Long
lFirstPos = Len(sText) + 1
lArrayIndex = 0
For lCount = 0 To UBound(vArray)
lTempPos = InStr(lStartPos, sText, vArray(lCount), vbTextCompare)
Do While (lTempPos >= lStartPos)
[COLOR=Orange][B]If InStr(csPunctuation, Mid$(sText, lTempPos - 1, 1)) Then[/B][/COLOR]
If lTempPos + Len(vArray(lCount)) > Len(sText) Then Exit Do
If InStr(csPunctuation, Mid$(sText, lTempPos + Len(vArray(lCount)), 1)) Then Exit Do
End If
lTempPos = InStr(lTempPos + 1, sText, vArray(lCount), vbTextCompare)
Loop
If (lTempPos > 0) And (lTempPos < lFirstPos) Then
lFirstPos = lTempPos
lArrayIndex = lCount
End If
Next lCount
lTempPos is = 1, so the result is 1 - 1 = 0, therefore an invalid # for the MID
And if I take off the -1, then the formatter says the select query is wrong on all my selects...
[Edit]
Also, something I forgot to say, Your formatter formats the code like this:
Code:
SELECT
Field1,
Field2,
....
When it's putting the "Field1" (the first field) on the second line, and I run the query, for some reason it does not return any data. But when I change it to this:
Code:
SELECT Field1,
Field2,
.....
Then everything is OK... it executes properly, and returns data...
-
Re: VB - SQL 'Select' statement formatter/checker
:( I added an extra bug when removing the previous one.
I'm gonna take a little while over it, and make sure I get it right this time.
-
Re: VB - SQL 'Select' statement formatter/checker
How's it going with your formatter ?
I posted my formatter here:
VB - SQL Select Formatter
-
Re: VB - SQL 'Select' statement formatter/checker
Cool, I've added a link :)
Well I finally got my head into a coding mood again - I really don't know what was wrong with me!
Here's the updates:
- Corrected FindFirstByArray.
- Due to the problem mentioned above (SQL will not run in ?Access?), I removed the new line immediately after "Select"/"From"/etc, and added the option for blank lines between each 'section' instead.
- Fixed typo for bracket errors.
- Corrected mis-use of a variable for passing the And in a Between clause (could cause infinite loop if Between was malformed!).
- Better formatting of error messages.
- Allowed # for dates, rather than just ' or ".
- Added more Join types.
-
Re: VB - SQL 'Select' statement formatter/checker
The new version is MUCH better ! No errors at all ! :thumb:
But could you make it have an option for how many spaces to put for indent ?
Because 10 spaces is WAY too much for me, I like 4 spaces...
To give you an idea, this:
Code:
SELECT DISTINCT MFrom AS "From Model", MWith AS "To Model" FROM
(
SELECT DISTINCT MFrom, TRIM(IIF(MWith LIKE 'abc5%', MID(MWith, 5, 100), MWith)) AS MWith FROM
(
SELECT MFrom, IIF(INSTR(MWith, '(') = 0, MWith, LEFT(MWith, INSTR(MWith, '(')-1)) AS MWith FROM
(
SELECT DISTINCT MFrom, MWith FROM
(
SELECT MFrom, TRIM(IIF(Num3 = 0, MWith, LEFT(MWith, Num3 - 1))) AS MWith FROM
(
SELECT MFrom, MWith, IIF(Num3 = 0, Num2, Num3) AS Num3 FROM
(
SELECT MFrom, MWith, IIF(Num2 = 0, Num1, Num2) AS Num2, IIF(Num2 = 0, 0, INSTR(Num2 + 1, MWith, '-')) AS Num3
FROM
(
SELECT MFrom, MWith, Num1, IIF(Num1 = 0, 0, INSTR(Num1 + 1, MWith, '-')) AS Num2 FROM
(
SELECT MFrom, MWith, INSTR(MWith, '-') AS Num1 FROM
(
SELECT LEFT(m_ofl.Notes, INSTR(m_ofl.Notes, '|') - 1) AS MFrom
, IIF(INSTR(repl.Notes, ',') > 0, LEFT(repl.Notes, INSTR(repl.Notes, ',')-1), repl.Notes) AS MWith
FROM tblInvoice AS repl INNER JOIN
(
SELECT OrderNumber, Notes FROM tblInvoice
WHERE StatusCode = 4
AND INSTR(Notes, '|') > 0
) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
WHERE repl.RecordIDType = 'STAT'
AND repl.StatusCode = 7
AND repl.Notes NOT LIKE '%abc%'
AND repl.Notes NOT LIKE '%abc2%'
AND repl.Notes NOT LIKE '%abc3%'
AND ShipDate > #Apr 01, 2004#
))))))
WHERE LEN(MWith) > 1
)
WHERE INSTR(MWith, MFrom) = 0
))
ORDER BY MFrom, MWith
You get:
Code:
SELECT DISTINCT MFrom AS "From Model",
MWith AS "To Model"
FROM (
SELECT DISTINCT MFrom,
TRIM ( IIF ( MWith LIKE 'abc5%', MID ( MWith, 5, 100 ), MWith ) ) AS MWith
FROM (
SELECT MFrom,
IIF ( INSTR ( MWith, '(' ) = 0, MWith, LEFT ( MWith, INSTR ( MWith, '(' ) - 1 ) ) AS MWith
FROM (
SELECT DISTINCT MFrom,
MWith
FROM (
SELECT MFrom,
TRIM ( IIF ( Num3 = 0, MWith, LEFT ( MWith, Num3 - 1 ) ) ) AS MWith
FROM (
SELECT MFrom,
MWith,
IIF ( Num3 = 0, Num2, Num3 ) AS Num3
FROM (
SELECT MFrom,
MWith,
IIF ( Num2 = 0, Num1, Num2 ) AS Num2,
IIF ( Num2 = 0, 0, INSTR ( Num2 + 1, MWith, '-' ) ) AS Num3
FROM (
SELECT MFrom,
MWith,
Num1,
IIF ( Num1 = 0, 0, INSTR ( Num1 + 1, MWith, '-' ) ) AS Num2
FROM (
SELECT MFrom,
MWith,
INSTR ( MWith, '-' ) AS Num1
FROM (
SELECT LEFT ( m_ofl.Notes, INSTR ( m_ofl.Notes, '|' ) - 1 ) AS MFrom,
IIF ( INSTR ( repl.Notes, ',' ) > 0, LEFT ( repl.Notes, INSTR ( repl.Notes, ',' ) - 1 ), repl.Notes ) AS MWith
FROM tblInvoice AS repl
INNER JOIN (
SELECT OrderNumber,
Notes
FROM tblInvoice
WHERE StatusCode = 4
AND INSTR ( Notes, '|' ) > 0
) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
WHERE repl.RecordIDType = 'STAT'
AND repl.StatusCode = 7
AND repl.Notes NOT LIKE '%abc%'
AND repl.Notes NOT LIKE '%abc2%'
AND repl.Notes NOT LIKE '%abc3%'
AND
(I could not paste all the code, too many characters)
Wich is way to much indenting for me....
Also, where whould I have to change your code to make it act as if {} brackets are like a string ? I don't want your formatter to make any changes between the {} brackets, so how do I do that ?
-
Re: VB - SQL 'Select' statement formatter/checker
I disagree with your spacing amounts, but agree with making it optional :) I have done that, and changed the default subquery indent size to be just over the normal indent, rather than double it.
To make {} act as a string, the best course of action would probably be to add an extra bit of code before/after the strings are removed to store the {} 'strings' to a separate array, then re-insert them after/before the strings (opposite of what you did above).
Unfortunately the strings code can't really be altered for this easily enough, as all other markers are the same for start/end, whereas these differ - a modified version ofthe strings code would work well tho. On the plus side, for this you would only need a one dimensional array - as you know the markers.
-
Re: VB - SQL 'Select' statement formatter/checker
When I call it like this:
Me.txtQuery.Text = FormatSelect(Me.txtQuery.Text, False, 4, 8)
I get an error at this line (In FormatSelect_Worker):
VB Code:
'add keyword (Ucase), and spaces to indent
sRetString = sRetString & aKeywords(lThisSectionType) & Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
But I fixed the problem by changing the code to this:
VB Code:
'add keyword (Ucase), and spaces to indent
If Len(sIndent) - Len(aKeywords(lThisSectionType)) <= 0 Then
sRetString = sRetString & aKeywords(lThisSectionType) & " "
Else
sRetString = sRetString & aKeywords(lThisSectionType) & Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
End If
-
Re: VB - SQL 'Select' statement formatter/checker
Ah yes, I should have checked a few values like that!
I've updated using basically the same code you posted, with just a minor variation of the If for efficiency:
VB Code:
If Len(aKeywords(lThisSectionType)) >= lIndentSize Then
-
Re: VB - SQL 'Select' statement formatter/checker
i tried this on the odbc commands and it got problem ...
exec sp_executesql N'SELECT "pk" FROM "dbo"."m_clients" WHERE id = @P1 AND closing_status = @P2 AND calc_status = @P3 AND opening_effec_date <= @P4 AND closing_effec_date > @P5 ', N'@P1 int,@P2 int,@P3 int,@P4 datetime,@P5 datetime', 11893738, 20, 0, 'Jul 1 2004 12:00:00:000AM', 'Jul 1 2004 12:00:00:000AM'
exec sp_executesql N'INSERT INTO "dbo"."p_costing_sheet_results" ("policy_no","cover_no","benefit_type","role","role_id","membership_category_no","series","opening_s tatus","opening_reg_date","opening_effec_date","opening_ref","closing_status","closing_reg_date","cl osing_effec_date","closing_ref","member_id","gender","entry_age","from_age","to_age","salary","pensi onable_salary","quantity","annual_premium","annual_basic_premium","sum_assured_amount","basic_premiu m_rate","total_restricted_sa_amount","capitalised_sum_assured","last_charging_date","current_net_ann ual_premium","prev_net_annual_premium","periodic_premium","action_code","adjustment_code","effec_dat e_adjustment_feed","charging_season_factor","event_date","dob","occupation","internal_cover_no","clo sing_effec_date_at_term","restricted_sa_max_benefit","total_sa_for_high_earners","capitalise_factor" ,"factor_for_retiree","closed_record_invoiced_date","benefit_type_param1","ref_date_time_changes" ) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P2 2,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P4 2,@P43,@P44,@P45,@P46,@P47,@P48,@P49)', N'@P1 float,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 datetime,@P10 datetime,@P11 varchar(255),@P12 int,@P13 datetime,@P14 datetime,@P15 varchar(255),@P16 int,@P17 int,@P18 int,@P19 int,@P20 int,@P21 float,@P22 float,@P23 int,@P24 float,@P25 float,@P26 float,@P27 float,@P28 float,@P29 float,@P30 datetime,@P31 float,@P32 float,@P33 float,@P34 int,@P35 int,@P36 datetime,@P37 float,@P38 datetime,@P39 datetime,@P40 int,@P41 int,@P42 datetime,@P43 float,@P44 float,@P45 float,@P46 float,@P47 datetime,@P48 int,@P49 datetime', 6.348980000000000e+005, 9000, 0, 13, 11893738, 4, 0, 10, 'Oct 5 2005 12:00:00:000AM', 'Jul 1 2005 12:00:00:000AM', '', 0, 'Jan 1 3000 12:00:00:000AM', 'Jan 1 3000 12:00:00:000AM', '', 12605586, 2, 37, 37, 0, 2.565040000000000e+004, 0.000000000000000e+000, 1, 2.360000000000000e+001, 2.360000000000000e+001, 5.130100000000000e+004, 4.600000000000000e-001, 5.130080000000000e+004, 0.000000000000000e+000, 'Jan 1 1900 12:00:00:000AM', 2.139359900000000e+001, 0.000000000000000e+000, 2.360000000000000e+001, 1, 0, 'Jan 1 1900 12:00:00:000AM', 1.000000000000000e+000, 'Jan 1 1900 12:00:00:000AM', 'Jul 11 1968 12:00:00:000AM', 0, 0, 'Jan 1 1900 12:00:00:000AM', 5.130080000000000e+004, 0.000000000000000e+000, 0.000000000000000e+000, 1.000000000000000e+000, 'Jan 1 1900 12:00:00:000AM', 0, 'Jan 1 1900 12:00:00:000AM'
-
Re: VB - SQL 'Select' statement formatter/checker
avihayt , it works only for SELECT statements...
-
Re: VB - SQL 'Select' statement formatter/checker
Yep, if you pass it just the text from "SELECT" up to the first "@P5" then it works.
The second statement is not compatible at all at this time, as I have not allowed for action statements (Insert/Update/etc) yet.
I am not sure if I at some point in the future I will allow for SP's or commands such as Exec.
-
Re: VB - SQL 'Select' statement formatter/checker
si,
Are you still working on this formatting project? I am working on VB.Net project which can use your formatting function. My project basically allow in place formatting in SQL QUERY ANALYZER using Hooks.
- AJ :wave:
-
Re: VB - SQL 'Select' statement formatter/checker
Hi ajviradia, welcome to VBForums! :wave:
I haven't done anything more to it yet, the trouble is that there are many different formats for Inserts (for example) depending on what DBMS you are using. I may have a go at adding more this weekend, assuming I am up to date on my work!
Your use of it sounds good, I'd like to see that when you get it working. :)
-
Re: VB - SQL 'Select' statement formatter/checker
Why not just format the Sql string while typing??? Why all this fuss??
-
Re: VB - SQL 'Select' statement formatter/checker
I originally designed it because we had lots of threads along the lines of "what is the error in this SQL statement?", and they didn't have any formatting at all (just one long string!).
That meant we had to manually format the SQL statements before we could attempt to answer. I realised that the amount of time I would save by having the code offset the time to write it.. and thankfully it was easy to add basic error checking too.
It also allows for those times when you are given an existing project to support, and the SQL statements aren't formatted (from my experience, this is often the case!).
-
Re: VB - SQL 'Select' statement formatter/checker
Thanx)) using it really makes everything easier: making changes and seeing mistakes much more quickly,notifying errors..:thumb:
-
Re: VB - SQL 'Select' statement formatter/checker
Hi, nice code!
I noticed the VB formatter doesn't handle simple UNIONs, eg
"SELECT * FROM A UNION SELECT * FROM B"
I am using VBA 6.5
-
Re: VB - SQL 'Select' statement formatter/checker
Welcome to VBForums :wave:
Thanks for your comments... I can confirm that it doesn't handle Unions at the moment (I'm surprised nobody has mentioned it before!), and I will try to add that in the next few days.
-
1 Attachment(s)
VB.NET - SQL 'Select' statement formatter/checker!
This project was incredibly helpful to me, so I figured I would post my changes to make this VB.NET compliant. Nothing else seemed to be out there and completely free.
Best Regards to all,
David
-
Re: VB - SQL 'Select' statement formatter/checker
Hi
I would really like to try this module out but need help please.
Using Excel and/or Access how can I make use of this module?
Thanks in advance
-
Re: VB - SQL 'Select' statement formatter/checker
You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.
If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.
You can then use it as shown earlier in this thread.
-
Re: VB - SQL 'Select' statement formatter/checker
Quote:
Originally Posted by
si_the_geek
You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.
If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.
You can then use it as shown earlier in this thread.
Alright thanks I think the best thing for me to do woudl be to go through some tutorials and try to figure out how I can write something to implement this.