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

2019-07-15 Thread Tom Benedict via 4D_Tech
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, I've crea

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

2019-07-15 Thread dc5464 via 4D_Tech
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 query 4D records

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

2019-07-01 Thread Tom Benedict via 4D_Tech
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

2019-07-01 Thread ernest hilgers via 4D_Tech
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

2019-03-12 Thread Charles Miller via 4D_Tech
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_Inventory

Re: sql assistance

2019-03-07 Thread Charles Miller via 4D_Tech
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

2019-03-07 Thread Charles Miller via 4D_Tech
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

RE: 4D Selection from SQL Query

2019-02-06 Thread Randy Engle via 4D_Tech
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 think it

RE: 4D Selection from SQL Query

2019-02-05 Thread Randy Engle via 4D_Tech
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

2019-02-05 Thread Keisuke Miyako via 4D_Tech
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

2019-02-05 Thread Randy Engle via 4D_Tech
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 creating a selection

Re: 4D Selection from SQL Query

2019-02-05 Thread Charles Miller via 4D_Tech
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 where

4D Selection from SQL Query

2019-02-05 Thread Randy Engle via 4D_Tech
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

Re: SQL query for the current date

2018-12-03 Thread Jeffrey Kain via 4D_Tech
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() seems to

Re: SQL query for the current date

2018-12-03 Thread Keisuke Miyako via 4D_Tech
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 WHE

SQL query for the current date

2018-11-30 Thread Jeffrey Kain via 4D_Tech
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 sequential

Re: V15 and SQL

2018-11-22 Thread Robert McKeever via 4D_Tech
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 rec

Re: SQL TRIM

2018-11-19 Thread Charles Miller via 4D_Tech
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' clause w

Re: V15 and SQL

2018-11-18 Thread Keisuke Miyako via 4D_Tech
m>のメール: > 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

2018-11-15 Thread Koen Van Hooreweghe via 4D_Tech
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, om 15:50 he

SQL TRIM

2018-11-15 Thread Jim Medlen via 4D_Tech
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

2018-11-14 Thread Robert McKeever via 4D_Tech
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

2018-11-02 Thread William Taylor via 4D_Tech
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

Re: Tableau connecting to SQL server of 4D

2018-11-02 Thread Danroth77 via 4D_Tech
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:

Re: SQL Convert problem

2018-10-30 Thread vagelis fallias via 4D_Tech
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

2018-10-29 Thread Charles Miller via 4D_Tech
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

2018-10-29 Thread vagelis fallias via 4D_Tech
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

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

2018-09-18 Thread Timothy Penner via 4D_Tech
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:

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

2018-09-18 Thread Chip Scheide via 4D_Tech
> > 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

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

2018-09-17 Thread Charles Miller via 4D_Tech
That’s v14 look at 13 doc which is where I got it from Regards Chuck 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-119839

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

2018-09-17 Thread Chip Scheide via 4D_Tech
Chuck, Thanks but... http://livedoc.4d.com/4D-SQL-Reference-14/Syntax-rules/primary-key-definition.300-1198391.en.html Says: [CONSTRAINT sql_name] PRIMARY KEY (sql_name, ... , sql_name) example: CREATE TABLE Customer (SID int32, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID

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

