> You still can't use local variables in a EXECUTE IMMEDIATE SQL statement in a > compiled application? Is it really that difficult to implement?
alternatively you can use SQL EXECUTE to still be able to use local variables, and build your query dynamically sample: C_TEXT($sqlStmt) $sqlStmt:="" $sqlStmt:=$sqlStmt+"SELECT " $sqlStmt:=$sqlStmt+"cast( "+$col1+" as varchar) ," $sqlStmt:=$sqlStmt+"cast( “+$col2+" as varchar) " $sqlStmt:=$sqlStmt+"FROM "+$tablename+" " $sqlStmt:=$sqlStmt+"where "+$where"n+"; " SQL LOGIN(SQL_INTERNAL;"";"") SQL EXECUTE($sqlStmt;$col1;$col2) While (Not(SQL End selection)) SQL LOAD RECORD(SQL all records) End while SQL LOGOUT -------------------------------------------- > - Why isn't the use of 4D array variables in SQL queries supported? You could implode an array (sometimes known as join) to a delimited list As you build your sql statement SQL statements can be executed by SQL EXECUTE or EXECUTE IMMEDIATE SQL I included a sample Implode/Join IN syntax according to 4D http://doc.4d.com/4Dv16/4D/16/in-predicate.300-3201202.en.html <http://doc.4d.com/4Dv16/4D/16/in-predicate.300-3201202.en.html> SELECT * FROM ORDERS WHERE order_id IN (10000, 10001, 10003, 10005); // Method: Join // example: $return := Join (->$myarray;",") the Array myarray will return a string separated by comma C_TEXT($0) //joined array C_POINTER($1) //passed array C_TEXT($2) //value to delimit by C_LONGINT($iter) //iterate array C_TEXT($delim;$return) //presets $delim:=$2 $return:="" For ($iter;1;Size of array($1->)) //empty array will return nothing $return:=$return+$1->{$iter} If ($iter#Size of array($1->)) $return:=$return+$delim //add delim unless its the last record, if only 1 record, there will be no delim End if End for $0:=$return > Message: 1 > Date: Fri, 14 Apr 2017 08:29:56 -0600 > From: Bart Davis <[email protected]> > To: [email protected] > Subject: 4D SQL Implementation > Message-ID: <[email protected]> > Content-Type: text/plain; charset=us-ascii > > >> On Apr 14, 2017, at 2:35 AM, [email protected] wrote: >> >> >> Message: 3 >> Date: Thu, 13 Apr 2017 16:31:15 -0400 >> From: Jeffrey Kain <[email protected]> >> To: 4D iNug Technical <[email protected]> >> Subject: Re: How to create Object field via SQL >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset=us-ascii >> >> It's almost as if the SQL engine is a bolted-on afterthought to 4D that >> doesn't receive much love at all from the development team. > > I couldn't agree more with this statement "It's almost as if the SQL engine > is a bolted-on afterthought to 4D that doesn't receive much love at all from > the development team." > > - The fact that you can't use SQL in a trigger when a record is inserted, > updated or deleted via SQL is baffling. Every other SQL database seems to be > able to do it. > > - You still can't use local variables in a EXECUTE IMMEDIATE SQL statement > in a compiled application? Is it really that difficult to implement? > > - Could there be some internal semaphore/blocking code that prevents some > part of a SQL query in one process from executing (this includes ORDER BY) > when a SQL query in executing in a different process? In two different web > applications that have heavy concurrent use, progress bar windows start > appearing on the screen saying ORDER BY or LOADING DATA and as more processes > are initiated more windows appear the application grinds to a stop and must > be killed and restarted. These are simple queries with Order By on indexed > fields on small data sets. I replaced the SQL queries with the standard 4D > Query language and there are no more performance issues. On all heavily used > queries I have replaced SQL with 4D query language. > > - Why isn't the use of 4D array variables in SQL queries supported? > > Assuming $aRecordID is a populated Longint Array... > > Begin SQL > Select * from Table where ID in :$aRecordID > End SQL > > Bart > > thanks, Aaron -- Aaron Blazer Myriad Systems ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
