Script tool for writing SQL query in SPSS syntax.
Locked
Script tool for writing SQL query in SPSS syntax.
 
|
Hi Everyone,
As you probably know, writing an SQL query in an SPSS syntax is not convenient: You have to add quotes at the beginning of the row, and a space, quotes, and a plus sign at the end of it. Doing it from the "open database" wizard solves that problem, but doesn’t preserve the line structure of the query. We* created a nice script, which can be added as an SPSS tool, that adjusts SQL queries into SPSS syntax format. You just copy the SQL from wherever you want (notepad, TOAD, syntax itself ..), click the icon and the adjusted query will be pasted into the syntax. Feel free to use it, Feel free to tell me if it helps … *created by Mark Haviv - Genius.co.ilPasteSQL_instructions.docxpasteSqlToSyntax.sbsPasteSQL.bmp |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
This could be a useful tool as formatting
externally generated SQL for use in Statistics is a pain in the neck. In
fact, there is already a custom dialog to do this available from the SPSS
Community website (www.ibm.com/developerworks/spssdevcentral).
The post doesn't say how to get Ehud's tool, but we invite anyone who has something to share to use the IBM developerWorks site for sharing. Details on how to do this are available in the Welcome section of the SPSS Community. Items shared this way can be listed in the SPSS Community site although they are owned and maintained independently. Regards, Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: Ehud Levitin <[hidden email]> To: [hidden email] Date: 06/10/2012 06:50 AM Subject: [SPSSX-L] Script tool for writing SQL query in SPSS syntax. Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Everyone, As you probably know, writing an SQL query in an SPSS syntax is not convenient: You have to add quotes at the beginning of the row, and a space, quotes, and a plus sign at the end of it. Doing it from the "open database" wizard solves that problem, but doesn’t preserve the line structure of the query. We* created a nice script, which can be added as an SPSS tool, that adjusts SQL queries into SPSS syntax format. You just copy the SQL from wherever you want (notepad, TOAD, syntax itself ..), click the icon and the adjusted query will be pasted into the syntax. Feel free to use it, Feel free to tell me if it helps … *created by Mark Haviv - Genius.co.il http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/PasteSQL_instructions.docx PasteSQL_instructions.docx http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/pasteSqlToSyntax.sbs pasteSqlToSyntax.sbs http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/PasteSQL.bmp PasteSQL.bmp -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Script-tool-for-writing-SQL-query-in-SPSS-syntax-tp5713613.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Script tool for writing SQL query in SPSS syntax.
Administrator
|
Reading via Nabble, there appears to be an attachment. When I click the hyperlink, I see the script file displayed in my browser.
http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/pasteSqlToSyntax.sbs
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
In reply to this post by Ehud Levitin
Another alternative is to use the old GET CAPTURE command, we use it to
paste hundreds of our sql scripts from our jobs in Banner into spss syntax without having to deal with all the quotes & + signs: GET CAPTURE ODBC /CONNECT=insert connect string here /SELECT insert sql script here just as it is written on the sql server side. CACHE. EXECUTE. no need for quotes or plus signs to demarcate line breaks: GET CAPTURE ODBC /CONNECT=insert connect string here /SELECT col1, col2 col3, col4 from database where col1=x and col3=x. CACHE. EXECUTE. On Sun, Jun 10, 2012 at 7:45 AM, Ehud Levitin wrote: > Hi Everyone, > As you probably know, writing an SQL query in an SPSS syntax is not > convenient: > You have to add quotes at the beginning of the row, and a space, > quotes, and > a plus sign at the end of it. Doing it from the "open database" wizard > solves that problem, but doesn’t preserve the line structure of the > query. > We* created a nice script, which can be added as an SPSS tool, that > adjusts > SQL queries into SPSS syntax format. > You just copy the SQL from wherever you want (notepad, TOAD, syntax > itself > ..), click the icon and the adjusted query will be pasted into the > syntax. > > > Feel free to use it, > Feel free to tell me if it helps … > > *created by Mark Haviv - Genius.co.il > > http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/PasteSQL_instructions.docx > PasteSQL_instructions.docx > > http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/pasteSqlToSyntax.sbs > pasteSqlToSyntax.sbs > > http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/PasteSQL.bmp > PasteSQL.bmp > > -- > View this message in context: > http://spssx-discussion.1045642.n5.nabble.com/Script-tool-for-writing-SQL-query-in-SPSS-syntax-tp5713613.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > [hidden email] (not to SPSSX-L), with no body text except > the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
In reply to this post by Bruce Weaver
There are three files attached to the first post
1. PasteSQL_instructions.docx - word guide that explain how to do it 2. pasteSqlToSyntax.sbs - the script 3. PasteSQL.bmp - the Icon bmp You can download them all and use them. This tool doesn't require python plugin. The GET CAPTURE ODBC solution works, (Thanks !!!!) but as an old command, is it still supported by IBM ? (I'm afraid that in the future it might stop working) Thanks Ehud |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
GET CAPTURE is still supported but is deprecated
in favor of GET DATA /TYPE=ODBC. What functionality is provided in GET
CAPTURE that cannot be accomplished with GET DATA?
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] Phone: 312.893.4922 | T/L: 206-4922 From: Ehud Levitin <[hidden email]> To: [hidden email] Date: 06/13/2012 09:14 AM Subject: Re: Script tool for writing SQL query in SPSS syntax. Sent by: "SPSSX(r) Discussion" <[hidden email]> There are three files attached to the first post 1. PasteSQL_instructions.docx - word guide that explain how to do it 2. pasteSqlToSyntax.sbs - the script 3. PasteSQL.bmp - the Icon bmp You can download them all and use them. This tool doesn't require python plugin. The GET CAPTURE ODBC solution works, (Thanks !!!!) but as an old command, is it still supported by IBM ? (I'm afraid that in the future it might stop working) Thanks Ehud -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Script-tool-for-writing-SQL-query-in-SPSS-syntax-tp5713613p5713662.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Script tool for writing SQL query in SPSS syntax.
|
Administrator
|
In reply to this post by Ehud Levitin
Okay, I now see there are 3 links. They all run together in Nabble. Here they are separated:
http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/PasteSQL_instructions.docx http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/pasteSqlToSyntax.sbs http://spssx-discussion.1045642.n5.nabble.com/file/n5713613/PasteSQL.bmp HTH.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
In reply to this post by Rick Oliver-3
Hi Rick,
As David Wright-6 wrote, by using the GET CAPTURE command, you dont have to add the quotes and the + signs in the SQL query. you can just write it as it is, and it works ! The thing is, that this functionality is not written in the help or the command syntax reference, so it might Disappear one day ... Is there (or will be ) such an option in the new command ? |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
In reply to this post by Ehud Levitin
I'm not certain its a question of functionality as much as one of convenience and use. The GET CAPTURE does not require all the quotes and '+' symbols, you simply take your sql script & plug it in, making it possible to use the same sql script in SPSS, SQL server, DBViz, Oracle, etc. We use and share hundreds if not thousands of sql scripts, some of which are very lengthy (20-60 pages of code), I can easily just copy the sql script from Oracle or DBViz and paste it into a GET CAPTURE syntax. The first time I showed our programmers the GET DATA requirements of the '+ symbols, they all laughed and said no way, not only would it cause needless hours wasted inserting the symbols, but the ability to move the script from program to program would be lost. Hopefully, SPSS will continue to support the GET CAPTURE option, it not only is easier but it widens the audience of folks who use other sql programs. Without it, I would not have been successful in converting to SPSS among our programmers and users on campus. On Wed, Jun 13, 2012 at 9:25 AM, Rick Oliver wrote: GET CAPTURE is still supported but is deprecated in favor of GET DATA /TYPE=ODBC. What functionality is provided in GET CAPTURE that cannot be accomplished with GET DATA? Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] Phone: 312.893.4922 | T/L: 206-4922 From: Ehud Levitin <[hidden email]> To: [hidden email] Date: 06/13/2012 09:14 AM Subject: Re: Script tool for writing SQL query in SPSS syntax. Sent by: "SPSSX(r) Discussion" <[hidden email]> ___________________________________ There are three files attached to the first post 1. PasteSQL_instructions.docx - word guide that explain how to do it 2. pasteSqlToSyntax.sbs - the script 3. PasteSQL.bmp - the Icon bmp You can download them all and use them. This tool doesn't require python plugin. The GET CAPTURE ODBC solution works, (Thanks !!!!) but as an old command, is it still supported by IBM ? (I'm afraid that in the future it might stop working) Thanks Ehud -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Script-tool-for-writing-SQL-query-in-SPSS-syntax-tp5713613p5713662.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Script tool for writing SQL query in SPSS syntax.
|
|
In reply to this post by Bruce Weaver
Hi,
The simple menu-based program below converts an SQL string like this:
SELECT cities, rivers, roads FROM france WHERE region = "Côte d'Azûr"
....into this:
/SQL =
'SELECT cities, rivers, roads FROM france ' + 'WHERE region = "Côte d''Azûr" ' (the one aposthrophe in Côte d'Azûr is converted into two aposthrophes -I believe that's how SPSS wants it)
# -*- coding: cp1252 -*-
from Tkinter import * import textwrap import locale import Tkinter import tkMessageBox class SQL2SyntaxGUI(Frame):
def __init__(self, root, width=40):
self.root = root self.width = width self.root.wm_title("SQL to SPSS quoting tool") self.mainframe = Frame(root) self.mainframe.grid() self.makeTextWidget() self.makeCloseWidget() self.makeButtonWidget() def makeTextWidget(self): self.txt = Text(self.mainframe) self.txt.insert(INSERT, "< insert SQL code here >") self.txt.tag_add(SEL, "1.0", END) self.txt.grid() self.txt.focus_set() def makeCloseWidget(self):
self.button = Button(self.mainframe, text="Close", width=10, fg="red", command=self.close) self.button.grid(column=0, row=1, sticky="NSE") def makeButtonWidget(self):
button = Button(self.mainframe, text="SQL to SPS", width=10, command=self.sql2syntax) button.grid(column=0, row=1, sticky="NSW") def close(self):
self.root.destroy() def paste2clipboard(self, sql):
self.root.clipboard_clear() self.root.clipboard_append(sql) msg = "SPSSified SQL code pasted to Clipboard" tkMessageBox.showinfo(title = "Done!", message = msg) def sql2syntax(self):
sql = self.txt.get("1.0", END) wrapped = textwrap.wrap(sql.replace("'", "''"), self.width) wrapped = [" '" + w + " ' +" for w in wrapped] syntax = "/SQL =\n" + "\n".join(wrapped).rstrip(" +") #syntax = syntax.decode(locale.getdefaultlocale()[1]) self.paste2clipboard(syntax) self.txt.delete("1.0", END) if __name__ == "__main__":
#sql = """SELECT cities, rivers, roads FROM france WHERE region = "Côte d'Azûr\"""" root = Tk() sql2syntaxgui = SQL2SyntaxGUI(root) root.mainloop() Regards,
Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Re: Script tool for writing SQL query in SPSS syntax.
|
|
In reply to this post by wsu_wright
GET CAPTURE is still listed in the CSR,
although the documentation does not suggest that you can omit those annoying
quotes. However it has a documented limit of 3800 characters for
the SQL subcommand (that should probably be bytes, not characters).
While that's a lot, arbitrary SQL could certainly exceed it. The
custom dialog I mentioned in an earlier post can take care of the quoting
if necessary. While it does not have unquoting capability, it would
be easy to add such a facility.
Regards, Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: David Wright <[hidden email]> To: [hidden email] Date: 06/14/2012 03:46 AM Subject: Re: [SPSSX-L] Script tool for writing SQL query in SPSS syntax. Sent by: "SPSSX(r) Discussion" <[hidden email]> I'm not certain its a question of functionality as much as one of convenience and use. The GET CAPTURE does not require all the quotes and '+' symbols, you simply take your sql script & plug it in, making it possible to use the same sql script in SPSS, SQL server, DBViz, Oracle, etc. We use and share hundreds if not thousands of sql scripts, some of which are very lengthy (20-60 pages of code), I can easily just copy the sql script from Oracle or DBViz and paste it into a GET CAPTURE syntax. The first time I showed our programmers the GET DATA requirements of the '+ symbols, they all laughed and said no way, not only would it cause needless hours wasted inserting the symbols, but the ability to move the script from program to program would be lost. Hopefully, SPSS will continue to support the GET CAPTURE option, it not only is easier but it widens the audience of folks who use other sql programs. Without it, I would not have been successful in converting to SPSS among our programmers and users on campus. On Wed, Jun 13, 2012 at 9:25 AM, Rick Oliver wrote: GET CAPTURE is still supported but is deprecated in favor of GET DATA /TYPE=ODBC. What functionality is provided in GET CAPTURE that cannot be accomplished with GET DATA? Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] Phone: 312.893.4922 | T/L: 206-4922 From: Ehud Levitin <[hidden email]> To: [hidden email] Date: 06/13/2012 09:14 AM Subject: Re: Script tool for writing SQL query in SPSS syntax. Sent by: "SPSSX(r) Discussion" <[hidden email]> ___________________________________ There are three files attached to the first post 1. PasteSQL_instructions.docx - word guide that explain how to do it 2. pasteSqlToSyntax.sbs - the script 3. PasteSQL.bmp - the Icon bmp You can download them all and use them. This tool doesn't require python plugin. The GET CAPTURE ODBC solution works, (Thanks !!!!) but as an old command, is it still supported by IBM ? (I'm afraid that in the future it might stop working) Thanks Ehud -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Script-tool-for-writing-SQL-query-in-SPSS-syntax-tp5713613p5713662.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |