Basic SQL Commands

Post Reply
rgpepper
Member
Posts: 82
Joined: Wed Oct 14, 2015 2:09 am

Basic SQL Commands

Post by rgpepper »

We have the Database connect module.
I'm not finding any examples of using INSERT INTO to send data to a SQL database from Switch (including the reference manual). Can anyone give me an example statement of inserting say 5 fields into a table (named Transactions)? The database name is defined in the SQL data source in the System DSN.

What I have:

INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)

The errors I'm getting seem to be format related - complains about "incorrect syntax near "`"" or "...`Transactions`".
lombert
Member
Posts: 167
Joined: Fri Feb 04, 2011 2:31 pm
Location: Sweden

Re: Basic SQL Commands

Post by lombert »

It should be;

INSERT table_name SET key1 = 'value1' , key2 = 'value2' ... ...

See this; viewtopic.php?f=12&t=1405
Between jobs!
sander
Advanced member
Posts: 302
Joined: Wed Oct 01, 2014 8:58 am
Location: Den Bosch

Re: Basic SQL Commands

Post by sander »

True. In addition to my other topic reply.
I'm heavily using this one, like a couple of 100 times a day when I create new carrier labels. Haven't failed once ;)

Code: Select all

update SwitchTransport
set TrackTraceURL='[Job.PrivateData:Key="TrackingURL"]', TrackTraceCode='[Job.PrivateData:Key="Barcode"]'
where ID=[Metadata.Text:Path="//ID",Dataset="Xml",Model="XML"]
rgpepper
Member
Posts: 82
Joined: Wed Oct 14, 2015 2:09 am

Re: Basic SQL Commands

Post by rgpepper »

And this is strictly using the Database connect module, and a SQL statement - no scripting? I'm not having any success.
lombert
Member
Posts: 167
Joined: Fri Feb 04, 2011 2:31 pm
Location: Sweden

Re: Basic SQL Commands

Post by lombert »

Yes. Is the SQL on the local computer? Can you write the data to the database from any sql-program at the switch-computer?

At the switch-computer you need to have an 32bit connector SQL -> ODBC.

Then you need to use a "ODBC Administrator" there you setup the connection between the database and the switch-computer (even if there is on the same computer). (Can't connect to my Switch now, so I can't make any screenshots.)

Then you configure Switch to talk to the ODBC Administrator.

Then you should connect throw Switch to SQL with the command I and Sander wrote.
Between jobs!
rgpepper
Member
Posts: 82
Joined: Wed Oct 14, 2015 2:09 am

Re: Basic SQL Commands

Post by rgpepper »

Check on all of the above (well, the MS SQL server is external but I went through all the setup stuff and it configured just fine and I get a SQL error back as I mentioned in my original post), except I can't make the insert command work. Can someone please comment on my original query:


INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)

Transactions is the name of the table. All the data I'm working with is being pulled with the "Multi-line text with variables defined" dialog in Switch.
lombert
Member
Posts: 167
Joined: Fri Feb 04, 2011 2:31 pm
Location: Sweden

Re: Basic SQL Commands

Post by lombert »

Then is should look like this;

INSERT Transactions SET FileName = `[Job.Name]' , Salesperson = '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, Email = `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, SecondEmail = `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]'

Everything as one line.
Between jobs!
rgpepper
Member
Posts: 82
Joined: Wed Oct 14, 2015 2:09 am

Re: Basic SQL Commands

Post by rgpepper »

I copied and pasted your statement, nada. "Incorrect syntax near the keyword `SET`" and "unclosed quotation mark after the character string "`" "

Switch 13u1, SQL 12.
lombert
Member
Posts: 167
Joined: Fri Feb 04, 2011 2:31 pm
Location: Sweden

Re: Basic SQL Commands

Post by lombert »

You need to change all " ' " , it looks like they are not the same..
Between jobs!
rgpepper
Member
Posts: 82
Joined: Wed Oct 14, 2015 2:09 am

Re: Basic SQL Commands

Post by rgpepper »

Are they supposed to be single quotes (apostrophe's) or the acute character - same key as tilde?
lombert
Member
Posts: 167
Joined: Fri Feb 04, 2011 2:31 pm
Location: Sweden

Re: Basic SQL Commands

Post by lombert »

Copy that in Sander's code. Must be the right one..
Between jobs!
rgpepper
Member
Posts: 82
Joined: Wed Oct 14, 2015 2:09 am

Re: Basic SQL Commands

Post by rgpepper »

This worked:

INSERT INTO Transactions
VALUES ( '[Job.Name]' , '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]', '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]', '[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]');
lombert
Member
Posts: 167
Joined: Fri Feb 04, 2011 2:31 pm
Location: Sweden

Re: Basic SQL Commands

Post by lombert »

Good!

But strange. Mabe not the same if it is MySQL or MS-SQL? I have MySQL..
Between jobs!
Post Reply