go to post David Van De Griek · Apr 14, 2023 If an upgrade is possible, the version of LOAD DATA in IRIS 2022.3 has many bug fixes and improvements.
go to post David Van De Griek · Apr 14, 2023 Just a note... LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, This field will only define the LAST_UPDATED field at INSERT, not for an UPDATE. You probably want something like: LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL, This will cause LAST_UPDATED to be set at INSERT and UPDATE to the current timestamp to microsend precision.
go to post David Van De Griek · Apr 12, 2023 You also need to merge over the index entry in ^rINDEXCLASS($$$UPPER(classname)) to the new namespace. Don't forget to move all related classes such as Serial classes, datatype classes, super classes, references, etc.
go to post David Van De Griek · Nov 9, 2020 Daniel, I guess it looks like we don't support CodeMode = generator for overriding property methods. CodeMode = code should work, but you need to access type parameters from the property definition in order to generate the proper code. I believe you will need to extend the %String datatype and override Normalize, then use the new datatype class in your property definition. Here is an example: Class User.StringoNoEmpty Extends %String [ Language = objectscript ] { ClassMethod Normalize(%val As %RawString) As %String [ CodeMode = generator, ServerOnly = 1 ] { { set code="%val" if %parameter("TRUNCATE"),%parameter("MAXLEN")'="" { set code="$e(%val,1,"_(+%parameter("MAXLEN"))_")" } $$$GENERATE(" RETURN $tr("_code_",$c(0),"""")") RETURN $$$OK} } Property myString As User.StringoNoEmpty(MAXLEN = 54, TRUNCATE = 1); Hope this helps, -dave
go to post David Van De Griek · Nov 4, 2020 $c(0) is the internal representation of the SQL empty string ''. Your insert must be using '' as the value for myProperty instead of null if you are seeing $c(0) for the field values. SQL null and empty-string string are very different. null means unknown, while empty string is a 0 length string value. If you always want to convert '' to null for myProperty values, write a myPropertyNormalize property member method that converts the value to null. This will override the %String Normalize() method and convert $c(0) to "" whenever the property/field's value is normalized by the filers. Trying to do this in a trigger will not work as triggers are not meant to be used to modify property/field values being filed.
go to post David Van De Griek · Sep 4, 2020 Can you be a little more specific? Are you are asking about embedded SQL, dynamic sql, ODBC, JDBC, or limits for each interface? The answer is different for each, although I'm not sure there is an exact length limit. For embedded SQL, I don't believe there is any limit other than the maximum size of the code you can define for a .MAC routine or class method. I believe for dynamic SQL the max length should be pretty close to the maximum string length supported by your Caché instance. I was able to Prepare a using %SQL.Statement that was a bit over 3,600,000 characters in length (using IRIS, I don't have immediate access to a Caché instance))
go to post David Van De Griek · Feb 19, 2020 Hi Suman, For the case where the ID is not system generated, try the following query after a successful INSERT: SELECT LAST_IDENTITY() You can actually do this for the case where the ID is system generated also, but in that case getGeneratedKeys is probably a better option. LAST_IDENTITY() simply returns the value of the %ROWID variable, which is set after a successful INSERT to the value of the rowid field.
go to post David Van De Griek · Mar 15, 2019 $System.SQL.TODATE() returns a %Date (+$Horolog) value.The second argument to TODATE() describes the input format of the string-date value. In your example, you passed in a MM/DD/YYYY value but told TODATE the format was YYYY-MM-DD.set p="12/03/2019" write $System.SQL.TODATE(p,"DD/MM/YYYY")Would have returned a logical %Date value
go to post David Van De Griek · Mar 6, 2019 Try this:CREATE TABLE test ( identifier VARCHAR(200) NOT NULL, value INTEGER COMPUTEONCHANGE("%%INSERT","%%UPDATE") COMPUTECODE { if %oper="INSERT" { set {*}=1 } elseif %oper="UPDATE" { set {*}={value}+1 } }, PRIMARY KEY (identifier))[SQL]SQL:USER>>select identifier, value from testidentifier value0 Rows(s) Affected[SQL]SQL:USER>>insert or update test (identifier) values ('row 1')1 Row Affected[SQL]SQL:USER>>insert or update test (identifier) values ('row 1')1 Row Affected[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')1 Row Affected[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')1 Row Affected[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')1 Row Affected[SQL]SQL:USER>>insert or update test (identifier) values ('row 3')1 Row Affected[SQL]SQL:USER>>select identifier, value from test identifier valuerow 1 2 row 2 3 row 3 1 3 Rows(s) Affected
go to post David Van De Griek · Mar 5, 2019 1) Primary Key in Caché / IRIS is useful in that you can expose to third-party tools (typically through xDBC catalog queries) what the Primary Key is for your table. Other than that, it has no special meaning to us.2) You can define bitmap indices using Index1 if you tweak the property definition of Identifier to tell the system the value is a positive integer: Property Identifier As %Integer(MINVAL=1)
go to post David Van De Griek · Aug 7, 2018 Once you have the $list value in dat, use $listnext rather than a loop with $listget. For example: set ptr = 0 while $listnext(dat,ptr,value) { <do something with your value in value> }
go to post David Van De Griek · Aug 13, 2017 $system.SQL.TODATE() converts a readable format into a logical %Date value (+$Horolog). $system.SQL.TODATE() does support converting from Julian date to %Date if you use the "J" format. However, there is one issue; the value is returned in YYYY-MM-DD format for dates prior to 1841-01-01. I will make a note to correct this. USER>for Julian=2393465:1:2393475 w !,$SYSTEM.SQL.TODATE(Julian,"J") 1840-12-25 1840-12-26 1840-12-27 1840-12-28 1840-12-29 1840-12-30 0 1 2 3 4 "J"ulain format is also supported for $SYSTEM.SQL.TOCHAR(): USER>set string="2017-08-13" write !,$SYSTEM.SQL.TOCHAR(string,"J") 2457979
go to post David Van De Griek · Sep 21, 2016 This same issue was reported a week or two ago to the WRC.Purging cached queries should clear the issue.It appears to be an issue where the server gets into a state where the cached query class gets an error during creation (that is not reported) and the class is created without any methods. I believe that customer was on version 2014.1. Can you please let us know what version you saw this problem on?Also, if you can reproduce the issue, please contact the WRC with the details. Thank you.Also, %SYS.SQLSRV is a routine, not a class, which is why you could not find information on it in documatic.
go to post David Van De Griek · Aug 23, 2016 Is is possible you have a version mismatch here?There is a new parameter for %Library.Integer called STRICT, but I believe this is only in version 2016.2 and up.I don't have much knowledge about mirroring, but if your main system was a 2016.2 field test version, and your mirrored system was 2016.1.2, the 2016.1.2 system would not recognize STRICT as being a valid type parameter for %Integer properties.
go to post David Van De Griek · Aug 1, 2016 Another reason for using class queries would be security. You could grant Execute privilege to a user/role on the stored procedure projected by the class query, and the user could access the data returned by the query without having to grant Select privilege on the table's involved in the class query.
go to post David Van De Griek · Jul 27, 2016 Perhaps I'm not fully understanding what you are asking for, but why doesn't using a string datatype work for you?SAMPLES>>create table kev (mynum varchar(10))1. create table kev (mynum varchar(10))0 Rows Affectedstatement prepare time(s)/globals/lines/disk: 0.0084s/2003/16386/0ms execute time(s)/globals/lines/disk: 0.0838s/46277/360501/0ms---------------------------------------------------------------------------SAMPLES>>insert into kev (mynum) values ('300.6000')2. insert into kev (mynum) values ('300.6000')1 Row Affectedstatement prepare time(s)/globals/lines/disk: 0.0108s/2772/28131/0ms execute time(s)/globals/lines/disk: 0.0002s/13/153/0ms---------------------------------------------------------------------------SAMPLES>>insert into kev (mynum) values ('200.100')3. insert into kev (mynum) values ('200.100')1 Row Affectedstatement prepare time(s)/globals/lines/disk: 0.0011s/49/2516/0ms execute time(s)/globals/lines/disk: 0.0007s/3/153/0ms---------------------------------------------------------------------------SAMPLES>>insert into kev (mynum) values ('643.1230')4. insert into kev (mynum) values ('643.1230')1 Row Affectedstatement prepare time(s)/globals/lines/disk: 0.0006s/49/2526/0ms execute time(s)/globals/lines/disk: 0.0001s/3/153/0ms---------------------------------------------------------------------------SAMPLES>>select mynum from kev5. select mynum from kevmynum300.6000200.100643.12303 Rows(s) Affectedstatement prepare time(s)/globals/lines/disk: 0.0622s/45809/254912/0ms execute time(s)/globals/lines/disk: 0.0002s/16/523/0ms---------------------------------------------------------------------------SAMPLES>>cos zw ^User.kevD^User.kevD=3^User.kevD(1)=$lb("300.6000")^User.kevD(2)=$lb("200.100")^User.kevD(3)=$lb("643.1230")
go to post David Van De Griek · Jul 27, 2016 If you are on an older system where INFORMATION_SCHEMA is not available, you can use the %Library.SQLCatalog class queries. Here is an example: #include %occStatus new stmt,sc,rset,handle set stmt=##class(%SQL.Statement).%New() set sc=stmt.%PrepareClassQuery("%Library.SQLCatalog","SQLTables") if $$$ISERR(sc) { write $System.Status.DisplayError(sc) QUIT } set rset=stmt.%Execute() do rset.%Display() QUIT
go to post David Van De Griek · Apr 27, 2016 Hi Andy,Have the tables used by the query been tuned? If not, I would suggest tuning the tables.In the Management Portal, System Explorer -> SQL, switch to the proper namespace and then enter the query text in the Execute Query tab and press the Show Plan button. Now do the same with the query that selects just a single column and compare the query plans. Are they different? If they are different, post the query plans here so we can have a look.Possible other issues:- Are any of the fields in the multi-field select list computed, and could the computation be taking a long time?- Is the table using default storage, or %CacheSQLStorage? If %CacheSQLStorage, do any of the fields in the multi-select field list have expensive data retrieval logic?- Are the tables in the from clause all base tables, or are views involved? - Do the fields in the select list come from the same table?
go to post David Van De Griek · Feb 3, 2016 There are a couple of other options for this topic. If you are simply wanting the ability to define the name of the "ID" field yourself, you can use the class keyword SqlRowidName. For example: SqlRowIdName = PersonId For an Identity property, there is a way to allow a process to explicitly specify values for the identity property/field. This is typically useful when first populating a table/object from an external source and you want to retain the identity's values. The call is: set oldValue = $SYSTEM.SQL.SetIdentityInsert(1,.sc) For more information on the $SYSTEM.SQL.SetIdentityInsert call see class documentation for the %SYSTEM.SQL class. Here is an example:
