SQLinForm SQL FormatterCommunity Discussion Forum
SQLinForm SQL Formatter
I recently upgraded to 6.23.09.01 of the desktop app. I had Profile 2 set up to automatically format SQL as a VB string (2) format - e.g. strSQL = "sql line 1" & _ "sql line 2" & _ ... "sql line n". Now when I use Profile 2 my SQL is formatted correctly but it is not preceded by strSQL = and the individual lines are not quoted. I see that there is a Generate Code button that I cannot find documentation for. When I click it I get a VB string formatted string but I lose all my SQL formatting.
For example if I start with
SELECT vBDSTEPS_SubjectID_CenterID.SubjectID, vBDSTEPS_SubjectID_CenterID.CenterID, tblPatientExam.PROCEDURE, tblPatientExam.OTHERPROCEDURE, tblPatientExam.Date, vBDSTEPS_SubjectID_CenterID.SubjectID, tblPatientExam.SPECIALTY, tblPatientExam.LOCATION, tblPatientExam.OTHERLOCATION, tblPatientExam.RESULTS, tblPatientExam.SpecialtyOther, tblPatientExam.Date, tblPatient.CDOB
FROM (tblPatientExam INNER JOIN vBDSTEPS_SubjectID_CenterID ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID) INNER JOIN tblPatient ON tblPatientExam.CenterID = tblPatient.CenterID
WHERE tblPatientExam.Date)<[tblPatient].[CDOB];
and then click the Profile 2 button I now get
SELECT vBDSTEPS_SubjectID_CenterID.SubjectID,
vBDSTEPS_SubjectID_CenterID.CenterID,
tblPatientExam.PROCEDURE,
tblPatientExam.OTHERPROCEDURE,
tblPatientExam.Date,
vBDSTEPS_SubjectID_CenterID.SubjectID,
tblPatientExam.SPECIALTY,
tblPatientExam.LOCATION,
tblPatientExam.OTHERLOCATION,
tblPatientExam.RESULTS,
tblPatientExam.SpecialtyOther,
tblPatientExam.Date,
tblPatient.CDOB
FROM (tblPatientExam
INNER JOIN vBDSTEPS_SubjectID_CenterID
ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID)
INNER JOIN tblPatient
ON tblPatientExam.CenterID = tblPatient.CenterID
WHERE tblPatientExam.Date) < [tblPatient].[CDOB];
instead of the desired format:
strSQL = "SELECT vBDSTEPS_SubjectID_CenterID.SubjectID," & _
"vBDSTEPS_SubjectID_CenterID.CenterID," & _
"tblPatientExam.PROCEDURE," & _
"tblPatientExam.OTHERPROCEDURE," & _
"tblPatientExam.Date," & _
"vBDSTEPS_SubjectID_CenterID.SubjectID," & _
"tblPatientExam.SPECIALTY," & _
"tblPatientExam.LOCATION," & _
"tblPatientExam.OTHERLOCATION," & _
"tblPatientExam.RESULTS," & _
"tblPatientExam.SpecialtyOther," & _
"tblPatientExam.Date," & _
"tblPatient.CDOB" & _
"FROM (tblPatientExam" & _
"INNER JOIN vBDSTEPS_SubjectID_CenterID" & _
"ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID)" & _
"INNER JOIN tblPatient" & _
"ON tblPatientExam.CenterID = tblPatient.CenterID" & _
"WHERE tblPatientExam.Date) < [tblPatient].[CDOB];"
If I click the Generate Code button I get
strSQL= "" & _
"SELECT vBDSTEPS_SubjectID_CenterID.SubjectID, vBDSTEPS_SubjectID_CenterID.CenterID, tblPatientExam.PROCEDURE, tblPatientExam.OTHERPROCEDURE, tblPatientExam.Date, vBDSTEPS_SubjectID_CenterID.SubjectID, tblPatientExam.SPECIALTY, tblPatientExam.LOCATION, tblPatientExam.OTHERLOCATION, tblPatientExam.RESULTS, tblPatientExam.SpecialtyOther, tblPatientExam.Date, tblPatient.CDOB " & _
"FROM (tblPatientExam INNER JOIN vBDSTEPS_SubjectID_CenterID ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID) INNER JOIN tblPatient ON tblPatientExam.CenterID = tblPatient.CenterID " & _
"WHERE tblPatientExam.Date)<[tblPatient].[CDOB];"
How do I get back to my old format without reverting to an earlier version of SQLInform?
-Bruce
Hi Bruce, thanks for your feedback.
The SQLinForm "Generate Code" function not only converted the SQL in a VB but also did the formatting in the same time. This was a little bit confusing for some users as they "forgot" they they have set this option and this produced strange results.
Therefore I split the "Formatting" feature from the "Generate Code" feature
Now you need 2 clicks for formatting and generating code.
I could add in the next release a button to format and generate source code with one click. Would this help?
Regards
Guido
Hi Guido, I understand that some people may have found it confusing but it made sense to me to simply dedicate a profile to generating a VB formatted string. Although I would prefer it to be a single step like it was before, I don't mind now having an extra step. I wonder if having a "button to format and generate source code with one click" would simply cause more confusion.
What I am doing seems to involve three steps (click Profile 2, Extract&Gen*, then Generate Code) , am I doing this incorrectly?
My current problem is that even with these steps (click Profile 2, Extract&Gen*, then Generate Code) I am not able to generate the same VB formatted string as before. For example if I start with this as my input:
SELECT vBDSTEPS_SubjectID_CenterID.SubjectID, vBDSTEPS_SubjectID_CenterID.CenterID, tblPatientExam.PROCEDURE, tblPatientExam.OTHERPROCEDURE, tblPatientExam.Date, vBDSTEPS_SubjectID_CenterID.SubjectID, tblPatientExam.SPECIALTY, tblPatientExam.LOCATION, tblPatientExam.OTHERLOCATION, tblPatientExam.RESULTS, tblPatientExam.SpecialtyOther, tblPatientExam.Date, tblPatient.CDOB
FROM (tblPatientExam INNER JOIN vBDSTEPS_SubjectID_CenterID ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID) INNER JOIN tblPatient ON tblPatientExam.CenterID = tblPatient.CenterID
WHERE tblPatientExam.Date)<[tblPatient].[CDOB];
and then I click Profile 2 and then Extract&Gen*, then Generate code, I get this:
strSQL= "" & _
"SELECT vBDSTEPS_SubjectID_CenterID.SubjectID, vBDSTEPS_SubjectID_CenterID.CenterID, tblPatientExam.PROCEDURE, tblPatientExam.OTHERPROCEDURE, tblPatientExam.Date, vBDSTEPS_SubjectID_CenterID.SubjectID, tblPatientExam.SPECIALTY, tblPatientExam.LOCATION, tblPatientExam.OTHERLOCATION, tblPatientExam.RESULTS, tblPatientExam.SpecialtyOther, tblPatientExam.Date, tblPatient.CDOB " & _
"FROM (tblPatientExam INNER JOIN vBDSTEPS_SubjectID_CenterID ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID) INNER JOIN tblPatient ON tblPatientExam.CenterID = tblPatient.CenterID " & _
"WHERE tblPatientExam.Date)<[tblPatient].[CDOB];"
when what I actually want is this (which I get with older version of SQLInform):
strSQL = "SELECT vBDSTEPS_SubjectID_CenterID.SubjectID," & _
"vBDSTEPS_SubjectID_CenterID.CenterID," & _
"tblPatientExam.PROCEDURE," & _
"tblPatientExam.OTHERPROCEDURE," & _
"tblPatientExam.Date," & _
"vBDSTEPS_SubjectID_CenterID.SubjectID," & _
"tblPatientExam.SPECIALTY," & _
"tblPatientExam.LOCATION," & _
"tblPatientExam.OTHERLOCATION," & _
"tblPatientExam.RESULTS," & _
"tblPatientExam.SpecialtyOther," & _
"tblPatientExam.Date," & _
"tblPatient.CDOB" & _
"FROM (tblPatientExam" & _
"INNER JOIN vBDSTEPS_SubjectID_CenterID" & _
"ON tblPatientExam.CenterID = vBDSTEPS_SubjectID_CenterID.CenterID)" & _
"INNER JOIN tblPatient" & _
"ON tblPatientExam.CenterID = tblPatient.CenterID" & _
"WHERE tblPatientExam.Date) < [tblPatient].[CDOB];"
For the time being I managed to revert to GUI version 6.23.04.11, engine 6.23.04.06 which gives me the format I need simply by clicking Profile 2.
@bbhulsey Hi Bruce,
will try to find a solution for this so that you can Extract & format & Generate simply by clicking Profile 2
Regards
Guido
could you please download the new release from https://www.sqlinform.com/bruce/ (Installation Instructions) and give it a try.
I added two new options.
Hope it does what you expected.
Guido
Hello Guido, the new .dll works perfectly if I uncheck the Always "Extract SQL" before "Format SQL" option. Again, my intent is simply to be able to generate a VB string from plain SQL so I think the "extract" option is not needed for me. Thanks again!
