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: [email protected] [mailto:[email protected]] 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:" <<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 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: [email protected] [mailto:[email protected]] 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 [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