2018-09-17 Thread Chuck Miller via 4D_Tech
I think it should be ALTER TABLE [Anmlprot_File_Link] ADD [Journal_Key] [Primary KEY] This is from the 4D docs ALTER TABLE sql_name {ADD column_definition [PRIMARY KEY] [TRAILING] | DROP sql_name | ADD primary_key_definition | DROP PRIMARY KEY | ADD foreign_key_definition | DROP CONSTRAINT

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

2018-09-17 Thread Chuck Miller via 4D_Tech
I missed th v13 part. Is the field already set as indexed and unique. If not I would bet alter table would not work. I do not know what would happen in newer versions if you tried to assign UUID inside 4D without it being a UUID field Have you tried other data types. Regards Chuck

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

2018-09-17 Thread Chip Scheide via 4D_Tech
Mon, Sep 17, 2018 at 3:49 PM Chip Scheide via 4D_Tech < > 4d_tech@lists.4d.com> wrote: > >> trying to set a new field in a new table as primary key >> >> $SQl_Command:="ALTER TABLE [Anmlprot_File_Link] ADD PRIMARY KEY >> (Journal_Key)" >> >

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

2018-09-17 Thread Charles Miller via 4D_Tech
ink] ADD PRIMARY KEY > (Journal_Key)" > > Begin SQL > Execute Immediate : $Sql_Command > end SQL > > table : [Anmlprot_File_Link] exists > Field : Journal_Key exists as a field in [Anmlprot_File_Link] > > Journal_Key defined as: String 255, Unique, Indexed, Nu

v13 - SQL alter table, what is wrong?

