Re: error 9922 o SQL Execute? what does that mean?

fixed in 15.3 HF1 and 16 R2. But it looks like there are other ODBC errors with data getting truncated to 255 characters by some drivers, etc. Is it possible that the data you’re trying to INSERT is causing the SQL statement to exceed some ODBC limitation of the driver you’re using? Randy

error 9922 o SQL Execute? what does that mean?

4d iDoes anyone here know what error 9922 means (not -9922)? this is 4D v17.5, and I am doing a SQL EXECUTE(“INSERT….“), via a ODBC connection. Can’t find that error code anywhere, not on 4D docs and not even on 4D Today. The error only comes up some times, for some statements. I could not

Re: Looking for 3rd party SQL query tool

we use valentina valentinadb.com it's got other tools built-in, too, like a report editor, etc. it's x-platform, in case that matters. On Wed, Apr 1, 2020 at 4:28 AM Paul Dennis via 4D_Tech <4d_tech@lists.4d.com> wrote: > I use heidi very easy and reliable. > > https://www.heidisql.com/ > > > Pau

Re: Looking for 3rd party SQL query tool

I use heidi very easy and reliable. https://www.heidisql.com/ Paul -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options:

Re: Looking for 3rd party SQL query tool

Tony, You can perform ad hoc sql queries directly from another copy of 4D. There’s probably an old tech note that has an example database. I know I modified an old db recently to just this thing. I think I started here “4D v11 SQL Assimilator" https://kb.4d.com/assetid=75182 or if you

RE: Looking for 3rd party SQL query tool

ech@lists.4d.com> Cc: Tony Ringsmuth Subject: Looking for 3rd party SQL query tool Can anyone recommend a good 3rd party SQL query tool, to connect to 4D via ODBC, and perform general SQL queries? I used to use “SqlDbx” free version, but the free version is 32 bit only.  Their 64 bit vers

Re: Looking for 3rd party SQL query tool

hrieb Tony Ringsmuth via 4D_Tech <4d_tech@lists.4d.com>: > > Can anyone recommend a good 3rd party SQL query tool, to connect to 4D via ODBC, and perform general SQL queries? > > > > I used to use “SqlDbx” free version, but the free version is 32

Re: Looking for 3rd party SQL query tool

uth via 4D_Tech > <4d_tech@lists.4d.com>: > > Can anyone recommend a good 3rd party SQL query tool, to connect to 4D via > ODBC, and perform general SQL queries? > > > > I used to use “SqlDbx” free version, but the free version is 32 bit only. >

Looking for 3rd party SQL query tool

Can anyone recommend a good 3rd party SQL query tool, to connect to 4D via ODBC, and perform general SQL queries? I used to use “SqlDbx” free version, but the free version is 32 bit only.  Their 64 bit version is a bit spendy. Thanks, -- Tony Ringsmuth Business Brothers Inc. 763-420

Re: Options for Connecting to MS SQL Server from OS X 10.14+

Since you are on a Mac you might need driver. Actual technology has the best Regards Chuck On Wed, Dec 18, 2019 at 4:37 AM Narinder Chandi via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Hi all. We have a requirement to connect to MS SQL Server databases hosted > on AWS from 4D v17

Options for Connecting to MS SQL Server from OS X 10.14+

Hi all. We have a requirement to connect to MS SQL Server databases hosted on AWS from 4D v17.3 on Mac OS 10.14+. The use case scenario is likely to be that: * only a single user will need to connect, either via 4D Client or from 4D standalone - tbc * we may need to ingest large amounts of data

Re: How to Modify a column using SQL

Thanks for the info John. I'm not planning on going to v18 until it is mature. I'm a little superstitious for even numbered versions! Cheers - Jim Labos - infobase -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html *

Re: How to Modify a column using SQL

I cannot alter the > field type etc.. That part of SQL does not work in 4D but I was wondering if > that had changed for v17 or even will for v18. > > From the looks of things it doesn't look like it has or will.

