[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
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
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 YDATA1) * X=Row , Y=Columns * 2000: USERNAME=... PASSWORD=... USRNO=@USERNO BLANKLINE=CHAR(9):CHAR(9) SQLLINE=tsql -S CRM -U :USERNAME: -P :PASSWORD: 21 /tmp/output:USRNO: EOF SQLLINE-1=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/holding:USRNO: /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 PTEMP1,T=NULL THEN PTEMP1,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
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 YDATA1) * X=Row , Y=Columns * 2000: USERNAME=... PASSWORD=... USRNO=@USERNO BLANKLINE=CHAR(9):CHAR(9) SQLLINE=tsql -S CRM -U :USERNAME: -P :PASSWORD: 21 /tmp/output:USRNO: EOF SQLLINE-1=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/holding:USRNO: /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 PTEMP1,T=NULL THEN PTEMP1,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
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
