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

[Randy Jaynes via 4D_Tech]

Julio,

http://forums.4d.com/Post/EN/27095796/1

https://bugs.4d.com/fixes?version=16_R2 shows 

ACI0095714  ODBC driver returns error #9922 if last character in a word is 
a diacritical sign as è,é or ä (using fields).

You would think your version is not susceptible to that because it was 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

--
Randy Jaynes
Senior Programmer and Customer Support

http://printpoint.com • 845.687.3741 • PrintPoint, Inc • 57 Ludlow Lane • 
Palisades, NY 10964 




Please send all emails to supp...@printpoint.com




> On Oct 8, 2021, at 10:37 AM, Julio Carneiro via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> 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 spot any significant difference between “INSERT” that pass and 
> those that fail. Even to the same table. 
> 
> Any link/hints will help
> --
> Julio Carneiro
> jjfo...@gmail.com
> 
> 
> 
> 
> **
> 4D Internet Users Group (4D iNUG)
> New Forum: https://discuss.4D.com
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
New Forum: https://discuss.4D.com
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

error 9922 o SQL Execute? what does that mean?

[Julio Carneiro via 4D_Tech]

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 spot any significant difference between “INSERT” that pass and 
those that fail. Even to the same table. 

Any link/hints will help
--
Julio Carneiro
jjfo...@gmail.com




**
4D Internet Users Group (4D iNUG)
New Forum: https://discuss.4D.com
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Looking for 3rd party SQL query tool

[Mike Kerner via 4D_Tech]

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/
>
>
> 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: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **



-- 
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Looking for 3rd party SQL query tool

[Paul Dennis via 4D_Tech]

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: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Looking for 3rd party SQL query tool

[Ed Hammond via 4D_Tech]

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 have MS Office. You can use MS Query and an ODBC connection to test 
queries and send results to Excel.

--
Edgar Hammond  e...@questinformation.com
Quest Information Systems 
http://www.questinformation.com
--



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Looking for 3rd party SQL query tool

[Justin Will via 4D_Tech]

Tony

I personally use the SqlDbx professional but you could try taking a look at 
RazorSQL or DBeaver

Thanks
Justin

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Tony Ringsmuth via 
4D_Tech
Sent: Monday, March 30, 2020 10:54 AM
To: 4D Nug <4d_tech@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 version is a bit spendy.

 

Thanks,

--

Tony Ringsmuth

Business Brothers Inc.

763-420-8686

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Looking for 3rd party SQL query tool

[Tony Ringsmuth via 4D_Tech]

Thanks Christian,
I'm actually looking for a Windows tool, not mac.

Tony

On 3/30/20, 10:59 AM, "4D_Tech on behalf of Christian Sakowski via 4D_Tech" 
<4d_tech-boun...@lists.4d.com on behalf of 4d_tech@lists.4d.com> wrote:

Hi Tony,

i guess you are seeking for a Mac-Tool?
I am using SQLPro for MSSQL/mysql from there:
https://www.macsqlclient.com
--

Grüße/Regards,
[heubach-media] | Christian Sakowski
christian.sakow...@heubach-media.de
Tel: +49/(0)40/41 455 455





> Am 30.03.2020 um 17:53 schrieb 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 bit only. 
 Their 64 bit version is a bit spendy.
> 
> 
> 
> Thanks,
> 
> --
> 
> Tony Ringsmuth
> 
> Business Brothers Inc.
> 
> 763-420-8686
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Looking for 3rd party SQL query tool

[Christian Sakowski via 4D_Tech]

Hi Tony,

i guess you are seeking for a Mac-Tool?
I am using SQLPro for MSSQL/mysql from there:
https://www.macsqlclient.com
--

Grüße/Regards,
[heubach-media] | Christian Sakowski
christian.sakow...@heubach-media.de
Tel: +49/(0)40/41 455 455





> Am 30.03.2020 um 17:53 schrieb 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 bit only.  
> Their 64 bit version is a bit spendy.
> 
> 
> 
> Thanks,
> 
> --
> 
> Tony Ringsmuth
> 
> Business Brothers Inc.
> 
> 763-420-8686
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Looking for 3rd party SQL query tool

[Tony Ringsmuth via 4D_Tech]

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-8686

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Charles Miller via 4D_Tech]

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.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 from SQL Server into 4D
> tables in order to analyse it - the developer wishes to do this from 4D for
> his convenience
> * the requirement is most likely one-way read only from MS SQL Server and
> write to 4D - tbc
>
> If my initial investigations are correct we need:
> * an ODBC manager
> * SQL Server ODBC driver
>
> For the ODBC manager it seems there are really only 2 choices - both free:
> * http://www.odbcmanager.net - ODBC Manager
> * http://www.iodbc.org - iODBC
>
> For the ODBC driver there seems to be these options - requiring licences:
> * http://www.actualtech.com
> * https://www.openlinksw.com
>
> It looks like ODBC Manager is supported and maintained by Actual
> Technologies who can also provide a driver.
>
> Does anybody have recent experience of connecting to MS SQL Server from 4D
> v17 on Mac OS 10.14? If so:
> * what combination of driver manager and SQL Server driver worked for you
> - was it from the ones I have listed or some other?
> * could you kindly elaborate on your experience - connection, reliability,
> driver stability, performance, etc
> * any other useful pointers for consideration?
>
> Regards,
> Narinder Chandi,
> ToolBox Systems Ltd.
> https://toolbox.systems
>
> I am available for new consulting opportunities…
> http://4d.1045681.n5.nabble.com/ANN-4D-Developer-Available-td5765443.html
> --
>
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity
  https://www.informed-solutions.com
-
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption from
disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Narinder Chandi via 4D_Tech]

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 from SQL Server into 4D tables in 
order to analyse it - the developer wishes to do this from 4D for his 
convenience
* the requirement is most likely one-way read only from MS SQL Server and write 
to 4D - tbc

If my initial investigations are correct we need:
* an ODBC manager
* SQL Server ODBC driver

For the ODBC manager it seems there are really only 2 choices - both free:
* http://www.odbcmanager.net - ODBC Manager
* http://www.iodbc.org - iODBC

For the ODBC driver there seems to be these options - requiring licences:
* http://www.actualtech.com
* https://www.openlinksw.com

It looks like ODBC Manager is supported and maintained by Actual Technologies 
who can also provide a driver.

Does anybody have recent experience of connecting to MS SQL Server from 4D v17 
on Mac OS 10.14? If so:
* what combination of driver manager and SQL Server driver worked for you - was 
it from the ones I have listed or some other?
* could you kindly elaborate on your experience - connection, reliability, 
driver stability, performance, etc
* any other useful pointers for consideration?

Regards,
Narinder Chandi,
ToolBox Systems Ltd.
https://toolbox.systems
 
I am available for new consulting opportunities…
http://4d.1045681.n5.nabble.com/ANN-4D-Developer-Available-td5765443.html
-- 


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: How to Modify a column using SQL

[Jim Labos - infobase via 4D_Tech]

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
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: How to Modify a column using SQL

[John DeSoi via 4D_Tech]

Untested, but with version 18 in source mode you could modify 
Project/Sources/catalog.4DCatalog.

John DeSoi, Ph.D.


> On Dec 4, 2019, at 2:42 PM, Jim Labos - infobase via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> 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.

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: How to Modify a column using SQL

[Jim Labos - infobase via 4D_Tech]

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

Jim



-
Jim Labos - infobase
--
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: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: How to Modify a column using SQL

[Chuck Miller via 4D_Tech]

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: (617) 232-1064   
 mailto:cjmillerinformed-solutions.com 
 Brookline, MA 02446 USA Registered 4D Developer
   Providers of 4D and Sybase connectivity
  http://www.informed-solutions.com  

This message and any attached documents contain information which may be 
confidential, subject to privilege or exempt from disclosure under applicable 
law.  These materials are intended only for the use of the intended recipient. 
If you are not the intended recipient of this transmission, you are hereby 
notified that any distribution, disclosure, printing, copying, storage, 
modification or the taking of any action in reliance upon this transmission is 
strictly prohibited.  Delivery of this message to any person other than the 
intended recipient shall not compromise or waive such confidentiality, 
privilege or exemption from disclosure as to this communication. 

> On Dec 2, 2019, at 2:22 PM, Jim Labos - infobase via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> Does anyone know if this has changed since 2013?
> 
> I couldn't find anything but I could be missing something.
> 
> Thanks

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: How to Modify a column using SQL

[Jim Labos - infobase via 4D_Tech]

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 Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Tom Benedict via 4D_Tech]

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 tools? 

Tom

> On Nov 25, 2019, at 08:48, Tom Benedict via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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 “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>のメール:
>> Miyako’s code requires XSLT, which is now deprecated, and I didn’t want to 
>> dive into modifying it to use
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Keisuke Miyako via 4D_Tech]

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@lists.4d.com>のメール:
> As for Alexander’s code, I noticed the following sometime ago. Maybe 
> something this simple is causing the blackness as well?




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Cannon Smith via 4D_Tech]

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 62 convert the decimal point in the height, left, top, and width of 
> the XML values to a comma. For values that have a decimal place, this can 
> make for some really, really big table widths and heights!

--
Cannon.Smith
Synergy Farm Solutions Inc.
Aetna, AB Canada




> On Nov 25, 2019, at 6:00 AM, Tom Benedict via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Alexander’s code ended with a number of black tables.

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Tom Benedict via 4D_Tech]

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 “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>のメール:
> Miyako’s code requires XSLT, which is now deprecated, and I didn’t want to 
> dive into modifying it to use

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Keisuke Miyako via 4D_Tech]

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>のメール:
Miyako’s code requires XSLT, which is now deprecated, and I didn’t want to dive 
into modifying it to use



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Herr Alexander Heintz via 4D_Tech]

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 correct 
this in code.
In the list of tables you should see a preview of the color it will render 
with, although I do not remeber if I added the option to change the color.
I did it with a structure of 374 tables und a truckload of relations and it 
worked.
Also you can export as SVG and this can be edited in some drawing apps (I use 
Illustrator).

Regards,
Alex
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[John DeSoi via 4D_Tech]

> 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 Studio, MySQL Workbench etc) to create an Entity 
> Relationship Diagram which I can manipulate and print.
> 
> 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. 
> I’ve tried to work with it in XMLSpy, but I don’t know enough about XSD, XSL, 
> DTD and XML to make much headway.


Working with the raw structure XML is rather painful, I found it easier to 
convert it to an object. But if all you want to do is write SQL for an ER 
modeling tool, I don't see why you need to deal with XML unless you have 
something that would use the relation names. GET RELATION PROPERTIES is enough 
to get all the source and target fields in the database. That should be enough 
to create foreign key references in your SQL export for the modeling tool.

John DeSoi, Ph.D.




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Tom Benedict via 4D_Tech]

Thanks for the reply Arnaud,

Yes. I’ve tried the SVG exports posted on the forum and they run into issues, 
at least for me. Perhaps my structure is too large? 225 tables. 

My results with Vincent’s code have overlapped tables and relation lines which 
don’t line up correctly. 

Miyako’s code requires XSLT, which is now deprecated, and I didn’t want to dive 
into modifying it to use

Alexander’s code ended with a number of black tables.

My effort to stitch together screenshots involves 108 1920x1080 pictures, so 
even with Snagit’s panorama feature it takes hours of manual work. Perhaps I 
should have used that time to write code instead.

I guess that printing an ERD is not common among 4D developers.

Tom Benedict  

> On Nov 25, 2019, at 00:42, Arnaud init5 imap via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> 
> 
>> 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 non-4D tool (like SQL 
>> Server Management Studio, MySQL Workbench etc) to create an Entity 
>> Relationship Diagram which I can manipulate and print.
> 
> Hi Tom, 
> have you seen these? 
> <https://forums.4d.com/Post/FR/2634499/1/2636076#2638661 
> <https://forums.4d.com/Post/FR/2634499/1/2636076#2638661>>
> <https://forums.4d.com/Post/FR/3124235/1/3124255#3124255 
> <https://forums.4d.com/Post/FR/3124235/1/3124255#3124236>>
> Using the export structure file to draw structure with SVG. 
> 
> If often use yEd, I like it's automatic redrawing. But I use it before 
> working in 4d, not after until now, I don't know how to "import" 4d structure 
> with it. 
> 
> -- 
> Arnaud de Montard 

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Arnaud init5 imap via 4D_Tech]

> 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 non-4D tool (like SQL 
> Server Management Studio, MySQL Workbench etc) to create an Entity 
> Relationship Diagram which I can manipulate and print.

Hi Tom, 
have you seen these? 
<https://forums.4d.com/Post/FR/2634499/1/2636076#2638661 
<https://forums.4d.com/Post/FR/2634499/1/2636076#2638661>>
<https://forums.4d.com/Post/FR/3124235/1/3124255#3124255 
<https://forums.4d.com/Post/FR/3124235/1/3124255#3124236>>
Using the export structure file to draw structure with SVG. 

If often use yEd, I like it's automatic redrawing. But I use it before working 
in 4d, not after until now, I don't know how to "import" 4d structure with it. 

-- 
Arnaud de Montard 





**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Herr Alexander Heintz via 4D_Tech]

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-structure-access/ 
<http://blog.heintz.net/sql-structure-access/>
This does export your structure as SQL table definitions, although without the 
relations (Foreign Key Constrains in MySQL speak).
Source available for a small donation.

Adding the export of the constrains should not be too hard, maybe I could do it 
if you really want to, otherwise here is the syntax:

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

Easy as pie :-)

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Tom Benedict via 4D_Tech]

I’d like to use an external data modeling tool to display my 4D structure file. 
Ultimately I want to print the 4D Structure. I’ve tried all the 4D components 
and scripts and they all seem to have shortcomings. Right now I’m in the middle 
of a tedious screenshot, export, panoramic merge in 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 Workbench etc) to create an Entity Relationship 
Diagram which I can manipulate and print.

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. I’ve tried to 
work with it in XMLSpy, but I don’t know enough about XSD, XSL, DTD and XML to 
make much headway.

Has anyone else done this?

Thanks,

Tom Benedict
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Arnaud de Montard via 4D_Tech]

> 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 
"automatic" and uneasy to predict; I also remember in previous 4d versions they 
could use existing names in the form without throwing an error; anyway I prefer 
to rename them after the query:

 //LB_renameCH (LBname_t)
 //rename columns + headers of a listbox using listbox name
C_TEXT($1)
$LBname_t:=$1
$nbCol_l:= LISTBOX Get number of columns(*;$LBname_t)
ARRAY TEXT($obj_at;0)
LISTBOX GET OBJECTS(*;$LBname_t;$obj_at) 
For ($i_l;1;$nbCol_l)
$arr_p:=OBJET Get pointer(Object named;$obj_at{$i_l*3-2})
$head_p:= OBJET Get pointer(Object named;$obj_at{$i_l*3-1})
$title_t:=OBJET Get title(*;$obj_at{$i_l*3-1})
$Cname_t:=$LBname_t+"_C"+String($i_l)
$Hname_t:=$LBname_t+"_H"+ String($i_l)
LISTBOX INSERT 
COLUMN(*;$LBname_t;$nbCol_l+$i_l;$Cname_t;$arr_p->;$Hname_t;$head_p->)
OBJET SET TITLE(*;$Hname_t;$title_t)
LISTBOX DELETE COLUMN(*;$LBname_t;1)
End for

-- 
Arnaud de Montard 





**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Chip Scheide via 4D_Tech]

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 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 Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

Hell is other people 
 Jean-Paul Sartre
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Spencer Hinsdale via 4D_Tech]

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 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 Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Chip Scheide via 4D_Tech]

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 Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Douglas Cryer via 4D_Tech]

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 would expect you would hit 
are: Oracle, MS SQL, MySQL, PostgreSQL

For each there are either general tools or specialist tools which can be used 
from 4D.  My favourite tools are Pluggers options for MySQL, SQLite and 
PostgreSQL

For other databases the base option is to use 4D ODBC Pro apart from Oracle 
where you could use 4D for OCI

I have always liked SQL although each system has it's own variations from the 
standard.

You said you were in the 'can this be done?' phase.  I would say it can be done 
the extent to which it is reasonable depends on your budget and time to learn.  
Sometimes simpler solutions are better.  For instance there are a number of 
tools that will allow you to run a select script which could be written to a 
file and then imported.  That would allow you to separate the SQL scripting 
from the report storage and data processing.

Regards,  Dougie


telekinetix Limited- J. Douglas Cryer
Phone : 01234 761759  Mobile : 07973 675 218
2nd Floor Broadway House, 4-6 The Broadway, Bedford MK40 2TE
Email : jdcr...@telekinetix.com  Web : http://www.telekinetix.com 
<http://www.telekinetix.com/>

 
 



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Robert McKeever via 4D_Tech]

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 - actually wrote 
4D code to generate 4D code for the extracts. Took 3-4 days.

Figured out what the client actually need, etc. The system is still running.

The code is probably around here somewhere - I’m like a packrat - I never throw 
code away.

> On Oct 8, 2019, at 10:04 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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.
> 
> so.. I have a few questions...
> 
> Given:
> - any version of 4D from v13 -> v17
> - an unknown SQL compliant database
> - a valid username/password
> 
> is it possible/reasonable to be able to:
> - get the table/field 'structure' (schema?) of the SQL system
> - use the table/field info to automagically create a matching 4D structure?
> - extract data (to text, or into a 4D system) from the SQL system
> 
> 
> Thanks
> Chip
> 
> Hell is other people 
> Jean-Paul Sartre
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

_
Bob McKeever  http://www.mswl.com <http://www.mswl.com/>
McKeever's Software Wizardry
Port Coquitlam, B.C.
bobmckee...@mac.com




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Chip Scheide via 4D_Tech]

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 contains information regarding 'projects'. 
- each program was written/maintained by separate software developers
- each program has part of the data needed for the desired final report 
product

I am currently in the 'can this be done?' phase, so I am just trying to 
get a feel for what is reasonable to be able to do.
However, honestly, even if I can get schema and raw data access - I am 
not sure that coordination of all the data will be practical, let alone 
possible - especially with little or no input from the 4 vendors...

Chip
On Wed, 9 Oct 2019 04:51:41 -0700, Tom Benedict wrote:
> Hi Chip,
> 
> The answer is yes to your three questions, but access to the 
> table/fields schema will need to be granted to the user. Also, “
> automagically’ creating a matching 4D structure will require you 
> write code, so it’s not very ‘magic’. ;)
> 
> Extracting data should be straightforward. It’s just a 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 Oct 8, 2019, at 22:04, Chip Scheide via 4D_Tech 
>> <4d_tech@lists.4d.com> wrote:
>> 
>> 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.
>> 
>> so.. I have a few questions...
>> 
>> Given:
>> - any version of 4D from v13 -> v17
>> - an unknown SQL compliant database
>> - a valid username/password
>> 
>> is it possible/reasonable to be able to:
>> - get the table/field 'structure' (schema?) of the SQL system
>> - use the table/field info to automagically create a matching 4D structure?
>> - extract data (to text, or into a 4D system) from the SQL system
>> 
>> 
>> Thanks
>> Chip
> 
> 
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Tom Benedict via 4D_Tech]

Hi Chip,

The answer is yes to your three questions, but access to the table/fields 
schema will need to be granted to the user. Also, “automagically’ creating a 
matching 4D structure will require you write code, so it’s not very ‘magic’. ;)

Extracting data should be straightforward. It’s just a 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 Oct 8, 2019, at 22:04, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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.
> 
> so.. I have a few questions...
> 
> Given:
> - any version of 4D from v13 -> v17
> - an unknown SQL compliant database
> - a valid username/password
> 
> is it possible/reasonable to be able to:
> - get the table/field 'structure' (schema?) of the SQL system
> - use the table/field info to automagically create a matching 4D structure?
> - extract data (to text, or into a 4D system) from the SQL system
> 
> 
> Thanks
> Chip

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

v13+ SQL access other system

[Chip Scheide via 4D_Tech]

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.

so.. I have a few questions...

Given:
- any version of 4D from v13 -> v17
- an unknown SQL compliant database
- a valid username/password

is it possible/reasonable to be able to:
- get the table/field 'structure' (schema?) of the SQL system
- use the table/field info to automagically create a matching 4D structure?
- extract data (to text, or into a 4D system) from the SQL system


Thanks
Chip

Hell is other people 
 Jean-Paul Sartre
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D ODBC, MS SQL and Alpha Fields

[David Samson via 4D_Tech]

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
Manchester
UK


>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}
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D ODBC, MS SQL and Alpha Fields

[Tom Benedict via 4D_Tech]

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, Charles Miller via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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 technologies
> 
> Regards
> 
> Chuck
> 
> On Thu, Oct 3, 2019 at 12:56 PM Paul Jones via 4D_Tech
> <4d_tech@lists.4d.com> wrote:
>> 
>> 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.
>> 
>> 

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D ODBC, MS SQL and Alpha Fields

[Charles Miller via 4D_Tech]

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 technologies

Regards

Chuck

On Thu, Oct 3, 2019 at 12:56 PM Paul Jones via 4D_Tech
<4d_tech@lists.4d.com> wrote:
>
> 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}
>
> I've tried several different ways to pull the data and I've found the 
> following:
> If I only query for records where the field contains data {not like ""}, the 
> field doesn't get truncated.  If I try to select all records,  the truncation 
> occurs.
>
> I have a very inefficient workaround which is to pull the truncated fields 
> one at a time into a temporary table and then update the record in the 
> original table.  While that works,  if I have to do that for every Alpha 
> field,  the process takes way to long.
>
> Here is an example of what gets returned without filtering for blank records:
>
> "wh
> 
>   "
> "no
> 
>   ?   
>   
>   
>   "
> "wh
> 
>   "
>
>
>   *   Note the 2nd records appears to contain a length of 257 characters when 
> in fact the field is only 10 characters.
>
> The correct field values that should be returned are:
> "wholesale"
> "non_exempt"
> "wholesale"
>
>
> Just wondering if anyone has worked with pulling data from 4D into MSSQL and 
> if have had this same issue and have found a solution to the problem.
>
>
> Paul Jones
>
>
> _
> Please consider the environment before printing
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity
  https://www.informed-solutions.com
-
This message and any attached documents contain information which may
be confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution,
disclosure, printing, copying, storage, modification or the taking of
any action in reliance upon this transmission is strictly prohibited.
Delivery of this message to any person other than the intended
recipient shall not compromise or waive such confidentiality,
privilege or exemption from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

4D ODBC, MS SQL and Alpha Fields

[Paul Jones via 4D_Tech]

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}

I've tried several different ways to pull the data and I've found the following:
If I only query for records where the field contains data {not like ""}, the 
field doesn't get truncated.  If I try to select all records,  the truncation 
occurs.

I have a very inefficient workaround which is to pull the truncated fields one 
at a time into a temporary table and then update the record in the original 
table.  While that works,  if I have to do that for every Alpha field,  the 
process takes way to long.

Here is an example of what gets returned without filtering for blank records:

"wh

  "
"no

  ? 


"
"wh

  "


  *   Note the 2nd records appears to contain a length of 257 characters when 
in fact the field is only 10 characters.

The correct field values that should be returned are:
"wholesale"
"non_exempt"
"wholesale"


Just wondering if anyone has worked with pulling data from 4D into MSSQL and if 
have had this same issue and have found a solution to the problem.


Paul Jones


_
Please consider the environment before printing
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY WITH ARRAY - SQL Equivalent?

[Arnaud de Montard via 4D_Tech]

> 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 change reserved names in tables 
and fields - I run code like this until none found:
<https://forums.4d.com/Post/FR/14532263/1/14536260#14536260> 
I use "PK" for primary key and "FK_[tableOneName]" for foreign key. I have a 
friend whose initials are PK, he found that awesome. 

To query with array, another solution:
<https://forums.4d.com/Post/FR/13701036/1/13701037#13705082>

-- 
Arnaud de Montard




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY WITH ARRAY - SQL Equivalent?

[Adam Bevan via 4D_Tech]

 <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't change the selection or current record for
the current process.

I'm not saying SQL is bad, but it's just nice to have the quick ease of
flipping a switch to run native 4D.


Adam Bevan
Developer & CEO, Argus Productions Inc.
<https://www.facebook.com/ArgusProductions/>


On Wed, 18 Sep 2019 at 11:05, Chuck Miller via 4D_Tech <4d_tech@lists.4d.com>
wrote:

> 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]
> where
> Personnel]Personnel_ID in :$INCLUDED_txt:
>
> end sql
>
> 
>  Chuck Miller Voice: (617) 739-0306
>  Informed Solutions, Inc. Fax: (617) 232-1064
>  mailto:cjmillerinformed-solutions.com
>  Brookline, MA 02446 USA Registered 4D Developer
>Providers of 4D and Sybase connectivity
>   http://www.informed-solutions.com
>
> 
> This message and any attached documents contain information which may be
> confidential, subject to privilege or exempt from disclosure under
> applicable law.  These materials are intended only for the use of the
> intended recipient. If you are not the intended recipient of this
> transmission, you are hereby notified that any distribution, disclosure,
> printing, copying, storage, modification or the taking of any action in
> reliance upon this transmission is strictly prohibited.  Delivery of this
> message to any person other than the intended recipient shall not
> compromise or waive such confidentiality, privilege or exemption from
> disclosure as to this communication.
>
> > On Sep 18, 2019, at 11:44 AM, Randy Engle via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
> >
> > 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)
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: QUERY WITH ARRAY - SQL Equivalent?

[Randy Engle via 4D_Tech]

Chuck,

Thanks for the response!

Took a while to get the syntax just right, but what you have below is basically 
what I ended up with.

Thanks

Randy Engle

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Chuck Miller via 
4D_Tech
Sent: Wednesday, September 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_txt+”,”+String(al_Personnel_ID{LOOPL})

end for
$INCLUDED_txt:=$INCLUDED_txt+”)"
begin sql
select [Personnel].[field],...

from [Personnel]
where
Personnel]Personnel_ID in :$INCLUDED_txt:

end sql

 Chuck Miller Voice: (617) 739-0306
 Informed Solutions, Inc. Fax: (617) 232-1064   
 mailto:cjmillerinformed-solutions.com 
 Brookline, MA 02446 USA Registered 4D Developer
   Providers of 4D and Sybase connectivity
  http://www.informed-solutions.com  

This message and any attached documents contain information which may be 
confidential, subject to privilege or exempt from disclosure under applicable 
law.  These materials are intended only for the use of the intended recipient. 
If you are not the intended recipient of this transmission, you are hereby 
notified that any distribution, disclosure, printing, copying, storage, 
modification or the taking of any action in reliance upon this transmission is 
strictly prohibited.  Delivery of this message to any person other than the 
intended recipient shall not compromise or waive such confidentiality, 
privilege or exemption from disclosure as to this communication. 

> On Sep 18, 2019, at 11:44 AM, Randy Engle via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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)

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: QUERY WITH ARRAY - SQL Equivalent?

[Randy Engle via 4D_Tech]

Jeff,

Many thanks,

Yes, I playing with the "IN" function

Randy Engle


Internet – Hosted – In the Cloud:   On-line Backflow Test Entry!
XC2Live!  - Backflow Program Management
Operational Now! – Call for additional information:  800.761.4999

-Original Message-
From: 4D_Tech <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 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:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY WITH ARRAY - SQL Equivalent?

[Chuck Miller via 4D_Tech]

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]
where
Personnel]Personnel_ID in :$INCLUDED_txt:

end sql

 Chuck Miller Voice: (617) 739-0306
 Informed Solutions, Inc. Fax: (617) 232-1064   
 mailto:cjmillerinformed-solutions.com 
 Brookline, MA 02446 USA Registered 4D Developer
   Providers of 4D and Sybase connectivity
  http://www.informed-solutions.com  

This message and any attached documents contain information which may be 
confidential, subject to privilege or exempt from disclosure under applicable 
law.  These materials are intended only for the use of the intended recipient. 
If you are not the intended recipient of this transmission, you are hereby 
notified that any distribution, disclosure, printing, copying, storage, 
modification or the taking of any action in reliance upon this transmission is 
strictly prohibited.  Delivery of this message to any person other than the 
intended recipient shall not compromise or waive such confidentiality, 
privilege or exemption from disclosure as to this communication. 

> On Sep 18, 2019, at 11:44 AM, Randy Engle via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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)

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY WITH ARRAY - SQL Equivalent?

[Jeffrey Kain via 4D_Tech]

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:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY WITH ARRAY - SQL Equivalent?

[Chip Scheide via 4D_Tech]

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("selection name")

or
SQL -- I think

$SQL_Query:="select  from  where "

for (size of array)
 $SQL_Query:=$SQL_Query+" = array element or "
end for
begin sql
execute immediate($SQL_Query)
end sql


I am not entirely sure about the "or" but that is the gist,
and '< >' are place holders for needed field or table info

Chip

On Wed, 18 Sep 2019 08:44:10 -0700, Randy Engle via 4D_Tech wrote:
> 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 changes the current selection and current record.
> 
> Whereas, what I really want is to end up with a half dozen or so 
> arrays from the [Personnel] Table, without touching the current 
> selection/record.
> 
> I'm using 4D 16r6 (Windows)
> 
> Anyone know of a SQL function that does this?
> Or some other method.
> 
> "Execute on Server" attribute would work, except in many cases these 
> are single-user systems, not client/server.
> 
> Gracious thanks to all
> 
> Randy Engle
> 
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

QUERY WITH ARRAY - SQL Equivalent?

[Randy Engle via 4D_Tech]

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 changes the current selection and current record.

Whereas, what I really want is to end up with a half dozen or so arrays from 
the [Personnel] Table, without touching the current selection/record.

I'm using 4D 16r6 (Windows)

Anyone know of a SQL function that does this?
Or some other method.

"Execute on Server" attribute would work, except in many cases these are 
single-user systems, not client/server.

Gracious thanks to all

Randy Engle


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL Connection to External SQL DB

[Tarawerkz via 4D_Tech]

Hi Tom, 

….and a word of thanks too for your reply and the tip !

Regards,
Ronnie
Tarawerkz




> On 16 Sep 2019, at 4:34 PM, Tarawerkz  wrote:
> 
> 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.
> 
> Regards,
> Ronnie
> Tarawerkz
> 
> 
> 
> 
>> On 16 Sep 2019, at 2:13 PM, Tom Benedict > <mailto:benedic...@comcast.net>> wrote:
>> 
>> Hi Ronnie,
>> 
>> My suspicion is that something around SSL on the external DB has changed. 
>> Perhaps your app uses a version of 4D which supports TLS 1.0 and the 
>> external DB was changed to use TLS 1.2? Or maybe the other way around? Just 
>> a guess.
>> 
>> Here’s 4D Tech Tip which can help determine what 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 
>>> <mailto:4d_tech@lists.4d.com>> wrote:
>>> 
>>> 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 of debugging, I tried a simple one-line SQL login command as 
>>> follows :
>>> 
>>>     SQL Login(“IP:10.240.99.99”;$username;$password”;*)
>>> 
>>> SQL Login(“IP:10.240.99.99:1433”;$username;$password”;*)
>>> SQL Login(“IP:10.240.99.99:1433:ssl”;$username;$password”;*)
>>> 
>>> They all resulted in 4D Syntax error that indicates a failure to connect to 
>>> remote server, with a SQL error 9918, Generic SQL error
>>> 
>>> Where has it gone wrong?  Thank you for any advise.  
>>> 
>>> 
>>> Regards,
>>> Ronnie
>>> Tarawerkz
>>> 
>> 
> 

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL Connection to External SQL DB

[Tarawerkz via 4D_Tech]

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.

Regards,
Ronnie
Tarawerkz




> On 16 Sep 2019, at 2:13 PM, Tom Benedict  wrote:
> 
> Hi Ronnie,
> 
> My suspicion is that something around SSL on the external DB has changed. 
> Perhaps your app uses a version of 4D which supports TLS 1.0 and the external 
> DB was changed to use TLS 1.2? Or maybe the other way around? Just a guess.
> 
> Here’s 4D Tech Tip which can help determine what 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 
>> <mailto:4d_tech@lists.4d.com>> wrote:
>> 
>> 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 of debugging, I tried a simple one-line SQL login command as 
>> follows :
>> 
>>  SQL Login(“IP:10.240.99.99”;$username;$password”;*)
>> 
>>  SQL Login(“IP:10.240.99.99:1433”;$username;$password”;*)
>>  SQL Login(“IP:10.240.99.99:1433:ssl”;$username;$password”;*)
>> 
>> They all resulted in 4D Syntax error that indicates a failure to connect to 
>> remote server, with a SQL error 9918, Generic SQL error
>> 
>> Where has it gone wrong?  Thank you for any advise.  
>> 
>> 
>> Regards,
>> Ronnie
>> Tarawerkz
>> 
> 

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL Connection to External SQL DB

[Tom Benedict via 4D_Tech]

Hi Ronnie,

My suspicion is that something around SSL on the external DB has changed. 
Perhaps your app uses a version of 4D which supports TLS 1.0 and the external 
DB was changed to use TLS 1.2? Or maybe the other way around? Just a guess.

Here’s 4D Tech Tip which can help determine what 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 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 of debugging, I tried a simple one-line SQL login command as 
> follows :
> 
>   SQL Login(“IP:10.240.99.99”;$username;$password”;*)
> 
>       SQL Login(“IP:10.240.99.99:1433”;$username;$password”;*)
>   SQL Login(“IP:10.240.99.99:1433:ssl”;$username;$password”;*)
> 
> They all resulted in 4D Syntax error that indicates a failure to connect to 
> remote server, with a SQL error 9918, Generic SQL error
> 
> Where has it gone wrong?  Thank you for any advise.  
> 
> 
> Regards,
> Ronnie
> Tarawerkz
> 

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

SQL Connection to External SQL DB

[Tarawerkz via 4D_Tech]

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 of debugging, I tried a simple one-line SQL login command as 
follows :

SQL Login(“IP:10.240.99.99”;$username;$password”;*)

SQL Login(“IP:10.240.99.99:1433”;$username;$password”;*)
SQL Login(“IP:10.240.99.99:1433:ssl”;$username;$password”;*)

They all resulted in 4D Syntax error that indicates a failure to connect to 
remote server, with a SQL error 9918, Generic SQL error

Where has it gone wrong?  Thank you for any advise.  


Regards,
Ronnie
Tarawerkz




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Confirmation for SQL Execute Immediate

[William Swann via 4D_Tech]

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 Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Confirmation for SQL Execute Immediate

[Keisuke Miyako via 4D_Tech]

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-4128371.en.html

no ODBC in preemptive mode with Begin/End SQL

some semantics:

internal DB refers to the 4D database defined in the current structure file
external DB refers to the 4D database opened via the SQL USE DATABASE command.
it can be on the client or server side.

https://doc.4d.com/4Dv17R5/4D/17-R5/USE-DATABASE.300-4142821.en.html

a 4D database served by another 4D application is a "remote" database (not 
external).

the "external" database referred to in the documentation

https://doc.4d.com/4Dv17R5/4D/17-R5/EXECUTE-IMMEDIATE.300-4142833.en.html

is not a remote database (that you would log into) but rather,
a 4DB/4DD file you open via the SQL USE DATABASE command.

note that there is not authentication required to use an external database.

> 2019/08/15 3:41、William Swann via 4D_Tech <4d_tech@lists.4d.com>のメール:
>
> Now the question, is it still true that "execute immediate" should only
> be used with the internal SQL db when using an entire SQL statement like
> above in a text variable.




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Confirmation for SQL Execute Immediate

[William Swann via 4D_Tech]

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 an 
external DB.


Now the question, is it still true that "execute immediate" should only 
be used with the internal SQL db when using an entire SQL statement like 
above in a text variable. I have been using it interpreted s as below 
and it works great, but I would prefer to follow recommended course. So 
4D folks, should I or shouldn't I?


sqlVar:="SELECT Col From Table WHERE condition INTO :var"

SQL Login(external ip; user name, password;*)

Begin SQL

    execute immediate :sqlVar

End SQL

SQL LOGOUT

It would be so much more efficient to generate SQL statements 
programatically and pass that in to execute in the remote 4D db. Some of 
the tables I am working with have in excess of 150 columns and the 
number of variables are too many for a series of parameters in a SQL 
EXECUTE call. I want to also generate INSERT and UPDATE statements by 
the vars that are modified. So if anyone has a suggestion on this front 
as well, it would be appreciated.


Thanks!

Bill


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Thread_Safe SQL

[Timothy Penner via 4D_Tech]

> 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
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Thread_Safe SQL

[Richard Wright via 4D_Tech]

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 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/ 
> <https://blog.4d.com/begin-end-sql-in-preemptive-mode/>
> 
> -Tim

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Thread_Safe SQL

[Timothy Penner via 4D_Tech]

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 Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Thread_Safe SQL

[Richard Wright via 4D_Tech]

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/SYNCHRONIZE.300-3786772.en.html>.
>  SQL code inserted in Begin SQL/End SQL blocks must comply with the following 
> conditions : 
> - It shall apply to the 4D Server or 4D local base (but not to ODBC). 
> - it cannot reference interprocess variables


However, a method set to Thread-Safe with the following code generates an error 
when I do a syntax check:

Begin SQL
SELECT Company_Name from Customer INTO :$atStr1
    End SQL

The error is: “Cannot us SQL from a method declared as thread-safe.”

The original v17 docs say that you cannot use Begin…End SQL but the v17.2 docs 
clearly say you can with the above restrictions. Has anyone had success getting 
Begin…End SQL to work in a thread-safe method in 17.2?



Richard Wright
DataDomain
rwri...@datadomainsoftware.com <mailto:rwri...@datadomainsoftware.com>


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: SQL - renaming a field

[Chip Scheide via 4D_Tech]

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 redefine the column 
from say text to longint.
I did not try changing the column definition
unused as real  vs  existing data type of text

anyway - that part of the idea is dead.

Thanks!

Chip

On Fri, 26 Jul 2019 13:56:58 -0500, Stephen J. Orth wrote:
> Chip,
> 
> That kind of sucks because doesn't a "drop" destroy data?
> 
> Steve
> 
> -Original Message-
> From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Chip 
> Scheide via 4D_Tech
> Sent: Friday, July 26, 2019 1:46 PM
> To: Tom Benedict 
> Cc: Chip Scheide <4d_o...@pghrepository.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
> 
> 
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL - renaming a field

[Narinder Chandi via 4D_Tech]

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 menu option which gives you the option to rename a table and 
also copies the data for you. This menu option is possible is because in MySQL 
the ALTER TABLE command supports the LIKE keyword, thus allowing you to do 
something like:
CREATE TABLE newTable LIKE oldTable; 
INSERT newTable SELECT * FROM oldTable;

Regards,
 
Narinder Chandi,
ToolBox Systems Ltd.
-- 

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> on behalf of 4D Tech Mailing List 
<4d_tech@lists.4d.com>
Organization: The Aquila Group, Inc.
Reply-To: , 4D Tech Mailing List 
<4d_tech@lists.4d.com>
Date: Friday, 26 July 2019 at 19:56
To: 4D Tech Mailing List <4d_tech@lists.4d.com>, 'Tom Benedict' 

