Re: [U2] TU.SQL.CONNECT
HI, TU.SQL.* are part of SB+/SBClient Utilities (Termulator Utilities) to interface SQL server via ODBC connection defined on the client. SBClient are the gateway between ODBC server request. On client, you define ODBC connection; like for excel or other. On server you have TU.SQL.* subrtoutine to call to interface it. TU.SQL.CONNECT : open the odbc relationship ; return a "odbcconn handle" TU.SQL.DISCONNECT : close it :-) TU.SQL.EXEC : submit a sql statement TU.SQL.READ : do a "fetch" from the last EXEC result set ; you get one sql result line into on dynamicarray TU.SQL.MAKEDICT : will create DICTionaries defn from a SQL TABLE You can found all details and samples in SBClient Programmer Reference from Rocket Software (https://docs.rocketsoftware.com/nxt/gateway.dll/RKB14/sbxa/622/client_prog.pdf chapter 6 ODBC connectivity) Very helpfull if you can't set SQL conn from U2Server side. manu > -Message d'origine- > De : u2-users-boun...@listserver.u2ug.org [mailto:u2-users- > boun...@listserver.u2ug.org] De la part de Israel, John R. > Envoyé : jeudi 22 août 2013 14:14 > À : U2 Users List > Objet : [U2] TU.SQL.CONNECT > > Does anyone have any experience using the TU.SQL.CONNECT command to > connect to a Microsoft Sequel database? > > I am not finding any on-line docs and the OLD book I am using is not very > helpful. > > > JRI > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] TU.SQL.CONNECT
Leftout FIRSTFIELD as an IN: variable - setup as below: 1200: CRMFIELDS="field1, field2, field3" CRMFILE="table1name inner join table2name on (field1 = field2)" CRMWHERE="where field1 = 'value' ORDER BY field2" SQLCMD="select ":CRMFIELDS:" from ":CRMFILE:" ":CRMWHERE FIRSTFIELD="field1" GOSUB 2000 Or you could just assign SQLCMD="select field1,field2,field3 from table1name inner join table2name on (field1 = field2) where field1 = 'value' ORDER BY field2" I just found it easier to break it up when debugging. The FIRSTFIELD is used to detect when to start pulling actual data - otherwise it will keep skipping Lines until it sees FIRSTFIELD as the first FIELD(line,char(9),1) George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Thursday, August 22, 2013 9:28 AM To: U2 Users List Subject: Re: [U2] TU.SQL.CONNECT Not exactly what you are asking for...but we use tsql (freetds - open source odbc for linux) Setup: we have a "/tmp" opened to F.TMP * *** * Subroutine Execute Query, Put results into YDATA Dynamic Array (ROW,COLUMN) * *** * * IN: SQLCMD contains the SQL command to execute on the MSSQL Server * OUT: YDATA contains a dynamic Array of the results (Headers are on YDATA<1>) * X=Row , Y=Columns * 2000: USERNAME="..." PASSWORD="..." USRNO=@USERNO BLANKLINE=CHAR(9):CHAR(9) SQLLINE="tsql -S CRM -U ":USERNAME:" -P ":PASSWORD:" 2>&1 > /tmp/output":USRNO:" <="use schema_name;" SQLLINE<-1>=SQLCMD SQLLINE<-1>="go" SQLLINE<-1>="bye" SQLLINE<-1>="EOF" * * The following was added to remove ^M's (from websites that allow the ENTER key *in Web Fields - it gets converted to a space * SQLLINE<-1>="cp /tmp/output":USRNO:" /tmp/holding":USRNO SQLLINE<-1>="perl -pe 's/\r\n/ /g' /tmp/output":USRNO SQLLINE<-1>="/bin/rm /tmp/holding":USRNO * WRITE SQLLINE ON F.TMP,"TSQLCMD":USRNO CMD1="/tmp/TSQLCMD":USRNO EXECUTE \SH -c "\:CMD1:\"\ CAPTURING ERRORS IF ERRORS#"" THEN CALL *EMAILFILE(SQLLINE:CHAR(254):CHAR(254):ERRORS,"programmer1@localhost","[MSSQL] (Program-Name) Errors Executing","*") DELETE F.TMP,"TSQLCMD":USRNO * OPENSEQ "/tmp/output":USRNO TO F.MSSQLIN ELSE STOP "Can't Open MSSQL file" STARTCHECK=0; NUMB=0 * YDATA="" LOOP READSEQ PLINE FROM F.MSSQLIN ELSE EXIT IF PLINE=BLANKLINE THEN CONTINUE IF STARTCHECK=0 THEN IF PLINE[1,LEN(FIRSTFIELD)]=FIRSTFIELD THEN STARTCHECK=1 YDATA=CHANGE(PLINE,CHAR(9),CHAR(253)) END CONTINUE END IF PLINE[1,2]="1>" THEN CONTINUE PTEMP=CHANGE(PLINE,CHAR(9),CHAR(253)) FOR T=1 TO DCOUNT(PTEMP,CHAR(253)) IF PTEMP<1,T>="NULL" THEN PTEMP<1,T>="" NEXT T YDATA<-1>=PTEMP REPEAT * CLOSESEQ F.MSSQLIN DELETE F.TMP,"output":USRNO RETURN -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Thursday, August 22, 2013 8:14 AM To: U2 Users List Subject: [U2] TU.SQL.CONNECT Does anyone have any experience using the TU.SQL.CONNECT command to connect to a Microsoft Sequel database? I am not finding any on-line docs and the OLD book I am using is not very helpful. JRI ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] TU.SQL.CONNECT
Not exactly what you are asking for...but we use tsql (freetds - open source odbc for linux) Setup: we have a "/tmp" opened to F.TMP * *** * Subroutine Execute Query, Put results into YDATA Dynamic Array (ROW,COLUMN) * *** * * IN: SQLCMD contains the SQL command to execute on the MSSQL Server * OUT: YDATA contains a dynamic Array of the results (Headers are on YDATA<1>) * X=Row , Y=Columns * 2000: USERNAME="..." PASSWORD="..." USRNO=@USERNO BLANKLINE=CHAR(9):CHAR(9) SQLLINE="tsql -S CRM -U ":USERNAME:" -P ":PASSWORD:" 2>&1 > /tmp/output":USRNO:" <="use schema_name;" SQLLINE<-1>=SQLCMD SQLLINE<-1>="go" SQLLINE<-1>="bye" SQLLINE<-1>="EOF" * * The following was added to remove ^M's (from websites that allow the ENTER key *in Web Fields - it gets converted to a space * SQLLINE<-1>="cp /tmp/output":USRNO:" /tmp/holding":USRNO SQLLINE<-1>="perl -pe 's/\r\n/ /g' /tmp/output":USRNO SQLLINE<-1>="/bin/rm /tmp/holding":USRNO * WRITE SQLLINE ON F.TMP,"TSQLCMD":USRNO CMD1="/tmp/TSQLCMD":USRNO EXECUTE \SH -c "\:CMD1:\"\ CAPTURING ERRORS IF ERRORS#"" THEN CALL *EMAILFILE(SQLLINE:CHAR(254):CHAR(254):ERRORS,"programmer1@localhost","[MSSQL] (Program-Name) Errors Executing","*") DELETE F.TMP,"TSQLCMD":USRNO * OPENSEQ "/tmp/output":USRNO TO F.MSSQLIN ELSE STOP "Can't Open MSSQL file" STARTCHECK=0; NUMB=0 * YDATA="" LOOP READSEQ PLINE FROM F.MSSQLIN ELSE EXIT IF PLINE=BLANKLINE THEN CONTINUE IF STARTCHECK=0 THEN IF PLINE[1,LEN(FIRSTFIELD)]=FIRSTFIELD THEN STARTCHECK=1 YDATA=CHANGE(PLINE,CHAR(9),CHAR(253)) END CONTINUE END IF PLINE[1,2]="1>" THEN CONTINUE PTEMP=CHANGE(PLINE,CHAR(9),CHAR(253)) FOR T=1 TO DCOUNT(PTEMP,CHAR(253)) IF PTEMP<1,T>="NULL" THEN PTEMP<1,T>="" NEXT T YDATA<-1>=PTEMP REPEAT * CLOSESEQ F.MSSQLIN DELETE F.TMP,"output":USRNO RETURN -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Thursday, August 22, 2013 8:14 AM To: U2 Users List Subject: [U2] TU.SQL.CONNECT Does anyone have any experience using the TU.SQL.CONNECT command to connect to a Microsoft Sequel database? I am not finding any on-line docs and the OLD book I am using is not very helpful. JRI ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] TU.SQL.CONNECT
This night give you a starting point: Use CONNECT to connect to a local or remote SQL server, such| |as another UniVerse system or an ORACLE, SYBASE, or INFORMIX| |system. | | | | SYNTAX | | | | CONNECT data.source [option setting [option setting...]] | | | | QUALIFIERS | | | | data.source The name of the data source to which you want to | | connect. The data source must be defined in the | | uvodbc.config file. If you do not enter the name | | of a data source, CONNECT lists all data source | in the uvodbc.config file. | | | | optionOne of the following options to control the input | | format or output display:| | | | BLOCKPREFIX | | INVERT UVOUT | | MVDISPLAYVERBOSE | | NULL WIDTH | | | | Specify any option by typing the word or its first | | letter. Each option must be followed by setting. | | | | setting The new setting for the option. | | | |The following sections describe each option and its possible| |settings in detail. | | | | BLOCK OPTION his option defines how input statements will be | |terminated. setting is one of the following: | | | | ONEnables block mode. In this mode you can enter| | a series of SQL statements, ending each with a| | semicolon (;). To terminate the block of SQL | | statements, press Return immediately after an| | "SQL+" prompt. | | | | OFF (Default) Disables block mode. In this mode if| | you type a semicolon at the end of a line of| | input, the SQL Client terminates your input and| | sends it to the data source. | | | | string | | Enables block mode (see ON). string must be | | from one to four characters. To terminate the | | block of SQL statements, enter string | | immediately after an "SQL+" prompt. INVERT OPTION| | | | This option lets you control case inversion for | | alphabetic characters you type while CONNECT is| | running. setting is one of the following:| | | | ON Inverts the case of all alphabetic characters | |you type--that is, lowercase letters change to | |uppercase, and uppercase letters change to | |lowercase. This is equivalent to setting PTERM | |CASE parameters to INVERT and
[U2] TU.SQL.CONNECT
Does anyone have any experience using the TU.SQL.CONNECT command to connect to a Microsoft Sequel database? I am not finding any on-line docs and the OLD book I am using is not very helpful. JRI ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