2018-09-17 Thread Chip Scheide via 4D_Tech
trying to set a new field in a new table as primary key $SQl_Command:="ALTER TABLE [Anmlprot_File_Link] ADD PRIMARY KEY (Journal_Key)" Begin SQL Execute Immediate : $Sql_Command end SQL table : [Anmlprot_File_Link] exists Field : Journal_Key exists as a field in [Anmlprot

sql server unavailable

2018-08-21 Thread Randy Jaynes via 4D_Tech
What does it mean when a method suddenly starts getting a runtime error in a compiled database that says Remote SQL Server is not available The line number it gives is the End SQL line in the method. The SQL code is a simple SELECT field1, field 2 from table where (field3

Re: SQL Select failure

2018-08-10 Thread Charles Miller via 4D_Tech
Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064 Informed Solutions, Inc. Brookline, MA 02446 USA Registered 4D

SQL Select failure

2018-08-09 Thread Robert McKeever via 4D_Tech
I’m transferring data between two 4D server systems. One table contains Lab reports which are formatted from a block of HL7. The records contain the raw HL7 and a 4D Write letter which is the lab report. There could,instead, be a PDF report in a blob. There is also a table of consult letters

Re: Tinkering with SQL

2018-08-06 Thread Robert McKeever via 4D_Tech
Thanks, Tim. > On Aug 6, 2018, at 5:27 PM, Timothy Penner via 4D_Tech <4d_tech@lists.4d.com> > wrote: > >> So, how do I make the SQL case-insensitive? > > http://doc.4d.com/4Dv16R5/4D/16-R5/Database-Parameters.302-3480842.en.html > > Set Database Param

RE: Tinkering with SQL

2018-08-06 Thread Timothy Penner via 4D_Tech
> So, how do I make the SQL case-insensitive? http://doc.4d.com/4Dv16R5/4D/16-R5/Database-Parameters.302-3480842.en.html Set Database Parameter (SQL Engine case sensitivity) = selector 44 -Tim ** 4D Internet Users Group

Re: Tinkering with SQL

2018-08-06 Thread Robert McKeever via 4D_Tech
So, how do I make the SQL case-insensitive? > On Aug 6, 2018, at 4:57 PM, Keisuke Miyako via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > 4D query is case insensitive whereas SWL by default is not. > > > > **

Re: Tinkering with SQL

2018-08-06 Thread Keisuke Miyako via 4D_Tech
4D query is case insensitive whereas SWL by default is not. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub:

Tinkering with SQL

2018-08-06 Thread Robert McKeever via 4D_Tech
4D v15 I run the following code: The SQL Login goes to the database that is running. DA_SQL_Login2 C_TEXT(tLookFor) tLookFor:="%Mc%" ARRAY TEXT(aFoundNames;0) ARRAY TEXT(aFoundPhones;0) Begin SQL Select FullName, Phone FROM PatientBase WHERE (FullName like :tLookFor) INTO :a

Re: First ORDA runtime error - just like SQL

2018-07-30 Thread Illustration House via 4D_Tech
Did you mean =:or:=in the following line: ("ItemID=:1";$tItemID) Roger T. Reed > On Jul 27, 2018, at 3:41 PM, Kirk Brooks via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > Jeff, > You know - something wrong. ;-/ > > Maybe a control char? Chars outside of regular ascii? If it's

Re: First ORDA runtime error - just like SQL

2018-07-27 Thread Kirk Brooks via 4D_Tech
Jeff, You know - something wrong. ;-/ Maybe a control char? Chars outside of regular ascii? If it's possible this value could have been pasted in from some external text it might have some wonky char in it that looked pretty but chokes here. It's happened to me. May be impossible there. Agreed

Re: First ORDA runtime error - just like SQL

2018-07-27 Thread John DeSoi via 4D_Tech
> On Jul 27, 2018, at 1:52 PM, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > It's also annoying that an error handler is running, yet a runtime error > appears instead of calling the error method. Also the lack of a call chain > makes this really hard to track down. So.

Re: First ORDA runtime error - just like SQL

2018-07-27 Thread Jeffrey Kain via 4D_Tech
annoying that an error handler is running, yet a runtime error appears instead of calling the error method. Also the lack of a call chain makes this really hard to track down. It failed once in two days (this is a "workhorse" method that gets called all the time). The old Begin S

Re: First ORDA runtime error - just like SQL

2018-07-27 Thread Kirk Brooks via 4D_Tech
Randy, I'd add a check to the item ID string. ORDA is excruciatingly picky in terms of inputs. I bet something is not completely kosher in an item id and that's causing the RT error. Alternatively you could loop through the list of items and check the id there. But if the param is generated or

First ORDA runtime error - just like SQL

2018-07-27 Thread Jeffrey Kain via 4D_Tech
It looks like ORDA may be 99.9% reliable, just like 4D SQL. I just had my first random, no explanation runtime error from a method I converted from SQL to ORDA. The code has been live for a day and a half - looks like it failed sometime early in day 2. The code: C_TEXT($0;$tManufacturer

RE: SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
Tim & Anyone Else, I got it working using the following: vt_temp1:="2018-04-06 11:09:00 AM" vt_temp2:="2018-04-06 11:12:00 AM" Begin SQL SELECT RecordID, Mach_ID, Energy_1, Energy_2, Energy_3 FROM DMM_TTX_HISTORIAN WHERE [TimeStamp] >

RE: SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
* -Original Message- From: Timothy Penner [mailto:tpen...@4d.com] Sent: Tuesday, July 10, 2018 3:09 PM To: s.o...@the-aquila-group.com; '4D iNug Technical' <4d_tech@lists.4d.com> Subject: RE: SQL Statement Assistance That looks like the field is just plain text; ha

RE: SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
Justin, Thanks... I've done that multiple times and nada. The error being returned by the driver is that it says you can't convert an "ntext" to "timestamp": Error Desc = Error #206 (Operand type clash: ntext is incompatible with datetim) I did not design the SQ

RE: SQL Statement Assistance

2018-07-10 Thread Timothy Penner via 4D_Tech
That looks like the field is just plain text; have you tried just removing the extra single quotes from the text variable in your initial example (Justin Will also suggested this)? vt_temp1:="04/06/2018 07:58:00 AM" vt_temp2:="04/06/2018 08:01:00 AM" Begin SQL

Re: SQL Statement Assistance

2018-07-10 Thread Charles Miller via 4D_Tech
Hi Steve, First can you do a sql call and add timestamp to return. Use cast as VARCHAR Begin SQL SELECT RecordID, Mach_ID, Energy_1, Energy_2, Energy_3, TimeStamp FROM DMM_TTX_HISTORIAN WHERE cast(TimeStamp as VARCHAR) >= '04/06/2018 07:58:00 AM' AND cast(TimeSt

RE: SQL Statement Assistance

2018-07-10 Thread Justin Will via 4D_Tech
Steve Try removing the single quotes in your text variable assignment and retry your initial example. Justin ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html

RE: SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
Tim, This is a MS SQL server database and it queries just fine using a static timestamp value, as shown below: Begin SQL SELECT RecordID, Mach_ID, Energy_1, Energy_2, Energy_3 FROM DMM_TTX_HISTORIAN WHERE TimeStamp >= '04/06/2018 07:58:00 AM' AND TimeStamp <= '04/06/2

RE: SQL Statement Assistance

2018-07-10 Thread Timothy Penner via 4D_Tech
There may be multiple things wrong here; First, is your field actually named TimeStamp? If so, this is a SQL Reserved word and shouldn’t be used as a field identifier. Let's start with that first. -Tim ** 4D Internet Users

RE: SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
Tim, I tried the following with no success: vt_temp1:="{ts '2018-04-06:58:00'}" vt_temp2:="{ts '2018-04-08:01:00'}" Begin SQL SELECT RecordID, Mach_ID, Energy_1, Energy_2, Energy_3 FROM DMM_TTX_HISTORIAN WHERE TimeStamp>=:vt_te

RE: SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
Technical <4d_tech@lists.4d.com> Subject: RE: SQL Statement Assistance How about? - Using the TS syntax as described here: http://kb.4d.com/assetid=75947 - Using CAST to cast the TEXT as a Timestamp: http://doc.4d.com/4Dv15/4D/15/CAST.300-2288162.en.html http://kb.4d.com/assetid=75047 -

RE: SQL Statement Assistance

2018-07-10 Thread Timothy Penner via 4D_Tech
How about? - Using the TS syntax as described here: http://kb.4d.com/assetid=75947 - Using CAST to cast the TEXT as a Timestamp: http://doc.4d.com/4Dv15/4D/15/CAST.300-2288162.en.html http://kb.4d.com/assetid=75047 - Using a C_DATE variable instead of a _CTEXT variable -Tim

SQL Statement Assistance

2018-07-10 Thread Stephen J. Orth via 4D_Tech
Hoping someone can point me in the right direction regarding querying a date/time field in a SQL database: I built the following query for testing purposes and it works just fine, returning the correct results: Begin SQL

connect to a pervasive sql database

2018-05-23 Thread Chuck Miller via 4D_Tech
Hi All, Has anyone done this. I am looking to select some data from a table and import into customer 4D database so as to mark invoices paid. Is the driver only available for windows or is there a UNIX version that can be used on a MAC Thanks and regards Chuck

Re: Help with SQL login

2018-05-09 Thread Stephen Shaw via 4D_Tech
Chuck Re: SQL LOGIN(“IP:ipaddress:port:ssl”;"user”;"Userpassword";*) That did it. Thanks! Steve > On 9 May 2018, at 21:50, Charles Miller via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > Try adding the port to ip address and I always connect using IP not D

Re: Help with SQL login

2018-05-09 Thread Charles Miller via 4D_Tech
Try adding the port to ip address and I always connect using IP not DB so connection would look like 192.168.5.12:19812 Regards Chuck On Wed, May 9, 2018 at 4:47 PM, Stephen Shaw via 4D_Tech < 4d_tech@lists.4d.com> wrote: > > > Chuck > > Thanks for answering > &g

Re: Help with SQL login

2018-05-09 Thread Stephen Shaw via 4D_Tech
Chuck Thanks for answering SQL is running and shows the active connection when the code is run from the client I have just copied the code to a single user database and I am on the same network and subnet Steve > On 9 May 2018, at 21:24, Chuck Miller via 4D_Tech <4d_tech@lists.

Re: Help with SQL login

2018-05-09 Thread Chuck Miller via 4D_Tech
Are you sure that 1. SQL server is running 2. you are trying to connect to same place Regards Chuck Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (617) 232-1064

Help with SQL login

2018-05-09 Thread Stephen Shaw via 4D_Tech
Hi all I get an error when using the sql login command from 4D Developer from a stand alone database, but not from a 4D client SQL LOGIN(“4D:DatabaseName”;"user”;"Userpassword";*) If (ok=1) ALERT("Success”) // works on 4d client attached to the database

Re: SQL Date Math

2018-05-07 Thread Richard Wright via 4D_Tech
. In Oracle, for example, you can do the following to create a date: To_Date(‘2018-05-07’,’-MM-DD’) or to subtract days: SYSDATE -3 (you can also use fractions of days) or to add months: ADD_MONTHS(SYSDATE,2) But 4D SQL has nothing like these. This seems like a big

Re: SQL Date Math

2018-05-04 Thread Keisuke Miyako via 4D_Tech
lues from one type to > another. http://doc.4d.com/4Dv16R6/4D/16-R6/4D-SQL-engine-implementation.300-3628406.en.html in other words, you can't expect 4D to implicitly convert numeric 2 to TIMESTAMP. besides, with an expression such as Select someDate - 2 it is unclear whether you want t

SQL Date Math

2018-05-04 Thread Richard Wright via 4D_Tech
What kind of date math does 4D’s SQL support? The following gives an error where I’m trying to subtract 2 days from the date: Select someDate - 2 From someTable The error returned is “Operation VK_TIME - VK_LONG8 is not type safe. Failed to validate SELECT command.” Same thing if you put

Re: SQL Interface to MS SQL

2018-05-03 Thread Uist Macdonald via 4D_Tech
Thanks Jeff Uist On 03/05/2018 15:06, Jeffrey Kain via 4D_Tech wrote: Just ignore it - I've seen this happen before even on perfectly valid SQL statements pointed to 4D's own engine. ** 4D Internet Users Group (4D iNUG) FAQ

Re: SQL Interface to MS SQL

2018-05-03 Thread Jeffrey Kain via 4D_Tech
Just ignore it - I've seen this happen before even on perfectly valid SQL statements pointed to 4D's own engine. Sometimes you can tweak a line or two in the statement and the error triangle icon will go away. -- Jeffrey Kain jeffrey.k...@gmail.com > On May 3, 2018, at 7:52 AM, U

SQL Interface to MS SQL

2018-05-03 Thread Uist Macdonald via 4D_Tech
Hi I am developing a V16.6 app which uses MSSQL as the backend database. I have a slight problem in that I am storing Unicode in NVARCHAR fields which 4D indicates is a syntax error next to the line, although it actually works fine in practice. The app compiles OK. Is this something I can

Setting "Map NULL values to blank values" with SQL command

2018-02-28 Thread Tim Nevels via 4D_Tech
I’ve got some SQL code that I am running to create new fields in tables. Basic "ALTER TABLE tablename ADD fieldname VARCHAR” command. It is working just fine. But I would like to also set the "Map NULL values to blank values” checkbox property. Can you do this programmatically? I

Re: Unicode characters and SQL queries

2018-02-20 Thread David Rose via 4D_Tech
Miyako, This worked, thanks!: SET DATABASE PARAMETER(SQL engine case sensitivity;0) -- Message: 1 Date: Mon, 19 Feb 2018 20:58:10 + From: Keisuke Miyako <keisuke.miy...@4d.com> To: 4D iNug Technical <4d_tech@lis

Re: Unicode characters and SQL queries

2018-02-19 Thread Keisuke Miyako via 4D_Tech
have you tried SET DATABASE PARAMETER(SQL engine case sensitivity;0) http://doc.4d.com/4Dv16/4D/16.3/SET-DATABASE-PARAMETER.301-3651635.en.html there is also a database setting http://doc.4d.com/4Dv16/4D/16.3/SQL-page.300-3670701.en.html > On Feb 20, 2018, at 03:20, David Rose via 4D_T

Unicode characters and SQL queries

2018-02-19 Thread David Rose via 4D_Tech
This 4D Query will return records with Last_Name containing both "u" and "ü": QUERY([Donors];[Donors]Last_Name="@u@") This SQL query will only return records with Last_Name containing "u" Begin SQL SELECT Last_Name FROM Donors WHERE Last_N

Re: SQL CASE statement?

2018-01-23 Thread Tony Ringsmuth via 4D_Tech
Jeffrey Kain jeffrey.k...@gmail.com > On Jan 23, 2018, at 10:44 AM, Tony Ringsmuth via 4D_Tech <4d_tech@lists.4d.com> wrote: > > I’ve been asked if 4D SQL supports CASE statements. *

Re: SQL CASE statement?

2018-01-23 Thread Jeffrey Kain via 4D_Tech
Yes. http://doc.4d.com/4Dv15/4D/15/case-expression.300-2288083.en.html -- Jeffrey Kain jeffrey.k...@gmail.com > On Jan 23, 2018, at 10:44 AM, Tony Ringsmuth via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > I’ve been asked if 4D SQL suppor

SQL CASE statement?

2018-01-23 Thread Tony Ringsmuth via 4D_Tech
I’ve been asked if 4D SQL supports CASE statements. I don’t see it in the docs:  but does anyone know definitively? Thanks, -- Tony Ringsmuth Business Brothers Inc. 763-420-8686 ** 4D Internet Users Group (4D iNUG

Re: 4D SQL Question

2018-01-23 Thread Jim Dorrance via 4D_Tech
Thanks, I'll try that... Jim Dorrance jim.dorra...@gmail.com 4...@dorrance.eu www.4d.dorrance.eu PS: If you know of anyone that needs an experienced 4D programmer to add energy and experience to their team, please let me know. I have experience in many areas. Reasonable rates. Remote or Paris

Re: 4D SQL Question

2018-01-23 Thread John DeSoi via 4D_Tech
. It is somewhat complicated to implement, but provide a seamless interface for scrolling large record sets without loading everything into memory. John DeSoi, Ph.D. > On Jan 23, 2018, at 7:22 AM, Jim Dorrance via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > What I am doing now

Re: 4D SQL Question

2018-01-23 Thread Jim Dorrance via 4D_Tech
What I am doing now is Begin SQL SELECT * FROM [myTable] LIMIT 1 into LISTBOX :myListbox; End SQL if Records in table > 1 I then fill the rest of the arrays manually starting at goto record (1) on successive outside calls Any other ideas? -- Jim Dorrance jim.dorra...@gmail.co

Re: 4D SQL Question

2018-01-23 Thread Jim Dorrance via 4D_Tech
Anybody have some hints how I could I fill a list box progressively ( LIMIT? OFFSET), or a demo DB... Thanks, Jim On Mon, Jan 22, 2018 at 7:30 PM, Keisuke Miyako via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Not so dissimilar from your idea but you could do > > Begin SQL >

Re: 4D SQL Question

2018-01-23 Thread Jim Dorrance via 4D_Tech
Thank you. Perfect. On Mon, Jan 22, 2018 at 7:30 PM, Keisuke Miyako via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Not so dissimilar from your idea but you could do > > Begin SQL > > SELECT * FROM [myTable] LIMIT 0 into LISTBOX :m

Re: 4D SQL Question

2018-01-22 Thread Arnaud de Montard via 4D_Tech
manually", see here, the last paragraph: <http://forums.4d.com/Post/FR/19409974/1/19413742#19413742 <http://forums.4d.com/Post/FR/19409974/1/19413742#19413742>> Briefly, after SQL into listbox, duplicate one column, do what you want with that new column. -- Arnaud de Mon

Re: 4D SQL Question

2018-01-22 Thread Jeffrey Kain via 4D_Tech
a but you could do > > Begin SQL > > SELECT * FROM [myTable] LIMIT 0 into LISTBOX :myListbox; > > End SQL ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com

Re: 4D SQL Question

2018-01-22 Thread Keisuke Miyako via 4D_Tech
Not so dissimilar from your idea but you could do Begin SQL SELECT * FROM [myTable] LIMIT 0 into LISTBOX :myListbox; End SQL ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists

Re: 4D SQL Question

2018-01-22 Thread Jim Dorrance via 4D_Tech
While you are on the subject... I am a big fan of: $tSQL:="SELECT * FROM "+$tTableName+" INTO : "+$tLBox Because it creates dynamic Listbox columns automatically. Is there a clever way to select nothing into a Listbox so that an empty listbox with all the columns defined? I could then fill the

RE: 4D SQL Question

2018-01-22 Thread Stephen J. Orth via 4D_Tech
@lists.4d.com> Subject: Re: 4D SQL Question DELETE FROM users WHERE id in [1,2.3]; would delete the three users with one of those ids (or none at all, if the ids didn’t exist.) DELETE FROM users WHERE last_name=“Smith”; would delete all users with the la

Re: 4D SQL Question

2018-01-22 Thread Lee Hinde via 4D_Tech
ech > <4d_tech@lists.4d.com> wrote: > > SQL newbie here... > > What is the correct syntax for deleting a number rows in a SQL database? Is > it possible to use a 4D array which contains the values used by the WHERE > clause? > > Or, do we have to delete each row indi

4D SQL Question

2018-01-22 Thread Stephen J. Orth via 4D_Tech
SQL newbie here... What is the correct syntax for deleting a number rows in a SQL database? Is it possible to use a 4D array which contains the values used by the WHERE clause? Or, do we have to delete each row individually? Thanks! Steve

Re: MS SQL Server Deletion Issue

2017-12-21 Thread Charles Miller via 4D_Tech
Hi ware you connecting to SQL server. Are you logging in or using windows autghentication fiorm machine. I woiuld try the other and see if there is a difference Regards Chuck On Thu, Dec 21, 2017 at 3:04 PM, Stephen J. Orth via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Is there a kn

MS SQL Server Deletion Issue

2017-12-21 Thread Stephen J. Orth via 4D_Tech
Is there a known issue in V16 regarding the deletion of records from MS SQL Server? We converted a system from V15 R5 over to V16.3 NB217768. We are able to read SQL records, but no longer able to delete them. We are using the standard, built in 4D SQL commands, same code in both V15 &

RE: Twin Process for 4D SQL Data Source

2017-12-13 Thread Timothy Penner via 4D_Tech
[mailto:alantil...@gmail.com] Sent: Wednesday, December 13, 2017 1:25 PM To: Timothy Penner <tpen...@4d.com> Cc: 4D iNug Technical <4d_tech@lists.4d.com> Subject: Re: Twin Process for 4D SQL Data Source Hello Tim, Thanks for the info. Where do I find pricing for ODBC Pro? Alan On Wed, Dec 13, 201

Re: Twin Process for 4D SQL Data Source

2017-12-13 Thread Alan Tilson via 4D_Tech
Hello Tim, Thanks for the info. Where do I find pricing for ODBC Pro? Alan On Wed, Dec 13, 2017 at 4:20 PM, Timothy Penner wrote: > Hi Alan, > > > but I couldn't find a place where it is sold for 4d server. > > ODBC Pro can be purchased as an expansion when configuring a new

RE: Twin Process for 4D SQL Data Source

2017-12-13 Thread Timothy Penner via 4D_Tech
Hi Alan, > but I couldn't find a place where it is sold for 4d server. ODBC Pro can be purchased as an expansion when configuring a new server on the store: https://store.4d.com/us/article/4USE16.shtml https://store.4d.com/us/article/4USD16.shtml ODBC Pro can also be purchased as an addon for

Re: Twin Process for 4D SQL Data Source

2017-12-13 Thread Alan Tilson via 4D_Tech
, but it is interesting that ODBC Pro offers cloning of 4D tables as ODBC tables and vice versa. One of the things I do regularly is to load data from 4d and theoretically the same data from the SQL Server system and compare it using arrays to find records that have changed. If this could be done within one SQL

Re: Twin Process for 4D SQL Data Source

2017-12-13 Thread Charles Miller via 4D_Tech
t counted 14 logins to the SQL Server in 1 min > 50 sec so I ran a test logging in and out 20 times and this took 2.03 > seconds. > There would have to be some overhead associated with using a separate > process so I doubt there would be any gain. > It just seems rather clunky

  1   2   3   >