Cc: "Stephen J. Orth" 
Subject: RE: SQL - renaming a field

Chip,

That kind of sucks because doesn't a "drop" destroy data?

Steve

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Chip Scheide via 
4D_Tech
Sent: Friday, July 26, 2019 1:46 PM
To: Tom Benedict 
Cc: Chip Scheide <4d_o...@pghrepository.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.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: SQL - renaming a field

[Stephen J. Orth via 4D_Tech]

Chip,

That kind of sucks because doesn't a "drop" destroy data?

Steve

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Chip Scheide via 
4D_Tech
Sent: Friday, July 26, 2019 1:46 PM
To: Tom Benedict 
Cc: Chip Scheide <4d_o...@pghrepository.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.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL - renaming a field

[Chip Scheide via 4D_Tech]

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-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 via 4D_Tech 
>> <4d_tech@lists.4d.com> wrote:
>> 
>> 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 syntax, or point out 
>> a way to rename an existing field using SQL, -- preferably without 
>> dropping and then adding.
>> 
>> Thanks
>> Chip
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL - renaming a field

[Narinder Chandi via 4D_Tech]

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.
-- 

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> on behalf of 4D Tech Mailing List 
<4d_tech@lists.4d.com>
Reply-To: 4D Tech Mailing List <4d_tech@lists.4d.com>
Date: Friday, 26 July 2019 at 19:30
To: 4D Tech Mailing List <4d_tech@lists.4d.com>
Cc: Chip Scheide <4d_o...@pghrepository.org>
Subject: 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 syntax, or point out 
a way to rename an existing field using SQL, -- preferably without 
dropping and then adding.

Thanks
Chip

---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL - renaming a field

[Tom Benedict via 4D_Tech]

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 via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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 syntax, or point out 
> a way to rename an existing field using SQL, -- preferably without 
> dropping and then adding.
> 
> Thanks
> Chip
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

SQL - renaming a field

[Chip Scheide via 4D_Tech]

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 syntax, or point out 
a way to rename an existing field using SQL, -- preferably without 
dropping and then adding.

Thanks
Chip

---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Error -1 using SQL.

[Chip Scheide via 4D_Tech]

Thanks -
I just found that code in another method I am working with -- copied 
fromsomewhere  :)

Chip

On Thu, 25 Jul 2019 15:43:49 -0400, Chuck Miller via 4D_Tech wrote:
> 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’
> Into 
> :constraintids_al,
> :tablenumbers_al;
> End sql
> Regards 
> 
> Chuck 
> 
> Sent from my iPhone
> 
>> On Jul 25, 2019, at 3:16 PM, Chip Scheide via 4D_Tech 
>> <4d_tech@lists.4d.com> wrote:
>> 
>> 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 ALTER TABLE command.
>> 
>> So... naturally I setup an ON ERR CALL method to catch and report the 
>> problem. However, the error call routine does NOT get either error 43 
>> nor 44, it gets error -1!.
>> 
>> If I remove the ON ERR CALL and generate the error, I get a 4D runtime 
>> error dialog  with error 43 or 44, install ON ERR CALL and get error -1.
>> 
>> Any insights?
>> 
>> Thanks
>> Chip
>> ---
>> Gas is for washing parts
>> Alcohol is for drinkin'
>> Nitromethane is for racing 
>> **
>> 4D Internet Users Group (4D iNUG)
>> Archive:  http://lists.4d.com/archives.html
>> Options: https://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>> **
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Error -1 using SQL.

[Chip Scheide via 4D_Tech]

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 LAST ERROR STACK(codesArray;intCompArray;textArray)
> 
> you will see the error you are expecting listed.
> 
> I’m not sure why the difference.
> 
>> On Jul 25, 2019, at 1:16 PM, Chip Scheide via 4D_Tech 
>> <4d_tech@lists.4d.com> wrote:
>> 
>> 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 ALTER TABLE command.
>> 
>> So... naturally I setup an ON ERR CALL method to catch and report the 
>> problem. However, the error call routine does NOT get either error 43 
>> nor 44, it gets error -1!.
>> 
>> If I remove the ON ERR CALL and generate the error, I get a 4D runtime 
>> error dialog  with error 43 or 44, install ON ERR CALL and get error -1.
>> 
>> Any insights?
> 
> 
> Later,
> Guy
> 
> --
> Guy Algot, Solutions Specialist
> Edmonton, Alberta
> (780) 974-8538
> 
> hardware, installation, training, support, programming, internet
> specializing in 4th Dimension
> =-= =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> "Microsoft is a cross between the Borg and the Ferengi. Unfortunately,
> they use Borg to do their marketing and Ferengi to do their programming."
> -- Simon Slavin
> 
> 
> 
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Error -1 using SQL.

[Guy Algot via 4D_Tech]

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 expecting listed.

I’m not sure why the difference.

> On Jul 25, 2019, at 1:16 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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 ALTER TABLE command.
> 
> So... naturally I setup an ON ERR CALL method to catch and report the 
> problem. However, the error call routine does NOT get either error 43 
> nor 44, it gets error -1!.
> 
> If I remove the ON ERR CALL and generate the error, I get a 4D runtime 
> error dialog  with error 43 or 44, install ON ERR CALL and get error -1.
> 
> Any insights?


Later,
Guy

--
Guy Algot, Solutions Specialist
Edmonton, Alberta
(780) 974-8538

hardware, installation, training, support, programming, internet
specializing in 4th Dimension
=-= =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
"Microsoft is a cross between the Borg and the Ferengi. Unfortunately,
they use Borg to do their marketing and Ferengi to do their programming."
-- Simon Slavin



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Error -1 using SQL.

[Chuck Miller via 4D_Tech]

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’
Into 
:constraintids_al,
:tablenumbers_al;
End sql
Regards 

Chuck 

Sent from my iPhone

> On Jul 25, 2019, at 3:16 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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 ALTER TABLE command.
> 
> So... naturally I setup an ON ERR CALL method to catch and report the 
> problem. However, the error call routine does NOT get either error 43 
> nor 44, it gets error -1!.
> 
> If I remove the ON ERR CALL and generate the error, I get a 4D runtime 
> error dialog  with error 43 or 44, install ON ERR CALL and get error -1.
> 
> Any insights?
> 
> Thanks
> Chip
> ---
> Gas is for washing parts
> Alcohol is for drinkin'
> Nitromethane is for racing 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Error -1 using SQL.

[Chip Scheide via 4D_Tech]

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 ALTER TABLE command.

So... naturally I setup an ON ERR CALL method to catch and report the 
problem. However, the error call routine does NOT get either error 43 
nor 44, it gets error -1!.

If I remove the ON ERR CALL and generate the error, I get a 4D runtime 
error dialog  with error 43 or 44, install ON ERR CALL and get error -1.

Any insights?

Thanks
Chip
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Tom Benedict via 4D_Tech]

I tried this a number of years ago and was not successful. I don’t recall the 
details though. It may be a limit in the ODBC driver or SSMS. You can update 
records from other apps via ODBC though. 

Hopefully there will be other, more definitive, responses.

Tom Benedict

