c++sql-serverc++11sqsh

how to use a file to input a list of vararible in the SQL or sqsh?


I am very new to SQL, I want either integrate the SQL to my c++ code or use c++ output file as an input variable for SQL.

for example i have a query:

use data_set1;
select a.cluster, b.pig, b.PDBgi, b.PDBaccession, a.Seqstar, a.Seqstp
from data_set1..clusterPDBpig a 
join pig2PDBgi_acc b
ON a.pig = b.pig where b.PDBaccession = '& 2Y69S'

In which the b.PDBaccession is a 500-1000 list of data, I could put them in a vector in c++ code or an output file...How do I integrate SQL query here? Thanks!


Solution

  • Look up SQL Table Value Parameters.

    You can create a user-defined table type parameter like:

    CREATE TYPE [dbo].[AccessionData] AS TABLE(
        [ValueToUse] [varchar](100) NOT NULL
        PRIMARY KEY CLUSTERED 
    (
        [ValueToUse] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
    )
    GO
    

    Then in your SQL code do something like:

    SELECT a.cluster, b.pig, b.PDBgi, b.PDBaccession, a.Seqstar, a.Seqstp
    from clusterPDBpig a
        INNER JOIN pig2PDBgi_acc b on (a.pig = b.pig)
        INNER JOIN @InputData I on (I.PDBaccession = b.PDBaccession)
    

    Where @InputData is of type AccessionData

    The following links provide a bit of information:

    http://www.christian-etter.de/?tag=table-valued-parameter

    http://www.c-sharpcorner.com/uploadfile/pchandraker/passing-table-valued-parameter-to-stored-procedu-part-1/