Hi
I would like to save a group of longs in a string, in as SQL table.
I use the STRING to store all sorts of data. Plain text, numbers and hopefully also a groups.
Is it possible?
The code below is for explanation only, not real code.
TestGroup GROUP
A LONG
B LONG
C LONG
END
TestGroup.A = 1
TestGroup.B = 2
TestGroup.C = 3
SQL:STRING = TestGroup
ACCESS:INSERT.SQLtable()
ACCESS:FETCH.SQLtable()
TestGroup=SQL:STRING
TestGroup doesn’t contain the same values as before inserted.
Is the SQL ‘string’ of the correct length to hold 3 LONGs ie 12 characters.
Is the SQL ‘string’ a fixed length datatype eg CHAR in SQL Server and not a VARCHAR or NCHAR (Unicode)
Why would you want to do this, you’re sidestepping all the data validation available on the backend.
You might need the BINARY switch on the NAME attribute of your STRING column. e.g. NAME(‘YourColumn |BINARY’)
From the help under the “SQL Column Properties” topic as found when searching for “BINARY driver switch”:
BINARY
Adding the BINARY switch to the External Name tells the driver to store the data in binary format. This is useful when storing images or non-printable characters. Valid only with STRING data types, and all SQL drivers (except Oracle).
@Graham_Dawson
I’m developing a project where I’m collecting all kind of informations from all kinds of sources. So I need to make it as flexible as possible.
@jslarve
Exactly what I’m looking for.
But I get the error below when I try to create the table.
Can’t figure out what I’m doing wrong.
Hi Niels,
I tested this using SQL Server with a CHAR(12) field in the backend table and it all worked perfectly (without needing the BINARY switch)
I tested with normal numbers and with minimum and maximum values for LONGs ie values way outside normal characters - it all still worked.
What database are you using?
Could you post the table declaration both backend and Clarion.
@Graham_Dawson
In Clarion my Table Field is a STRING(256) and in MSSQL my field is a CHAR(256):
The length is 256 is because it will contain all sorts of Clarion structures. Txt, Groups, dates decimal ect.
You need to post the complete declaration of the backend table.
How are you creating it?
Would be nice to see the Clarion definition too.
This is my test code that works for me…
PROGRAM
MAP
END
glo:owner cstring(129)
theFile file,driver('MSSQL'),owner(glo:owner),create
record RECORD
ID long
theString string(256)
end
end
testGroup GROUP
a LONG
b LONG
c LONG
end
CODE
glo:owner = 'LENOVO-PC\SQLEXPRESS,Test,sa,sdm2010'
create(theFile)
open(theFile)
if errorcode()
message(choose(errorcode()=90,fileerror(),error()),'On Open theFile')
end
testGroup.a = 999
testGroup.b = 555
clear(testGroup.c,-1)
theFile.ID = 1
theFile.theString = testGroup
add(theFile)
if errorcode()
message(choose(errorcode()=90,fileerror(),error()),'On Open theFile')
end
close(theFile)
clear(testGroup)
testGroup.a = 1
testGroup.b = 9
testGroup.c = 6666
open(theFile)
next(theFile)
testGroup = theFile.theString
message('A ' & testGroup.a & '|B ' & testGroup.b & '|C ' & testGroup.c)
close(theFile)
I recently encountered a similar requirement, and decided to instead go with JSON inside a BLOB.
The advantages of this approach are;
a) the data stored can be different from one row to the next, but it is still structured. Meaning that individual parts can be read, new parts added and so on, all with maximum flexibility.
b) Although I’m using a BLOB, some backends are supporting JSON fields - so it’s possible the backend could then also read the data. Doing so is beyond my paygrade (I’ll leave that to the SQL folks) but certainly the door is open to that.
c) There are no issues when upgrading tables. (Using Strings and OVER makes upgrading really tricky)
d) The field itself remains as Text, meaning it can be inspected easily, and debugging (outside the program itself) is easy.
There are some disadvantages;
a) BLOB support inside Clarion is limited. (I wrote MyTable to take care of this - which includes using the blob data fields on Browses and forms.)
b) It takes “longer” to parse JSON than it does to simple OVER a string. However the time is short compared to the time it takes to actually read the database. So performance lost is minimal, and inconsequential.
Cheers
Bruce
@Graham_Dawson
I started from scratch and now it works for me too. Thanks
@Bruce
Now I finally took the time to read the documentation and watch the webinar for MyTable. And that’s definitely the way I should go. It gives me the flexibility I am looking for.
However, much of the data I receive comes as XML. Is there an easy way to convert to JSON?
I do this all the time. If you look at the way SQL date/times are handled in Clarion you will get the idea…
If you import a date (e.g. - ORDER_DATE) you will see the following structure in the dictionary:
ORDER_DATE STRING(8)
ORDER_DATE_GROUP GROUP,OVR(ORDER_DATE)
ORDER_DATE_DATE LONG
ORDER_DATE_TIME LONG
END !ORDER_DATE_GROUP
You can expand on this idea as much as you want; by making the string length 20, you could define 5 longs. On one project, I create a user-defined screen generator that allowed the user to define a data entry screen. I used a MEMO field to store the data from the screen. The user could mix all data types within the MEMO field. Worked great…
Id suggest storing these values using XML or JSON in your string. Much easier to manage.
XML or JSON should work about the same. I should note that there is also an intermediate approach. On the very structured end you have fixed columns and whatever data arrives has to be put into those fixed columns. If you get something that needs a new column it’s a bit of a process to add to your data model. On the unstructured end you have throwing the xml or json you retrieve into the database like it is and searching for stuff is like rummaging in a load of bags that could contain anything. In between is the Entity-Attribute-Value (EAV) model, where you have a table that has a column that says what the attribute belongs to (which bag it came in), the attribute type (which you would get from the json/xml tag), and the value (which would normally be stored in a string variable). It’s no trouble to add a new attribute (it’s just a different label value in the attribute column). It’s a little more trouble to load the data in, but it’s much quicker to get the data out, though not as quick as the totally structured model. Some databases (like Oracle) also allow explicitly declaring columns as XMLTYPE, which can speed up searching too.
Could representing ‘random’ bytes as a string, result in the equivalent of a SQL Injection problem?
Does the ,BINARY switch solve that ?