> On Jul 13, 2019, at 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 created a Linked
> Server using that ODBC data source. I can successfully query 4D records via
> that Linked Server, using OPENQUERY, but I can't UPDATE or DELETE those same
> 4D records.
> 
> This outputs the expected 4D data:
> 
> SELECT * FROM OPENQUERY([4DTest2], 'SELECT UserID, FirstName, LastName FROM
> [Users] WHERE UserID = 23990')
> 
> This gets an error:
> 
> UPDATE OPENQUERY([4DTest2], 'SELECT UserID, FirstName, LastName FROM [Users]
> WHERE UserID = 23990') SET FirstName = 'Doug'
> 
> The error is:
> 
> OLE DB provider "MSDASQL" for linked server "4DTest2" returned message "Data
> provider or other service returned an E_FAIL status.". Msg 7343, Level 16,
> State 4, Line 2 The OLE DB provider "MSDASQL" for linked server "4DTest2"
> could not UPDATE table "[MSDASQL]".
> 
> 
> 
> --
> 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: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[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 via
that Linked Server, using OPENQUERY, but I can't UPDATE or DELETE those same
4D records.

This outputs the expected 4D data:

SELECT * FROM OPENQUERY([4DTest2], 'SELECT UserID, FirstName, LastName FROM
[Users] WHERE UserID = 23990')

This gets an error:

UPDATE OPENQUERY([4DTest2], 'SELECT UserID, FirstName, LastName FROM [Users]
WHERE UserID = 23990') SET FirstName = 'Doug'

The error is:

OLE DB provider "MSDASQL" for linked server "4DTest2" returned message "Data
provider or other service returned an E_FAIL status.". Msg 7343, Level 16,
State 4, Line 2 The OLE DB provider "MSDASQL" for linked server "4DTest2"
could not UPDATE table "[MSDASQL]".



--
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: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[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 queries to work there, then it’s 
much easier to write them in 4D. Also, there may be stored procedures exposed 
for your use.

To connect you would set up an ODBC data source on your machine to connect to 
the SQL Server then use the 4D SQL commands connect to it. If you’re using 4D 
64bit client, use the 64 bit version of ODBC Data Source administrator app 
(it's in Windows Settings) otherwise use 32bit version. The 4D documentation is 
pretty good on the whole ODBC and the SQL LOGIN command.

Let me know if you have further questions as you proceed.

HTH,

Tom Benedict

> On Jul 1, 2019, at 13:31, ernest hilgers via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> 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 which I found on the disk and is called 
> : SMSDB.dat
> 
> I have a discription of the tables (files) used including the field.names 
> (the most weird naming system). 
> 
> Pointers how to access this data would be very welcome if anyone has any 
> experience with this datafile. 
> 
> greetings
> 
> ernie hilgers (aruba)
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

information needed using sql on a database called smsdb.dat

[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 which I found on the disk and is called : 
SMSDB.dat

I have a discription of the tables (files) used including the field.names (the 
most weird naming system). 

Pointers how to access this data would be very welcome if anyone has any 
experience with this datafile. 

greetings

ernie hilgers (aruba)

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

sql issue

[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].[LSS_InventoryId_s]
having
[LSS_Inspection].[LSS_InspectionDate_d] =
(SELECT MAX([LSS_Inspection].[LSS_InspectionDate_d])
from [LSS_Inspection])

into
:$INVIDs_atxt;
End SQL
whether I dfo it as above or like this
Begin SQL
select
[LSS_Inventory].[LSS_InventoryId_s]
from
[LSS_Inventory] ,
[LSS_Inspection]
where
[LSS_Inspection].[LSS_CondFoundationCurr_s] = '2'

having
[LSS_Inspection].[LSS_InventoryId_s] = [LSS_Inventory].[LSS_InventoryId_s]
and
[LSS_Inspection].[LSS_InspectionDate_d] =
(SELECT MAX([LSS_Inspection].[LSS_InspectionDate_d])
from [LSS_Inspection])

into
:$INVIDs_atxt;
End SQL

I get 0 results, The windows for join and having processing show but
nothing comes back
Thanks and Regards

chuck
-- 
-
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 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 confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution,
disclosure, printing, copying, storage, modification or the taking of
any action in reliance upon this transmission is strictly prohibited.
Delivery of this message to any person other than the intended
recipient shall not compromise or waive such confidentiality,
privilege or exemption from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: sql assistance

[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
> 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 can be done, but it has been a long time since I
> coded anything but really basic sql
>
> Thanks and regards
>
>
> Chuck
> --
> -
>  Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
>  Informed Solutions, Inc.
>  Brookline, MA 02446 USA Registered 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 confidential, subject to privilege or exempt from disclosure under
> applicable law.  These materials are intended only for the use of the
> intended recipient. If you are not the intended recipient of this
> transmission, you are hereby notified that any distribution,
> disclosure, printing, copying, storage, modification or the taking of
> any action in reliance upon this transmission is strictly prohibited.
> Delivery of this message to any person other than the intended
> recipient shall not compromise or waive such confidentiality,
> privilege or exemption from disclosure as to this communication.



-- 
-
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 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 confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution,
disclosure, printing, copying, storage, modification or the taking of
any action in reliance upon this transmission is strictly prohibited.
Delivery of this message to any person other than the intended
recipient shall not compromise or waive such confidentiality,
privilege or exemption from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

sql assistance

[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 this can be done, but it has been a long time since I
coded anything but really basic sql

Thanks and regards


Chuck
-- 
-
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 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 confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution,
disclosure, printing, copying, storage, modification or the taking of
any action in reliance upon this transmission is strictly prohibited.
Delivery of this message to any person other than the intended
recipient shall not compromise or waive such confidentiality,
privilege or exemption from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D Selection from SQL Query

[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 might come in handy at some 
point.

Thanks again,

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 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 
<4d_tech@lists.4d.com<mailto:4d_tech@lists.4d.com>>のメール:

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



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D Selection from SQL Query

[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 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 
<4d_tech@lists.4d.com<mailto:4d_tech@lists.4d.com>>のメール:

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



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D Selection from SQL Query

[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 
<4d_tech@lists.4d.com<mailto:4d_tech@lists.4d.com>>のメール:

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



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D Selection from SQL Query

[Randy Engle via 4D_Tech]

Chuck,

Many thanks!  ( I hunched that I'd hear from you on this one. )

It is as I expected:   ce n'est pas possible
Except as you showed in your example
(Which has a little bit of merit for what I'm thinking about doing)

Thanks!

😉

Randy Engle



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 on the fly


BTW not creating a selection of records and not loading one is one of the 
beautiful parts of sql as you do nto need to muck with the current selection



--
-
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064  Informed Solutions, 
Inc.
 Brookline, MA 02446 USA Registered 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 
confidential, subject to privilege or exempt from disclosure under applicable 
law.  These materials are intended only for the use of the intended recipient. 
If you are not the intended recipient of this transmission, you are hereby 
notified that any distribution, disclosure, printing, copying, storage, 
modification or the taking of any action in reliance upon this transmission is 
strictly prohibited.
Delivery of this message to any person other than the intended recipient shall 
not compromise or waive such confidentiality, privilege or exemption from 
disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D Selection from SQL Query

[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 on the fly


BTW not creating a selection of records and not loading one is one of
the beautiful parts of sql as you do nto need to muck with the current
selection

Regards

Chuck

On Tue, Feb 5, 2019 at 12:56 PM Randy Engle via 4D_Tech
<4d_tech@lists.4d.com> wrote:
>
> 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...ing into the wind?
>
> Thanks!
>
> Randy Engle
>
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity
  http://www.informed-solutions.com
-
This message and any attached documents contain information which may
be confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution,
disclosure, printing, copying, storage, modification or the taking of
any action in reliance upon this transmission is strictly prohibited.
Delivery of this message to any person other than the intended
recipient shall not compromise or waive such confidentiality,
privilege or exemption from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

4D Selection from SQL Query

[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 I p...ing into the wind?

Thanks!

Randy Engle


**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL query for the current date

[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 return a TIMESTAMP, which may 
> or may not match a native date value.
> 
> for that reason, a C_DATE FN wrapper method seems inevitable if you want to 
> query the database for date values using SQL.

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL query for the current date

[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
WHERE  {FN CD() AS TIMESTAMP} = Field_2
INTO :$ad;

End SQL

or

ARRAY DATE($ad;0)

Begin SQL

SELECT Field_2 from Table_1*/
WHERE Field_2 = {FN CD() AS TIMESTAMP}
INTO :$ad;

End SQL

either way, the method is called for all records in table.

if, for example we use a subquery:

ARRAY DATE($ad;0)

Begin SQL

SELECT Field_2 from Table_1
WHERE Field_2 = (SELECT {FN CD() AS TIMESTAMP} FROM Table_1 LIMIT 1)
INTO :$ad;

End SQL

then of course CD() is called only once.

by the way, SQL does not support native date (only) type except when cast from 
a 4D expression or literals.

http://doc.4d.com/4Dv15/4D/15/4D-SQL-engine-implementation.300-2288119.en.html

{ d '2013-10-02' }
{ t '13:33:41' }
{ ts '1998-05-02 01:23:56.123' }

SQL functions such as CURRENT_DATE() seems to return a TIMESTAMP, which may or 
may not match a native date value.

for that reason, a C_DATE FN wrapper method seems inevitable if you want to 
query the database for date values using SQL.

> 2018/12/01 4:00、Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com>のメール:
>
> 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 scan of the Invoices table, even though 
> Invoices.CreatedData is indexed. This takes a very long time - probably close 
> to a half an hour.
>
> If I rewrite the where clause as:
>
>  WHERE CreatedDate='2018-11-30'
>
> ... it takes milliseconds.
>
> Is there a trick to being able to write a script that can generically select 
> against the current date?  This is from some php code, fyi... but it's easily 
> reproduced inside of 4D itself.




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

SQL query for the current date

[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 scan of the Invoices table, even though 
Invoices.CreatedData is indexed. This takes a very long time - probably close 
to a half an hour.

If I rewrite the where clause as:

  WHERE CreatedDate='2018-11-30'

... it takes milliseconds.

Is there a trick to being able to write a script that can generically select 
against the current date?  This is from some php code, fyi... but it's easily 
reproduced inside of 4D itself.


Thanks!
Jeff
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: V15 and SQL

[Robert McKeever via 4D_Tech]

Unfortunately, adding ‘ALTER DATABASE ENABLE TRIGGERS’ to the server startup 
routine did nothing.

> On Nov 18, 2018, at 9:50 PM, Keisuke Miyako via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> could it be possible that triggers are disabled by code?
> 
> http://doc.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 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?
> 
> 
> 
> 
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

_
Bob McKeever  http://www.mswl.com <http://www.mswl.com/>
McKeever's Software Wizardry
Port Coquitlam, B.C.
bobmckee...@mac.com




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL TRIM

[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 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 heeft Jim Medlen via 4D_Tech <
> 4d_tech@lists.4d.com> het volgende geschreven:
> >
> >
> > 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 :vText ;
> > End SQL
> > ALERT(vText)
> > **
> > 4D Internet Users Group (4D iNUG)
> > Archive:  http://lists.4d.com/archives.html
> > Options: https://lists.4d.com/mailman/options/4d_tech
> > Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> > **
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity
  http://www.informed-solutions.com
-
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: V15 and SQL

[Keisuke Miyako via 4D_Tech]

could it be possible that triggers are disabled by code?

http://doc.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 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?




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL TRIM

[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 heeft Jim Medlen via 4D_Tech <4d_tech@lists.4d.com> 
> het volgende geschreven:
> 
> 
> 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 :vText ;
> End SQL
> ALERT(vText)
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

SQL TRIM

[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 :vText ;
End SQL
ALERT(vText)
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

V15 and SQL

[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?

_
Bob McKeever  http://www.mswl.com <http://www.mswl.com/>
McKeever's Software Wizardry
Port Coquitlam, B.C.
bobmckee...@mac.com




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Tableau connecting to SQL server of 4D

[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 with the WDC, using the examples.

Thanks,
Will
wtay...@4d.com






**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Tableau connecting to SQL server of 4D

[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: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL Convert problem

[vagelis fallias via 4D_Tech]

 It worked , Thank you
Στις Δευτέρα, 29 Οκτωβρίου 2018, 1:51:45 μ.μ. GMT-4, ο χρήστης Charles 
Miller  έγραψε:  
 
 Try cast.
Chuck 
On Mon, Oct 29, 2018 at 12:37 PM vagelis fallias via 4D_Tech 
<4d_tech@lists.4d.com> wrote:

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 is with the 
convert, any ideas as to what the problem might be, what changed?

Thanks
Van Fallias
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity          
http://www.informed-solutions.com  
-
This message and any attached documents contain information which may be 
confidential, subject to privilege or exempt from disclosure under applicable 
law.  These materials are intended only for the use of the intended recipient. 
If you are not the intended recipient of this transmission, you are hereby 
notified that any distribution, disclosure, printing, copying, storage, 
modification or the taking of any action in reliance upon this transmission is 
strictly prohibited.  Delivery of this message to any person other than the 
intended recipient shall not compromise or waive such confidentiality, 
privilege or exemption from disclosure as to this communication.   
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: SQL Convert problem

[Charles Miller via 4D_Tech]

Try cast.

Chuck

On Mon, Oct 29, 2018 at 12:37 PM vagelis fallias via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> 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 is with
> the convert, any ideas as to what the problem might be, what changed?
>
> Thanks
> Van Fallias
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity
  http://www.informed-solutions.com
-
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

SQL Convert problem

[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 is with the 
convert, any ideas as to what the problem might be, what changed?

Thanks
Van Fallias
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[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: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

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

[Chip Scheide via 4D_Tech]

I would, but could not find it...
all I have/can find is in Japanese.  My wife could make sense of it, 
but unfortunately not me  :)

On Mon, 17 Sep 2018 18:47:52 -0400, Charles Miller via 4D_Tech wrote:
> 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-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));
>> 
>> 
>> On Mon, 17 Sep 2018 17:10:56 -0400, Chuck Miller via 4D_Tech wrote:
>>> 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 sql_name |
>>> [{ENABLE | DISABLE} REPLICATE] |
>>> [{ENABLE | DISABLE} AUTO_INCREMENT] |
>>> [{ENABLE | DISABLE} AUTO_GENERATE] |
>>> SET SCHEMA sql_name}
>>> 
>>> Chuck
>>> 
>> 
>> 

>>>  Chuck Miller Voice: (617) 739-0306
>>>  Informed Solutions, Inc. Fax: (617) 232-1064
>>>  mailto:cjmillerinformed-solutions.com
>>>  Brookline, MA 02446 USA Registered 4D Developer
>>>Providers of 4D and Sybase connectivity
>>>   http://www.informed-solutions.com
>>> 
>> 
>> 

>>> This message and any attached documents contain information which may
>>> be confidential, subject to privilege or exempt from disclosure under
>>> applicable law.  These materials are intended only for the use of the
>>> intended recipient. If you are not the intended recipient of this
>>> transmission, you are hereby notified that any distribution,
>>> disclosure, printing, copying, storage, modification or the taking of
>>> any action in reliance upon this transmission is strictly
>>> prohibited.  Delivery of this message to any person other than the
>>> intended recipient shall not compromise or waive such
>>> confidentiality, privilege or exemption from disclosure as to this
>>> communication.
>>> 
>>>> On Sep 17, 2018, at 3:48 PM, Chip Scheide via 4D_Tech
>>>> <4d_tech@lists.4d.com> wrote:
>>>> 
>>>> $SQl_Command:="ALTER TABLE [Anmlprot_File_Link] ADD PRIMARY KEY
>>>> (Journal_Key)"
>>> 
>>> **
>>> 4D Internet Users Group (4D iNUG)
>>> Archive:  http://lists.4d.com/archives.html
>>> Options: https://lists.4d.com/mailman/options/4d_tech
>>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>>> **
>> ---
>> Gas is for washing parts
>> Alcohol is for drinkin'
>> Nitromethane is for racing
>> **
>> 4D Internet Users Group (4D iNUG)
>> Archive:  http://lists.4d.com/archives.html
>> Options: https://lists.4d.com/mailman/options/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 & SQL Server connectivity
>   http://www.informed-solutions.com
> 
-
> This message and any attached documents contain information which may be
> confidential, subject to privilege or exempt from disclosure under
> applicable law.  These materials are intended only