Re: How to Modify a column using SQL

Hi Chuck, No I meant the field definition as in the original post. I cannot alter the field type etc.. That part of SQL does not work in 4D but I was wondering if that had changed for v17 or even will for v18. From the looks of things it doesn't look like it has or will. Cheers

Re: How to Modify a column using SQL

Do you mean the name or the data. If data it is an update command it name of column it would be an alter command Regards Chuck Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (61

Re: How to Modify a column using SQL

Does anyone know if this has changed since 2013? I couldn't find anything but I could be missing something. Thanks - Jim Labos - infobase -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet

Re: Create SQL DB ERD from 4D Export Structure XML File?

I tried Miyako’s Structure to SVG tool and generated a fine structure.svg file. Now I’m trying to find a freeware tool to edit it. I’m trying Inkscape right now, but it’s very pokey (lots of “Not Responding” when selecting objects etc). Mostly unusable. Any recommendations on free SVG editing t

Re: Create SQL DB ERD from 4D Export Structure XML File?

I think he has already posted an explanation to this thread, But the reason is that the colour by default (displayed grey in structure editor) is rgb(0,0,0) So you need to code exceptions for tables that do not have a custom colour defined. > 2019/11/26 8:42、Cannon Smith via 4D_Tech <4d_tech@lis

Re: Create SQL DB ERD from 4D Export Structure XML File?

Hi Tom, As for Alexander’s code, I noticed the following sometime ago. Maybe something this simple is causing the blackness as well? > Just a heads up on a bit of code that won’t work in countries that use a > decimal place instead of a comma. In the method xml_atts2ob, lines 50, 54, > 58, and

Re: Create SQL DB ERD from 4D Export Structure XML File?

Thank you for correcting me Miyako! I should have dug deeper. I’ll check it out. Tom Benedict > On Nov 25, 2019, at 08:02, Keisuke Miyako via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > For reference: > > I do not use native XSLT commands, > the component has a CLI xsltproc, > so the remarks

Re: Create SQL DB ERD from 4D Export Structure XML File?

For reference: I do not use native XSLT commands, the component has a CLI xsltproc, so the remarks “It is now deprecated” is incorrect in this instance. https://github.com/miyako/4d-utility-structure-to-svg-converter-v2 2019/11/25 22:00、Tom Benedict via 4D_Tech <4d_tech@lists.4d.com

Re: Create SQL DB ERD from 4D Export Structure XML File?

Am 25.11.2019 um 14:00 schrieb Tom Benedict via 4D_Tech <4d_tech@lists.4d.com>: > > Alexander’s code ended with a number of black tables. Hi Tom, sorry for that, I take the color commands from the xml structure definitions. If you like, you could send me the XML structure and I check if I can co

Re: Create SQL DB ERD from 4D Export Structure XML File?

> On Nov 24, 2019, at 11:54 AM, Tom Benedict via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > To that end, I’m investigating whether there is a way to use the XML file > that 4D creates via the EXPORT STRUCTURE command in a non-4D tool (like SQL > Server Management Stu

Re: Create SQL DB ERD from 4D Export Structure XML File?

nedict via 4D_Tech <4d_tech@lists.4d.com >> <mailto:4d_tech@lists.4d.com>> a écrit : >> >> [...] >> To that end, I’m investigating whether there is a way to use the XML file >> that 4D creates via the EXPORT STRUCTURE command in a non-4D tool (like SQL

Re: Create SQL DB ERD from 4D Export Structure XML File?

> Le 24 nov. 2019 à 18:54, Tom Benedict via 4D_Tech <4d_tech@lists.4d.com > <mailto:4d_tech@lists.4d.com>> a écrit : > > [...] > To that end, I’m investigating whether there is a way to use the XML file > that 4D creates via the EXPORT STRUCTURE command in a n

Re: Create SQL DB ERD from 4D Export Structure XML File?

Am 24.11.2019 um 18:54 schrieb Tom Benedict via 4D_Tech <4d_tech@lists.4d.com>: > > I have a 4D method which generates a SQL DDL, but it doesn’t include > relations. So now I’m looking at the XML structure file as a possibility. Look at my Blog http://blog.heintz.net/sql-s

Create SQL DB ERD from 4D Export Structure XML File?

SnagIt process which is fragile. I’m losing interest in that process and am hoping for something that is repeatable. To that end, I’m investigating whether there is a way to use the XML file that 4D creates via the EXPORT STRUCTURE command in a non-4D tool (like SQL Server Management Studio, MySQL

Re: v15 how do I feed a SQL query result to a listbox?

> Le 8 nov. 2019 à 02:30, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > > Thanks! Hi Chip, I'd add 2 things… 1/ the INTO LISTBOX target variable must not be a local one 2/ after the SQL populates the listbox, resulting headers/columns names are

Re: v15 how do I feed a SQL query result to a listbox?

Thanks! > > > See the bottom of this page: > https://doc.4d.com/4Dv16/4D/16.6/Managing-List-Box-Objects.300-4445342.en.html > > On 11/7/19, 3:11 PM, "4D_Tech on behalf of Chip Scheide via 4D_Tech" > <4d_tech-boun...@lists.4d.com on behalf of 4d_tech@lists.4d.com> > wrote: > > I believe

Re: v15 how do I feed a SQL query result to a listbox?

See the bottom of this page: https://doc.4d.com/4Dv16/4D/16.6/Managing-List-Box-Objects.300-4445342.en.html On 11/7/19, 3:11 PM, "4D_Tech on behalf of Chip Scheide via 4D_Tech" <4d_tech-boun...@lists.4d.com on behalf of 4d_tech@lists.4d.com> wrote: I believe that this is possible. I a

v15 how do I feed a SQL query result to a listbox?

I believe that this is possible. I am not sure how to go about doing such. Any help? thanks Chip We have done so much, with so little, for so long; We are now qualified to anything with nothing - unknown ** 4D Internet Users Gr

Re: v13+ SQL access other system

Chip, What you are trying to do is all possible and I have done it with many different SQL databases over the years with various SQL backends such as DEC RDB, Sharebase, Terradata, Oracle, MS SQL, Firebird, MySQL, SQLite and PostgreSQL. Some of these do not exist anymore the main ones I

Re: v13+ SQL access other system

Back in 4Dv11 days, I was asked to connect to a SQL database with 4D. With 4D, I was able to connect to a test copy of the SQL system. I found and extracted the list of the SQL tables. And, for each table, I extracted the field map. I then wrote 4D code to do sample data extracts for each table

Re: v13+ SQL access other system

Thanks Tom! the end goal is a single report (this would be done in 4D), summarizing information across all 4 commercial applications. What I know about the internal workings of any of the 4 sql programs = 0 From the description - there is no official outline of the report yet - each program

Re: v13+ SQL access other system

matter of writing queries (SELECT) and putting the returned arrays into records or write to text files. It’s possible to SYNCHRONIZE 4D tables with SQL tables, but I have no experience with that feature. Is the goal to generate the reports in 4D? Or in SQL? HTH, Tom Benedict > On Oc

v13+ SQL access other system

My use of SQL is very limited... I have been asked to try to gather data from 4 separate commercial databases (assumed to be running some SQL compliant system of unknown origin - i.e. mysql, ms sql server etc) and try to coordinate the data and produce a report summarizing the combined data

Re: 4D ODBC, MS SQL and Alpha Fields

Hello Paul, For years we have been doing it the other way round with very few problems. We write to SQL from 4D using ODBC. Then you have full control of what you send both on the record and field level. You are welcome to contact me off list if you want some sample code. HTH David Samson

Re: 4D ODBC, MS SQL and Alpha Fields

I second Chuck’s comment about 4D -> SQL Server via ODBC being much easier, stable etc than SQL Server -> 4D via ODBC. I know that probably doesn’t help, but if you have the option to change your architecture it would make life much easier. Tom Benedict > On Oct 3, 2019, at 10:22

Re: 4D ODBC, MS SQL and Alpha Fields

can we see the entire sql statement you are using. What version of 4D? Lastly, If you can I have always found it easier to go from 4D to sql server. If you are on PC you already have odbc on PC if on mac you would have to buy odbc driver for sql server. I always use the one from actual

4D ODBC, MS SQL and Alpha Fields

We pull data from 4D into MS SQL using the 4D ODBC Driver. On occasion, we have a problem with some Alpha fields where only the first 2 characters are pulled into SQL. In MS SQL, the associated field has been configure to use varchar(255) {or whatever the field length is for the 4D field

Re: QUERY WITH ARRAY - SQL Equivalent?

> Le 18 sept. 2019 à 18:43, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > [...] > ID is a reserved word in sql so if that’s the name of your field you need to > escape it (I use brackets in 4D). I don't like having to use brackets, I always ch

Re: QUERY WITH ARRAY - SQL Equivalent?

<https://www.facebook.com/ArgusProductions/> Because of the SQL gotcha in v12 I always have a case of statement to run 4D queries instead. For something like what is described, I send the query to another process and return the array of record ID's. Allows for the query but doesn&#

RE: QUERY WITH ARRAY - SQL Equivalent?

tember 18, 2019 10:06 AM To: 4DTechList Tech <4d_tech@lists.4d.com> Cc: Chuck Miller Subject: Re: QUERY WITH ARRAY - SQL Equivalent? Here goes C_TEXT($INCLUDED_txt) $INCLUDED_txt:=“(“+String(al_Personnel_ID{1}) for($LOOPL;2;size of array(al_Personnel_ID)) $INCLUDED_txt:= $INCLUDED_tx

RE: QUERY WITH ARRAY - SQL Equivalent?

h <4d_tech-boun...@lists.4d.com> On Behalf Of Jeffrey Kain via 4D_Tech Sent: Wednesday, September 18, 2019 9:44 AM To: 4D iNug Technical <4d_tech@lists.4d.com> Cc: Jeffrey Kain Subject: Re: QUERY WITH ARRAY - SQL Equivalent? SELECT ... FROM Table WHERE [ID] in ( comma separated list of I

Re: QUERY WITH ARRAY - SQL Equivalent?

Here goes C_TEXT($INCLUDED_txt) $INCLUDED_txt:=“(“+String(al_Personnel_ID{1}) for($LOOPL;2;size of array(al_Personnel_ID)) $INCLUDED_txt:= $INCLUDED_txt+”,”+String(al_Personnel_ID{LOOPL}) end for $INCLUDED_txt:=$INCLUDED_txt+”)" begin sql select [Personnel].[field],... from [Personnel]

Re: QUERY WITH ARRAY - SQL Equivalent?

SELECT ... FROM Table WHERE [ID] in ( comma separated list of IDs ) ID is a reserved word in sql so if that’s the name of your field you need to escape it (I use brackets in 4D). ** 4D Internet Users Group (4D iNUG) Archive

Re: QUERY WITH ARRAY - SQL Equivalent?

repeat set query destination(into set;[table];"set name") -- I think that is the syntax do your query with array - result is in the set until - whatever termination condition or copy named selection([table[;"selection name") do queries use named selection("sele

QUERY WITH ARRAY - SQL Equivalent?

Hello SQL Afficionados! I need to get a selection of records, based upon an array of ID numbers. (What I want is to end up the arrays... not the selection of records) e.g. QUERY WITH ARRAY([Personnel]Personnel_ID;al_Personnel_ID) The above works just great! "Except", that it c

Re: SQL Connection to External SQL DB

15, there has not > been an issue with the SQL connection. > > I suspect it could be some GPOs that were pushed down….so the subsequent > investigation are narrowing down to that. > > Regards, > Ronnie > Tarawerkz > > > > >> On 16 Sep 2019, at 2:

Re: SQL Connection to External SQL DB

Hi Tom, The current 4D version at the client’s site is v15. Through the various versions of upgrade from v11 to 13 to 15, there has not been an issue with the SQL connection. I suspect it could be some GPOs that were pushed down….so the subsequent investigation are narrowing down to that

Re: SQL Connection to External SQL DB

version of TLS your app uses: http://kb.4d.com/assetid=76175 <http://kb.4d.com/assetid=76175> Tom Benedict > On Sep 15, 2019, at 22:04, Tarawerkz via 4D_Tech <4d_tech@lists.4d.com> wrote: > > Hi All, > > Need some help on a SQL connection issue…. > > A script that h

SQL Connection to External SQL DB

Hi All, Need some help on a SQL connection issue…. A script that has been to access an SQL database (been deployed for years) has suddenly stopped working and threw up the following error : Error - [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error In the cause

Re: Confirmation for SQL Execute Immediate

Thanks Keisuke! This make things so much more manageable. In the docs I interpreted the comment to mean remote 4D as well and got concerned. I really appreciate your insight and help here. Best regards, Bill ** 4D Internet

Re: Confirmation for SQL Execute Immediate

Hello, I don't think the statement (that "execute immediate" should only be used with the internal SQL db) was ever true. the only restrictions that I know of are: no local variables with EXECUTE IMMEDIATE. https://doc.4d.com/4Dv17R5/4D/17-R5/Overview-of-SQL-Commands.300-4128

Confirmation for SQL Execute Immediate

Hi Folks, I am using v14.6 and setting up SQL access between two databases. Would like to use variables that are complete SQL statements, i.e., SELECT Col From Table WHERE condition INTO :var. The only place we can use INTO is Begin SQL/End SQL. The SQL Login command passes the * so I can use

RE: Thread_Safe SQL

> OK, so the online documentation is wrong. It seems that way - I sent a message to the doc center team, thanks for letting us know. -Tim ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Option

Re: Thread_Safe SQL

OK, so the online documentation is wrong. https://docs.4d.com/4Dv17/4D/17.2/Preemptive-4D-processes.300-4386982.en.html Thanks. > Date: Tue, 6 Aug 2019 18:02:54 + > From: Timothy Penner mailto:tpen...@4d.com>> > Hi Richard, > > Beginning with 4D v17 R4, Begin

RE: Thread_Safe SQL

Hi Richard, Beginning with 4D v17 R4, Begin SQL, End SQL, and the associated SQL statements used with an internal database are now preemptive compliant. https://blog.4d.com/begin-end-sql-in-preemptive-mode/ -Tim ** 4D

Thread_Safe SQL

According to the v17.2 docs: > All SQL statements are thread-safe, except for REPLICATE > <https://livedoc.4d.com/4D-SQL-Reference-17/SQL-Commands/REPLICATE.300-3786790.en.html> > or SYNCHRONIZE > <https://livedoc.4d.com/4D-SQL-Reference-17/SQL-Commands/SYNCHRONIZ

RE: SQL - renaming a field

Good point - I already simply forgot about trying to rename. In concept it would not be an issue as I was only going to rename a field named 'Unused' But there were other considerations to renaming that occurred to me after I posted. in sql - if rename works, I do not see a way to re

Re: SQL - renaming a field

Yup. Actually, many flavours of SQL support table renaming or table duplication with additional keywords on ALTER TABLE since, at least in SQL92 AFAIK it wasn't supported as part of the DDL. For example, the last time I used MySQL with the Sequel Pro GUI tool, It has a table duplication

RE: SQL - renaming a field

repository.org>; 4D iNug Technical <4d_tech@lists.4d.com> Subject: Re: SQL - renaming a field OK Thanks both I guess rename is only possible as Drop + Add Chip ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d

Re: SQL - renaming a field

OK Thanks both I guess rename is only possible as Drop + Add Chip On Fri, 26 Jul 2019 11:42:49 -0700, Tom Benedict wrote: > Maybe 4D’s SQL implementation doesn’t support the RENAME keyword? > > I don’t see it in the docs.. > https://doc.4d.com/4Dv16/4D/16/ALTER-TABLE.300-320

Re: SQL - renaming a field

Chip, My initial observations are: * I think [ ] around the table name are not required (that's not part of the SQL standard) * it would seem that 4D's implementation of Alter table doesn't support "rename" Regards, Narinder Chandi, ToolBox Systems Ltd. -- -O

Re: SQL - renaming a field

Maybe 4D’s SQL implementation doesn’t support the RENAME keyword? I don’t see it in the docs.. https://doc.4d.com/4Dv16/4D/16/ALTER-TABLE.300-3201314.en.html <https://doc.4d.com/4Dv16/4D/16/ALTER-TABLE.300-3201314.en.html> Tom Benedict > On Jul 26, 2019, at 11:30, Chip Scheide vi

SQL - renaming a field

I'm trying to use SQL to rename an existing field $Sql:="Alter table ["+$Table_Name+"] Rename "+"Unused"+" to "+$Field_Name+";" When executed I get a parsing error at 'Rename'. can someone better versed either correct my above

Re: Error -1 using SQL.

one > of the hidden sql tables > > Array longint(constraintids_al;0) > Array longint(tablenumbers_al;0) > > Begin sql > Select constraints_id, table_id > From _user_constraints > Where constraint_type =‘P’ > Into > :constraintids_al, > :tablenumbers_al; >

Re: Error -1 using SQL.

Thanks On Thu, 25 Jul 2019 14:09:46 -0600, Guy Algot wrote: > Chip, I just noticed recently that when the SQL option is off on the > server and I try to connect with SQL LOGIN that I get an error -1 vs > a 9918. If you call the following in the IN ERR CALL method ; > > GET L

Re: Error -1 using SQL.

Chip, I just noticed recently that when the SQL option is off on the server and I try to connect with SQL LOGIN that I get an error -1 vs a 9918. If you call the following in the IN ERR CALL method ; GET LAST ERROR STACK(codesArray;intCompArray;textArray) you will see the error you are

Re: Error -1 using SQL.

You can write the code to see if there is already a primary key and not do the alter if there is It is one of the hidden sql tables Array longint(constraintids_al;0) Array longint(tablenumbers_al;0) Begin sql Select constraints_id, table_id From _user_constraints Where constraint_type =‘P

Error -1 using SQL.

I am working up a method to set the primary key property on a field. I am using SQL to do this, as it seems the only way. I have working code, but - I noticed if I try to apply the primary key property a second time I get a runtime error either 43 or 44 with the message : Failed to execute

Re: How can I update a 4D record from SQL Server, over ODBC?

12:12, dc5464 via 4D_Tech <4d_tech@lists.4d.com> wrote: > > I've got a 4D database running in 4D Server, with 4D Sql Server running. On > another Windows machine, I've got an an ODBC data source set up for it. On > that 2nd machine, in SQL Server Management Studio,

How can I update a 4D record from SQL Server, over ODBC?

I've got a 4D database running in 4D Server, with 4D Sql Server running. On another Windows machine, I've got an an ODBC data source set up for it. On that 2nd machine, in SQL Server Management Studio, I've created a Linked Server using that ODBC data source. I can successfully q

Re: information needed using sql on a database called smsdb.dat

Hi Ernie, If you haven’t already, I would start by using Microsoft SQL Server Management Studio (SSMS) to access whatever details you can of the database. There may be documentation or ERD diagrams available there. SQ Query Analyzer, part of SSMS, is a great tool for writing queries. Get your

information needed using sql on a database called smsdb.dat

Hi All, using 4D v17.2 pc. I need to have some basic knowledge on accessing a database used in the supermarket business called: LOC Store Management Suit. It’s running on MS server 2008 and MS sql. the program that is using it : storeman; SMS Pro V340. Only need to ‘read’ from the datafile

sql issue

Hi All, I am trying to build a sql statement see below ARRAY TEXT($INVIDs_atxt;0) Begin SQL select [LSS_Inventory].[LSS_InventoryId_s] from [LSS_Inventory] , [LSS_Inspection] where [LSS_Inspection].[LSS_CondFoundationCurr_s] = '2' and [LSS_Inspection].[LSS_InventoryId_s] = [LSS

Re: sql assistance

OK here is another related question has anyone used the CASE inside 4D SQL in the context of my previous post Regards Chuck On Thu, Mar 7, 2019 at 12:49 PM Charles Miller wrote: > > Hi All > > Given the following > Table_1 > Table_1Key > > Table_2 > Table_2Key

sql assistance

Hi All Given the following Table_1 Table_1Key Table_2 Table_2Key Table_1Key Table_2Date Table_3 Table_3Key Table_2Key Table3_Criteria I am trying to figure out how to write a sql statment that would get the table_1Keys where Table3_Criteria exists for max(Table_2Date) I am pretty sure this

RE: 4D Selection from SQL Query

Miyako Many thanks again! Works like a charm! Took a couple of passes to see the "Double Underline" in the virtual field: __ROW_ID I'm going to take a W.A.G. that this is what QUERY BY SQL command is doing behind the scenes. Not sure how I'm going to use this, but I th

RE: 4D Selection from SQL Query

Miyako, Many thanks! Randy Engle -Original Message- From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Keisuke Miyako via 4D_Tech Sent: Tuesday, February 5, 2019 4:30 PM To: 4D iNug Technical <4d_tech@lists.4d.com> Cc: Keisuke Miyako Subject: Re: 4D Selection fr

Re: 4D Selection from SQL Query

alternatively, I guess we can SELECT __ROW_ID == record number (not all tables have a primary key). https://doc.4d.com/4Dv17/4D/17/Replication-via-SQL.300-3786654.en.html https://doc.4d.com/4Dv17/4D/17/CREATE-SELECTION-FROM-ARRAY.301-3729926.en.html 2019/02/06 3:31、Randy Engle via 4D_Tech

RE: 4D Selection from SQL Query

lly need it, it would be a two step process. Step one Begin sql select primary_key form table where (some criteria) into :$primaryKeyArray end sql query with array([table]primary_key;$primaryKeyArray) Don't know why you would do this except you could build sql where on the fly BTW not cre

Re: 4D Selection from SQL Query

It is not possible. If you really need it, it would be a two step process. Step one Begin sql select primary_key form table where (some criteria) into :$primaryKeyArray end sql query with array([table]primary_key;$primaryKeyArray) Don't know why you would do this except you could build sql

4D Selection from SQL Query

For you SQL aficionados I know that I can get a "selection" of 4D records using QUERY BY SQL However as a point of curiosity and interest, is it reasonably easy/possible to get a selection of 4D records when using the other SQL commands e.g. Begin SQL-End SQL SQL EXECUTE Or, am I p.

Re: SQL query for the current date

Thanks for the idea... very clever. Unfortunately 4D still won't do an indexed query in this case like it does when passed a literal. > On Dec 3, 2018, at 7:49 PM, Keisuke Miyako via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > SQL functions such as CURRENT_DATE() seem

Re: SQL query for the current date

I would hazard a guess that the CURRENT_DATE() function is called for every record (row) in the table. to verify: create a project method "CD" published for SQL: $0:=Current date place a break point. --- then do ARRAY DATE($ad;0) Begin SQL SELECT Field_2 from Table_1 WHERE {

SQL query for the current date

I'm trying to query 4D Server via the SQL engine from another application, and it seems like if you use CURRENT_DATE() or CURDATE(), the query is always sequential even if an index is available. Example: SELECT InvoiceID from Invoices WHERE CreatedDate=CURRENT_DATE() ... does a seque

Re: V15 and SQL

4d.com/4Dv15/4D/15/ALTER-TABLE-DISABLE-TRIGGERS.300-2048419.en.html > http://doc.4d.com/4Dv15/4D/15/ALTER-DATABASE-DISABLE-TRIGGERS.300-2048403.en.html > >> 2018/11/15 8:25、Robert McKeever via 4D_Tech <4d_tech@lists.4d.com>のメール: >> a 4D server (Mac) is receiving new records

Re: SQL TRIM

Also remember to add limit 1 to command. If you run against a real table that has 100 rows the sql will run 100 times Regards Chuck On Thu, Nov 15, 2018 at 10:23 AM Koen Van Hooreweghe via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Hi Jim, > > You are missing the 'from'

Re: V15 and SQL

のメール: > a 4D server (Mac) is receiving new records in a table via SQL. That is fine > EXCEPT that > ((Trigger event=On Saving New Record Event) | (Trigger event=On Saving > Existing Record Event)) > never triggers. I need this to happen so other related tables can be updated > a

Re: SQL TRIM

Hi Jim, You are missing the 'from' clause with a table. 4D does not support sql queries without table. Eg in Oracle I would write: select trim(both from :vtext) from dual into :vtext; But it seems 4D sql does not support the dual phantom table either. Koen > Op 15 nov. 2018, o

SQL TRIM

I am trying to learn to use SQL in 4D. I wrote the code below to try a test using the TRIM function. I have rewritten this several times but it throws a general parsing error every time. C_TEXT(vText) vText:=" sql Text! " Begin SQL SELECT TRIM(BOTH FROM :VTEXT) INTO :v

V15 and SQL

a 4D server (Mac) is receiving new records in a table via SQL. That is fine EXCEPT that ((Trigger event=On Saving New Record Event) | (Trigger event=On Saving Existing Record Event)) never triggers. I need this to happen so other related tables can be updated accordingly. Any suggestions

Re: Tableau connecting to SQL server of 4D

Hi Dan. We have a few customers that are using Tableau and they have been using the Web Data Connector. Here is the link to the documentation: http://tableau.github.io/webdataconnector/docs/ I tested this out this week and it was quite easy to expose data from 4D and ingest it into Tableau wit

Re: Tableau connecting to SQL server of 4D

I know this is an old post, but did you find a way to connect tableau? I am using the latest Tableau version and 4D/4D ODBC V16.2. I was told by the people at Tableau that ODBC must be Version 3.0 or higher. Any clues are highly appreciated. Dan -- Sent from: http://4d.1045681.n5.nabble.com/4

Re: SQL Convert problem

stered 4D Developer                       Providers of 4D, Sybase & SQL Server connectivity           http://www.informed-solutions.com   - This message and any attached documents contain information which may be confidentia

Re: SQL Convert problem

ons/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** -- - Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064 Informed Solutions, Inc. Brookline, MA 02446 USA Registered 4D Developer Providers of 4D, Sybase &a

SQL Convert problem

Hello, I have the following statement from an external application to 4D via ODBC    var_SelectQuery = "select workorder, convert(prodstartdate,SQL_VARCHAR), prodstarttime, planstarttime from workorders  where workorder > 278000" this used to work in v 6.8 but not in v16, it crashes. The problem i

RE: v13 - SQL alter table, what is wrong?

Regarding v13 docs; we have this on the Doc Center doc.4d.com: https://i.imgur.com/czSO9xs.png Hope that helps. -Tim ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.c

Re: v13 - SQL alter table, what is wrong?

> > On Mon, Sep 17, 2018 at 6:46 PM Chip Scheide via 4D_Tech < > 4d_tech@lists.4d.com> wrote: > >> Chuck, >> Thanks but... >> >> http://livedoc.4d.com/4D-SQL-Reference-14/Syntax-rules/primary-key-definition.300-1198391.en.html >> >> Sa

  1   2   3   4   >