Solved

Saving  values into a Blob (text field)

Posted on 2015-01-25
5
171 Views
Last Modified: 2015-06-02
We are using classic ASP/VB script and passing some values from our database to a different system.
To do this we declare variables and assign the value to them, then pass them to the other system. So far works great.

Problem is from the other system it sends us back two values. the name of the 'field' and the 'value'

Say for example.

name: address1
value:  street name goes here

name: zipcode1
value:  33044

and so on, they can be 200 values. We need to save the name of the field AND the value in one text field in the database, so that later on we can parse it and send it back to the system again.

I have no clue on how to concatenate this values and save them as one big Blob into the text field of our Blobtbl
And then the second step how to un-concatenate them so that they can be passed back again like we do with the other variables. Right now we do it like:

<%
Dim ownFName
ownFName= (rs_Beneficiary.Fields.Item("FirstNm").Value)
Dim ownLName
ownLName= (rs_Beneficiary.Fields.Item("LastNm").Value)
Dim ownMName
ownMName= (rs_Beneficiary.Fields.Item("MiddleNm").Value)
Dim ownDOB
ownDOB= (rs_Beneficiary.Fields.Item("Dob").Value)
Dim ownCitizenship
ownCitizenship= (rs_Beneficiary.Fields.Item("ResCntry").Value)
%>

But those are for variables we KNOW the names and values, the ones coming back from the other system may be 10 or 100 variable names with their values. So on our end we need to un-concatenate each one and separate them as above so that we can send them back later on.

I hope I make sense. We are using MS SQL 2008 if it makes any difference.
0
Comment
Question by:amucinobluedot
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 333 total points
ID: 40570361
If the original data is coming in as some kind of key-value pairs, storing it all in a text blob (e.g. varchar(max)) is really not a very nice solution.  Is the raw data XML?  JSON?  XML has its own data type in SQL Server 2008.

If, however, you really must concatenate the values then something like this could work.

Dim MyBigText
for i = 0 to rs_Beneficiary.fields.count -1
  MyBigText = MyBigtext & rs_Beneficiary.fields(i).name & ":" & rs_Beneficiary.fields(i).value & vbcrlf
next i

Now you have a monster string you can store the way you usually store your data.
0
 

Author Comment

by:amucinobluedot
ID: 40570413
What would be a different solution to save all this data to my database to then later on retrieve it and send it back ?
0
 
LVL 32

Assisted Solution

by:Big Monty
Big Monty earned 167 total points
ID: 40570717
I agree using a BLOB field isn't the way to go. If all you're storing is pieces of text, either store it in a TEXT field or parse out the name/values before saving it to the database in it's own table (this is the way I would go). BLOB fields are for storing files directly to the database.

To elaborate on this, you'll need to create an associative table, a table where you can store data and then associate some kind of ID that'll link that data to another tables record. Without knowing your exact needs, I'll just give you this example.

Say you have a PAGE table, and you want to be able to associate a dynamic number of fields to that table. In your PAGE table, you have a PageID that is your primary key, and then you have all of your other basic data for the page. Next, create a table called FIELDS, that would look something like this:

create table Fields {
    fieldID int IDENTITY(1,1),
    fieldName varchar(50),
    fieldValue varchar(50), 
    pageID int
}

Open in new window


Here, you'll see that I created a table called FIELDS where you can now store all of the fields that you get back from the other service, all you have to do is associate the pageID with it.

parsing the data is relatively easy as well, assuming you're getting them as a POST or GET (if it's another way, we'll need to tweak the code below):

dim fldsFrom3rdParty
fldsFrom3rdParty = Request.QueryString           '-- change to Request.Form if the data is coming via POST

for each fldName in fldsFrom3rdParty
     fldValue = Request( fldName )
     '-- build sql to insert data into FIELDS table, then do the insert
next

Open in new window


if you have any questions on how this code works, feel free to ask!
0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 333 total points
ID: 40570759
Big Monty's solution -- entity-attribute-value -- appears very appropriate.
0
 

Author Comment

by:amucinobluedot
ID: 40570768
I did mean a text field  my bad. Thanks for the above info. I will test and let you know how that worked ... then I will have to read it and parse it back to an xml to then send it to the other system. BUt Ill get to that later, first I have to save it.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to dynamically set the form action using jQuery.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now